Getting started with sorting and filtering in Microsoft Excel

Description of the video:

>> Too far into my introduction, I want to make sure everybody can hear me. Can somebody send me a message in chat that lets me know they can hear me? Okay. Great. I'm hearing lots of you saying that you can hear me. So my name is April Law, and I work for IT Training at IU Bloomington. And I am going to be walking you through getting started with sorting and filtering in Microsoft Excel today. I have three of my colleagues here: Rachel, Jason, and Jen. They are working with me as well. They're going to be monitoring the chat. So if you're new to Zoom, if you move your mouse down towards the bottom of the Zoom window, you will see a chat button. That is where you can communicate with my colleagues, with Jason, Rachel, or Jen, and with me. You can put any kind of questions in there, and they will be looking at that as I'm talking. They will be answering you directly if they can. If not, they will not hesitate to interrupt me and ask me to explain something, to show something again, to give a little bit more information. So don't hesitate to ask questions in the chat. In today's session, for the next 25 minutes or so, we are going to be looking at ways to sort and filter data in Excel to answer questions you may have about your data while keeping that data intact so that we're not disrupting the data, we're not deleting data, we're not manipulating the data. We're just sorting and filtering to answer the questions we have. We are also going to see how to create a table in Excel and work with the total row in that table. Towards the end of today's session, we're going to take just a couple of minutes to show you how you can get more information about sorting and filtering, how you can get to the class that this Getting Started session was based on, which is our online course called Excel: Working With Data. And we'll show you how to do all of that in a little bit. Couple things to note. Today I am going to be working in demonstration mode, which means you will simply follow along, watch what is on your screen. You won't be doing the steps with me today. The file I'm using this afternoon will be included in a follow-up email, so you will be able to have this file later on to play with and to work on sorting things on your own. Another thing to note. I will be working today in Office 365. That is what happens to be on the laptop I have available to me here. Office 365 looks like and works like Office 2019. But everything I'm going to show you here is a basic part of Excel and can be done in Office 2016 and earlier versions of Office as well, although things may look slightly different in other versions. So I'm going to, first of all, share my screen. And here is -- I have to move all of the little things around on my screen so that I can see everything I need to see when it pops up here in Zoom. That takes a second to get all this out of the way. Okay. So, again, I'm working in Office 365. Should be the same anywhere with the exception of maybe some different colors, slightly different looks of the graphics, but everything I'm going to show you should work. Okay. What we have here is a workbook called Getting Started with Sorting and Filtering and a worksheet called Summer Reading. This is a highly fictionalized account of a summer reading program at a public library. What we have is some information about children who come in and sign up for the summer reading program, their library card numbers, names, branch, their school, their grade, and how many of the opportunities they completed in a summer reading program. So what's going on with this data is there is absolutely no organization here, right? This data is not organized by library card number, is not organized by last name, not organized in any way at all. The assumption could be that maybe the front desk of the library has this spreadsheet open, and when a child comes in to sign up for summer reading, that whoever's sitting there scrolls to the bottom and adds the child to the bottom of this list, right, with no kind of order to it. So that could work, right? But what if we need to actually answer some questions about this? What if we need to know how many first-graders from Newton Elementary signed up, right? This -- that's going to be difficult to find without any organization. Or what if we have a student who comes in and says, you know, "Oh, hey, I read three more books this weekend." It's going to be difficult to find that student with no organization to this data. So we're going to use sorting and filtering, and we're going to figure out how to get this so we can find the data we need, and we can find the students we need. Before we begin with sorting and filtering, we are going to turn this data into a table in Excel. If you have not used tables in Excel, you may want begin using tables in Excel. They give you a lot of benefits. They automatically freeze the top row. They put in built-in filters. If you add formulas or functions to the data and you have the data in a table, the formulas and functions are automatically applied down the columns. And you have the use of the total row when you have a table. We did this session a little earlier, and we had some questions about this. Everything I'm going to show you today you can do without the table, but the table makes it a little easier. So we're going to do this with a table today. To get the table, I'm going to be clicked anywhere in my data, and I'm going to go to the Insert tab and click Table. Excel picks out the cells that it thinks make up my data. Excel is correct. My table has headers. The headers are the column headers up here along the top. So it does have headers. And I'm going to click OK. So here's my table. Before I do anything else, I have recently learned that this default table style is not accessible, which means that it may be difficult for somebody with a visual impairment to read. So before I do anything else, I'm going to change it to an accessible table style, which is this one. There are several, actually. Just this is one of them that is accessible. So I'm going to make that change just to keep things going well. All right. So here's our data in the table. What the table did -- obviously, it changed the colors. The table put in automatic filters. See how each of my headers now has a drop-down? Those are the filters. And if I scroll down, notice how my column headers and filters automatically replace the number -- or the letters of the columns up there. And so let's say I have a student who comes up, and she says, "April, I read two more books." And I say, "Okay. What's your name?" And she says, "My name is Wanda Hart." And I say, "Okay." So I start hunting through this, looking for Wanda. Where is Wanda, right? Not the best. I can quickly sort this by last name. To do this, you don't have to select anything in Excel, right? You don't have to select the whole column, you don't have to select the data you want to sort. You just have to click in that column. So I just need to click in the last name column, anywhere in the last name column. And I'm going to go to my Home tab. Way over here to the right is my Sort and Filter button. I click that, and I click A to Z. And my entire sheet now is in order by last name, right? It pulls everything from the row with it. So it did not simply sort the last name column. It sorted the entire sheet based on last name. And now I can quickly find Wanda Hart. Here's Wanda. Wanda said she read -- what did she do? She read two more books? Let's say she did two more programs. So I'm going to take her from one to three and press enter. And now my sheet is in order by last name and easier, hopefully, to find the students that I'm looking for. All right. There are some questions. Okay. The -- yeah, Jason's correct, Noma. We -- this was something we discovered recently because Office 365 runs an automatic accessibility checker as you're working. And as we were working through some things, it kept popping up that that default table was not accessible. So yeah, the accessibility checker in Excel caught that for us. Okay. Jason's answering questions. Yes. Oh, yeah. But you could use Control-F. Yeah. All right. Okay. So we did a pretty simple sort there. We can also do multiple levels in a sort. So we can sort by one thing, and once we have our sheet sorted that way, we can sort by something else within that first sort. So let's say we want to see participation by school, right? We want to know how many students participated in each school. We're going to begin by sorting by school. Earlier, when we did the sort, we went up to the Sort and Find button, but we can do this using this drop-down. So if I click anywhere in column F for the schools, and I click this drop-down, there's my sort, sort A to Z. And now my sheet is sorted by schools in alphabetical order, starting with Carmichael Elementary and so on. But maybe -- you know, that's good. And whoever came to me and said, "April, I need to know, you know, how many students from Newton Elementary we had," you know, we could figure that out from here. But it might be even more helpful if we had the students within each school sorted by grade, right? So not just how many students from Newton Elementary, but how many second-graders or third-graders from Newton Elementary? So we can add a second level to this sort. To get to the second-level sort, I'm going to go here to my Sort and Filter button. I am still on the Home tab. I'm going to click Sort and Filter, and I'm going to click Custom Sort. Notice it's already telling me that this is sorted by school, which it is. That's the sort I just did. And I can add a level to this. I'm going to sort by school. And it even tells me here Then by Grade. And I can do smallest to largest, or largest to smallest. We'll keep it smallest to largest and click OK. And now I can see my Newton Elementary students in order by grade, starting with first and going to sixth, right? Same with all of them, not just Newton Elementary. It did all of the schools that way. Oak Brooke Middle School is sorted, Smalley High School as well. So maybe the person who came to me said, "April, we need to know how many students from Newton Elementary participated in the summer reading program." There are lots of ways I could do this. I'm going to show you a very quick way to do this. If I take this data here and I press and drag all of the Newton Elementaries, and I look down in my status bar -- let me get -- where's my drawing tool? Right here. There it is, right? If I get that status bar or I get down there to the status bar, it tells me 12, right? If I press and drag any kind of data in an Excel worksheet, the status bar will give me information about the data I have selected. Again, there are lots of different ways I could come up with 12 here, right? This is just one. This is a quick way. And if that person who wanted to know about Newton Elementary, they said, "Okay. Well, yeah, you had 12 from Newton Elementary but how many third-graders?" Oh, let me get rid of my drawing. I had four third-graders. There, right? Whatever I press and drag, it will give me the count, the average, the min. The status bar can do lots of different things, and you can set it up to do lots of different things. But by default, it will give you the count and the sum and the average, I believe. All right. So that's a multilevel sort where we sorted by school, and then within that, we sorted by grade. We also can do -- let's do another pretty simple sort. Let's look at this and see the number of programs attended. Do we have lots of students who attended a lot of different programs? So I'm going to go here to my programs attended column, and I'm going to sort these largest to smallest. We haven't done that yet. And look, we have several students who attended a lot of programs, right? So now we can see here that Martha attended the most, 22 programs, the most out of anybody. A couple people right there below her, but she was at the top. So when we do that programs added sort, when we sorted to see that, it overrode the school sort and the grade sort, right? So when we sorted by programs, we are no longer in order by grade or by school or grade. If we had wanted to do school, then grade, then programs, we could have done more multilevel sorts. But when we do a brand new sort this way, it overrides the sorting that we had done before. All right. So that's sorting. We saw a pretty simple sort, we saw a multilevel sort, and then we saw a sort that went largest to smallest. Now we're going to take some time and look at filters. Filters are another way we can get information and answers from our data. They are a little different than sorts. We can actually build filters and sorts together. So let's see. Let's try to answer the question, do high school students attend library programs? Right. So to answer this question, we're going to want to see only the high school students. So we can filter the school to show us just the high school students. We could also technically filter by grade, right? But we're going to filter by school. We're going to click this filter drop-down, and here are our filters. We could uncheck the ones we don't want. They give us a quick way to do this. We uncheck Select All and then just check the two we do want, Elmwood High and Smalley High School. And there. There are our high school students who signed up for summer reading. Notice over on the side, right here, our row -- that's not a great drawing. Sorry about that. Let's try that again. Our row numbers look a little different. So what has happened here is when we filter our data, we don't delete data. The data's not gone out of the worksheet. The data hasn't been moved somewhere else. The data has simply been hidden. And to give us a visual indicator of that, we can see that the rows numbers are now blue, but they're also not always consecutive. Anywhere there's a break in these numbers, that's hidden data. You also get sort of this wider line to this, right, to give you an idea that there's something here between 9 and 12. We just aren't seeing it because it's hidden. So that is an indicator there, when we filter, that something is hidden. So we have our high schools. We're seeing only the high schools, but now it would be even more helpful if we could see how many students there were at Elmwood High and how many students there were at Smalley High. So we can add a sort within our filter. So I can click the Filter button, and I can sort this A to Z. And there I see my Elmwood High students at the top and my Smalley High students at the bottom. And did they attend programs? Some of them did. That was the question we were trying to answer, Right? We have one person here who didn't come to any programs, and we've got a person down here who came to just one. But several of these students came to several programs throughout the summer. All right. All right. So the difference, one of the differences -- there are several differences, but one of the differences with filters and sorting is that to do something else with this, we will have to remove that filter at this point because all we're seeing are the high schools. And to see anything else, we go back to the Filter button, and we choose Clear Filter. And there we are back to all of our schools. They're still in order by programs attended. Oh no, they're not. Sort of are, aren't they? But not really. So -- but we have removed the filter, and we're ready to go on to something else. We are going to now work with a filter to see how many books seventh and eighth-graders read. So we are going to filter by grade. We're going to uncheck Select All, and we're going to check only seventh and eighth and OK. So here are my seventh and eighth-graders at two different middle schools, but we want to know how many books did they read. There's the books read. Again, we can use the status bar to see this very quickly. We can press and drag down column H, and my status bar tells me that seventh and eighth-graders read 258 books. They read an average of 17.2 books. That status bar may be really small on your screen, but there is not a way for me to make that bigger, right? So that's how many books our seventh and eighth-graders read. But I'm looking at this and I think, "How many of these students read more than 20 books? So we can add another filter. We can add a filter to show us only the students who read 20 or more books. So I can click the filter next to Books Read, and instead of choosing just a number, I can choose any of these things -- Less Than, Equal Than, Greater Than, the Top 10. But I'm going to do Greater Than Or Equal To. And I want to know the students who did more -- greater than or equal to 20 books. And there they are, our five students who read more than 20 books -- five seventh and eighth-graders who read more than 20 books, right? There may be students, other students, that read more than 20 books. But what we're seeing now, we have this filtered down to see only the seventh and eighth-graders. Now, what if I wanted to see -- I thought, "Oh, that's pretty telling, that five seventh and eighth-graders who read more than 20 books?" But how many students did we have overall that read more than 20 books? So I can take the filter off Grade. I can clear the filter from Grade, and now I see all the students in every grade who read more than 20 books, right? I could sort this by number of books, largest to smallest, and there we go. I can see all the students who read more than 20 books using sorting and filtering to get those answers. All right. I'm going to remove these filters. I'm going to remove the filter from Books Read, and now I'm looking at all of my students again, everybody, no matter how many books they read, what school they're at or what grade they're in. And I'm going to now add the total row to this table. I mentioned at the beginning of our session that by turning this into a table, we got some benefits, including these built-in filters at the top and a total row. So the total row appears at the bottom. If I click anywhere in this table and I go to the Table Design tab and click the Total Row check box, the total row comes in down here at the very bottom. I'm going to have to scroll a little bit, just a little bit. Oh. There. I have -- 46 is way over here to the right. So what the total row does is it goes all the way to the right, and it does -- Excel does what it thinks you want it to do way over here. So my furthest right column contains information about T-shirts. Excel cannot average that. Excel cannot add that up. So what Excel does is it counts it, right? So we have 46 T-shirts ordered, which gives us an indicator that we have 46 students that did the summer reading program. But we can make this total row do lots of different things. If I click in my total row under Books Read, you see I get this drop-down here. This is what the total row does for me, and I can click that, and I can have all of these options. What do I want to know about the number of books read? I want to know the sum, the total. We had 878 books read this summer. I can press and drag that over. I can put things for the total row all across here in every column if I want to. But I can use my AutoFill handle and press and drag that sum over. And I had 508 people attend my programs, and I had 202 puzzles completed this summer. I can get all of that information in the total row. And what's even more, I can change what this does. So I could change maybe the books read from the total to the average. Nineteen and some odd. I can adjust this so we don't see all of those decimal places. Right. So on average, my students read 19 books this summer. And I can pull that over and see the averages for programs attended and puzzles completed as well. Not only can I use the total row for the entire table; I can use the total row combined with a filter. So maybe I want to see what my first and second-graders did. So I can go to Grade to the Filter button -- or the Filter drop-down on Grade. Click Select All. Clicked 1 and 2 and OK. And now I'm seeing just what my first and second-graders did. The first and second-graders read an average of 12 books, attended an average of 13 programs, and did an average of four puzzles. So I can combine the total row with the filters to really narrow down exactly the information that I need or that somebody else needs from my data. All right. I'm going to remove the filter from Grade, and I am back at the top. All right. I'm going to take -- we are at 2:26. I have talked for 26 minutes about sorting and filtering. So I'm going to take a few minutes to show you how to find the rest of this information. I'm going to stop sharing this screen. I'm going to get my other screen over here and begin sharing it. Here it is. So if you are wanting a little more about sorting and filtering or Excel in general, at IT Training, we have several online training courses available to you. All of our courses are available at no cost to everyone, whether or not you are associated with IU. To find our courses, you can go our website, ittraining.iu.edu. And I see Jen has helpfully put that in the chat for you. And from here, you can click on Explore Topics. And this one's having a little trouble. Oh, there it is. And here is where you can see all of the IT Training Expand courses. Expand is the online portal that IU uses to bring continuing ed and not-for-credit courses to people in the university and outside of the university. Those things all go through Expand. So when we click on See Expand courses, this then gives you a listing of all of the IT training courses that are in Expand. Again, these are all online self-paced courses, and they don't cost you anything, whether or not you are part of IU or not. So I'm going to scroll down until I see -- the course that we built this Getting Started session from is the course called Excel: Working With Data. And if I click that, I see much more information about Excel: Working With Data. And then I click View Course in Expand. And here I am now in Expand, and I have the option to log in or sign up. And this is where things get slightly different, depending on if you are associated with IU and you have an IU username and password or if you do not. If you have an IU username and password, you simply log in like you do for all the IU services. If you do not, you create a guest account. The guest account is also free. And once you create the guest account, you can come back in and log in with your guest account. So this was quite a bit of information. All of this information that I just told you about, getting to expand and finding your courses, is on this website. We have information for IU people and non-IU people. Jen has also put this link in the chat. These links will both be part of your follow-up email that you will be getting. So you can grab those from the chat if you want, or you can wait for your follow-up email. It will all be there. Your follow-up email will also contain a link to the file I use today so that you can have a file to play around with sorting and filtering and not be in your own data that you might use. We have several more sessions coming up. This is the last one for this week. I'm going to scroll. I'll try to do that as gently as possible on your screens. Next Tuesday, we have Getting Started with Pivot Tables. I will be back talking about pivot tables. And next Thursday my colleague Beth will be talking about getting started with Adobe applications. The following week we're going to do a little more Adobe, and we're going to do a little more Excel and a little more -- oh, accessible documents. That's the other thing we're doing the third week. So these are all part of the Get Started website through IT Training. And there will be a video. This session was recorded, and the video will be up on the IT Training website as soon as we can get it there. It does take -- there's a lot of stuff that has to happen in the background to get those videos ready to be posted. And we have colleagues at IT Training who are working very hard to get those up and ready to go and posted as soon as we can. So if I have missed anything, Jason or Jen will let me know that. Or Rachel. If not, we are glad to hang out for a couple of minutes and take questions. So if anybody has a question, please drop that into chat. And I will return to the exercise file. I'll be happy to take questions and explain things as best I can. The table, you will be getting the link to that in the follow-up email. I think Jen put a link to it in the chat. There it is. >> I think also -- >> Jen has also put it there in the chat. Oh, go ahead. >> Yeah. I'm wondering if the question is, how did you turn it into a table? I think that's what Brett is trying to get at there. Could you review that step? >> Yeah. So when I was in my regular data, if you go to Insert, the Insert tab -- and Table is here. It's grayed out because I've already made this a table. But that's the button -- Insert and then Table. Did that answer it? Do you think that got it? Perfect. Okay. Yeah. >> Yeah. >> Great. >> Okay. Thanks. >> Yeah. All right. Any other questions? Anybody? I'm happy -- we're happy to keep going.