Google Sheets for Educators: Sorting and Filtering

1. Course Introduction: Hi there. I'm Allison and I'll be your teacher for Google Sheets Mastery. I have worked in the field of education for 18 years, first as a teacher, and then as an instructional technology coach and also in data governance at the district level. Over all of those years, I have learned just how important spreadsheet experience is for both educators and school administrators. I'm here now to teach you what you need to know to get your school's data organized and ready to use. Now, let's get started. Welcome back to our course on mastering Google Sheets for educators and administrators. For this course, we're diving into an essential skill organizing data using both filters and sorting. So whether you're managing student performance or maybe student attendance, maybe even standardized test data. These tools of sorting and filtering are going to give you the powerful data and insights that you need. So let's get started. 2. Preview Your Data & The Basics of Sorting and Formatting: For this course, I have created a Google sheet with sample student data that contains whether the student was absent today, what their current grade is in class and who's their homeroom teacher. You can get a copy of this spreadsheet over in the course. So when I first get a spreadsheet like this, and I'm not the owner, I'm going to take a look at a few things. Like, how is this spreadsheet currently sorted if it's sorted at all? This one, when I take a look at it, it looks to me like it's sorted by first name. That's not super helpful to me at this time. I generally like students sorted by last name. You may like something different, but in general, that's how I like to sort lists of students. Of course, it depends on the situation and what you're looking for. In another situation, you may want to sort by homeroom teacher. The other thing I'm looking for, how many students am I expecting to see or with any data? How many rows or am I expecting to see? Let's say I'm expecting to see 200 students, but when I scroll down, I see there's only 67 students. So it seems like maybe some data is missing. So that's a good thing to check for. Also, is there any data missing? I noticed several students don't have a middle name, which might be okay, so I won't worry too much about that. But look, there are two students who don't have absence data today. That is a problem. So I'll need to check on those things as well. These are things I like to do anytime I get a new spreadsheet to make sure that my data is complete before I move along. So once I've decided that all of my data is good, I'm confident in its completeness, let's look now at sorting. So currently, this is sorted by first name, and that just means that everything is in order either alphabetically or possibly numerically, depending on what you're sorting by. There's another topic we'll talk about during this course, and that's filtering. Filtering is a way that you can hide data that is not relevant to you without deleting it. So, for example, let's say we want to know everybody who's absent today because we want to call their parents and find out why they're absent. So we wouldn't want to delete all of the students who are present today. We would just want to narrow down the spreadsheet so we can only see the absent students. That would be an example of filtering. So as you could probably already see, sorting and filtering is going to help you prioritize your tasks. So what's most important? What parents do I need to call? What students do I need to have tutoring with? Like, I could even filter by the students who are failing my class so that I can call them in and ask them to come in for tutoring or extra help. So it's going to help you to prioritize your tasks and to focus on the parts of the data that are most important to you without deleting anything in that process. Another thing you could do here, you could sort by grade so we could see you know who's doing the best in my class, who needs the extra help because they have the lowest average in the class. We could also just do a filter maybe on who's present today and make sure that the count that is present matches the number of students who are actually in my classroom at that time. So there's so many different ways that we can use sorting and filtering. And in the next section, we'll look specifically at how to sort in Google Sheets. 3. Sort Your Data: So let's get started with sorting. Sorting is one of the easiest ways to bring order to your spreadsheet. So you can sort by names like text fields. You could sort by numerical fields and even a custom criteria. We'll start by sorting our spreadsheet by the attendance column. So we want to know who's absent. We want to bring those up to the top of the spreadsheet, so we can sort by the attendance column. So what we'll do is we will select the entire spreadsheet. And you can do that by clicking on this little box here that is to the left of column A, and then go to data. Sort sheet. But we don't really want to sort by column A. We want to sort by column F, and that's not an option. So that's the default. That's not going to work here, so we have a couple other options. We can choose sort range and then go to advanced sort Range, or we can just click on the little arrow up here at the top of F and say, sort sheet A to Z. Now notice that it says sortset when it says the word sheet, you know that Google Sheets is going to take the entire sheet and sort it. So all of Adeline's data will stay together when I sort it. So we're going to choose that option, sort sheet A to Z. Except that look here, we've got a problem. Our header row is not the header row anymore, and there is a way we can fix that. So let me undo it. And when you see, you've got to header row on a spreadsheet, we want to freeze that row. We can do that by selecting the row and then right click and say, freeze up to row one. Okay. That is the way to do it, but let me show you how I generally do it because it's a lot faster. I'm just going to turn that off unfreeze row. If you hover your mouse right here above row one, you'll notice this little hand pops up. That hand is going to if you click on it, it's going to bring this gray bar down, and that's going to allow you to freeze that row. Now when I scroll down, my head of row is always there. So I'll turn that off by dragging it up, and now it's not frozen, and then I'm just going to hover until I get the little hand right there and then drag down, and now row one is frozen. So now let's try that sort again. I'm going to click on column F and say sort sheet A to Z. And now all of our absent students are at the top. Okay. So that's real important that you always freeze that row one so that the sort will work properly. Okay. Now, let's try again. This time, we're going to sort numerically. So we'll choose column D and we'll sort so that the students with the highest grades are at the top. So I'll click on the D here, and then go sort sheet A to Z. And that didn't do what we wanted, we wanted the opposite direction. So let me try sort sheet Z to A. Okay, perfect. Now the higher numbers are at top and the lower numbers are at the bottom. Remember, because we chose that feature sort sheet, it is going to keep all of the data together for each student. So now the students with the highest grades are at the top, and then the ones with the lowest grades are at the bottom. Okay, let's say we also want to do a different sort where we're going to sort by homeroom teacher, but then we also want to sort by the student's last name. So we have two different columns that we're going to sort by. There's a couple different ways to do that, but my favorite way is to use the advanced sort, which I did allude to earlier. So I select the whole spreadsheet by clicking on this little box here to the left of the A, and then data sort range and advanced Range sort. And then from here, we're going to say, yes, we do have a header row. That's Row one. And then what do we want to sort by first? Well, first, I want to sort by the homeroom teacher so that all of Gross' students are all together. But then after that, I also want to sort by the student's last name and then by the student's first name. All right, and then I'll hit SRT. Now all of Gross's students are all together, and all of his students are alphabetized. And then all of Ho Yoshida's students are together, and all of them are alphabetized, and so on. Okay, so that's it. We've done it. We have sorted numerically. Remember, we did that by clicking on this and we just sorted A to Z or Z to A. We also sorted by just the attendance column. And then we did a sort with multiple columns by selecting the spreadsheet, then going to data, sort range, advanced range sorting options. Now, a quick note. Google Sheets does not save your sort. So if I wanted to resort this, I'm going to have to go put each of those things in again. So it doesn't no matter even if you just did it like 1 second ago, it doesn't save that sort, so you're gonna need to reput it in each time you want to resort something. And that's it. Now you know how to sort. We'll look at some more details and more advanced sorting later, but you have the basics. Way to go. 4. Filter Your Data: All right, let's move on to filters. So filters are a way to narrow down a lot of data so that you can only focus on what matters most at this moment. The nice thing about filters is that you're not deleting anything, you're just viewing the information you need the most. So let's look at how to create a filter. So the first thing you'll need to click somewhere inside your data. It could be up here on the headers or just in the data itself, and then go to data, create a filter. And what you'll notice is these little funnel looking triangle things are going to show up. That means the filters are on. Now, if you look over here on your shortcuts, there's also a filter button here that is a way that you can easily turn it off and on without going to the menu. So now we have our filters turned on. So let's filter the attendance column. So that it only shows the students who are absent today. Maybe we want to give the parents an email or a phone call to let them know that their student is absent today and make sure everything's going okay. So what we'll do if we want to see absent students is we'll come up here to the little filter button on this column. And we'll say, Okay, we want to just see absent students. So I'm going to uncheck present so that only absent is there. Now, if you remember back from our last video, we had some incomplete data where a couple of our students were blank for today's attendance. And I want to leave that checked because we need to figure out what's going on with those kids. So let's leave that checked and hit Okay. So now we're going to see all of our absence students and those two students who don't have attendance today so we can figure out what's going on with them. So now, when I turned the filter on, that little triangle turned into a funnel. So that means that column is now filtered and we're only seeing those particular students. So, let's say we want to turn the filter off. We can just click on the funnel. And we can say, select all three, that'll do it. Hit Okay. You could also let me just undo that. You could also just click the funnel up here, and that's just going to get rid of everything. So the filters turn off, everything's gone. That's a couple different ways. So I'm going to go ahead and take present off again, and this is where we were. So from here, you can completely turn the filter off, remove all filters, or you could turn off just the filter on this particular column just by saying select all three and hit Okay. Alright, now we're back to all of our data. So that's filtering by text. Now, let's look at filtering by numbers. So this spreadsheet has a grade, a percentage grade for each of our students. And let's say we want to focus on those students who currently have less than 70 in my class because I want to give them some extra support, maybe tutoring, maybe some kind of a retesting, a different assignment for help. So we're going to give them some extra support. We want to identify who those students are. So I'll click on this little triangle here, and we want to filter by values. So we can see here the lowest value is 50, and it goes all the way up to 100. So we could go in and uncheck a whole bunch of different people here. Like, that would be an option. But we don't want to do that. It's gonna take too long. We would have to go and unchecked. Everything's 70 and above. So instead, what we'll do is filter by condition. And we're going to say that it is less than 70, less than 70. So now, any grade that 69 or below should show up. So let's check a look. We'll hit Okay and see what it does. And it looks like we're good. So remember, our lowest student was a 55, and I do see the 55 here, and then we have grades all the way up to 69. So we're now seeing all of our students who have anything lower than a 70 in class. So let's say we really want to catch those kids that are kind of on the bubble, maybe the kids who have grades 70 to 75 as well, because we're concerned that they may drop below failing. So I'm going to say less than 76 instead and then hit Okay. So now our list has all students all the way up to 75 because we said less than 76. This is it looks like we don't have any students with a grade of 75. 74 is the highest, but our formula did say less than 76. So now we've looked at both grading, filtering by numbers on the grade column and filtering by text on the attendance column. So here's one problem with using filters in Google Sheets. If I share this spreadsheet with some of my coworkers or with the administrator at my school, when that person opens this spreadsheet, they're going to see it filtered as it is now. So let's say I share this is let's say I'm an administrator, and the other administrator at my school also needs to look at the same data. But she's going to be looking at different students in different conditions. So she doesn't want to see this data in my filters. She wants to have her own filters. It becomes a problem if both of us are working on the spreadsheet at the same time. So Google Sheets has come up with a way to address that, and those are called filter views. So what we've been doing are filters, just plain filters, and we're going to next look at filter views. So I'm going to go and just completely turn off and remove all of the filters we've already created. And just to the right of that button is another button called filter views. So what you can do is you can create a filter view that only you will see. So let's try doing that now. And we're going to say, let's say, we're going to call we're going to say any student who has less than 70, right? Because we know that that's something we're concerned about. We're concerned about our students who are currently failing. So less than 70. Then I'm going to hit Okay. And then what we can do is hit Save view. And then we're going to call this grade less than 70. Now, hit Save. Okay. So now, when we called it, it renamed the filter. It was called, I think, temporary filter, and now it's called grade less than 70. So what I can do is I can hit the exit and it's gone, and then come back up here and notice it's now listed here because we saved it. So I click that, and it's now applied. But the cool thing is, when the other administrator is looking at this same spreadsheet, she sees all the data. She does not see that I have filtered the data. She'll be able to tell that I'm in here because she'll see, like, a little icon with my name on it up at the top, that I'm working in the spreadsheet. But she won't be able to tell that I have this particular filter open. This is so nice when two people are having to work in the same spreadsheet at once so that we don't you don't hide data from each other accidentally. So we'll turn off this filter view, and let's create another filter view. Create filter view. And this time, let's say we want to see all the students who are absent. Okay? So we'll say and we're going to go ahead and include those blanks again because we are a little bit concerned about what's going on there, and I'm going to hit Okay. And then I'll save the view, and I'll call it attendance, absences and blanks. And hit Save. Okay, so now I'll close this filter view. And now we have both. So I can be looking at attendance, and then at the same time on her computer, she can be looking at failing students or vice versa. I'm sure you can see how this is just a game changer when you are sharing Google sheets with other people so that you can both be looking at your own filter views and using the spreadsheet in whatever way that you need. Alright, that covers filters in Google Sheets. Just a reminder that when you use filters, you are not deleting any data. You're just hiding the data that you don't want to see right this moment. This is so useful when you are trying to get a large set of data pare down to the thing that you need to know right this moment. Remember, we started by using regular filters, and you can turn those on by clicking the funnel. And then turn them off. And then we went over to filter views, where you can create a filter view, and then if you've already created one, they're listed right here, you can turn those on that way and turn them off with a little X. 5. Combine Sorting and Filtering: Alright, so we first learned how to sort, and then we learned how to filter. So now let's look at sorting and filtering together to do some basic data analysis on our spreadsheet. So let's start by sorting our grades here and sorting our students by class. So I think what we'll do is we'll sort highest grades at the top, lowest at the bottom. So there's different ways to do that. One way you can do it is to click on this down arrow. Go to sort Set A to Z. Now, I never remember if A to Z is lowest to highest or highest to lowest. And so I just try one and C. Okay? That's not what I wanted. I wanted Z to A, because I wanted the highest at the top. All right. So now we have our highest students at the top. And then the next thing I want to do is, I want to sort by homeroom teacher. So let's see how that looks out. So I'm going to do sort sheet A to Z. Okay, so what we have now is we have a list of all of our homeroom teachers, right? And then within that home room, we have the highest at the top and lowest at the top. So let me hit undo and show you that same process, but backwards. So back again and back again. So this is where we started. Now I'm going to do a sort by homeroom teacher, and then I'm going to sort by grade. Okay, notice how this is different. So all of the students with 100 are at the top, and then all the students with 99 are down below them. But within that group of 100, those are sorted alphabetically. So the point I'm trying to make here is the order in which you do things matters. And you'll kind of start to see that. Now, the way I just did it is I did one sort, and then I did another sort. But a different way to do that is to highlight the whole spreadsheet, go to data, sort range, advanced range sorting. And then you can do it all together. So if I really want to sort by homeroom teacher first and then sort by grade, Z to A, I can do that, and I can see it all on one sheet rather than doing one sort, than doing another sort and realizing, Oh, that was backwards. I didn't want to do it that way. That's why I prefer to do it. If I'm going to do multiple levels of sorting, I prefer to use this instead. And then it's going to be exactly what I want it to be. All right. Alright, let's talk about a specific use case for using sorting and filtering together. Let's say, our administrator has asked us for all of our students who are on the A honor roll. Or let's say you're the administrator, and you're trying to get every student in the school who's on the A honor roll. So where I am from, the A honor roll is 90 and higher. 90 to 100 is an A. So I'm going to filter in the grade column for 90 and above, and I want to see that grouped or sorted by homeroom teacher. So let's start by filtering. Remember to filter. You're going to click on Create a filter. Oh, sorry about that. We have to I'm going to hit Undo. The reason that happened is because my cursor was outside of the data. So let me put my cursor inside the data and then click the filter button. Perfect. So now I want to filter the grade column. So I'm going to say filter by condition, and I want to say, the value is greater than or equal to 90, greater than or equal to 90 and hit Okay. So these are all of our students with a 90 or higher. And then I want to make sure that they're sorted by homoom teacher. And it looks like that's already the case, but I can hit sort A to Z, and then just make sure that is the case here. I could also sort by last name. So let's do that. Sort A to Z. But, see, now, the problem with that is that when I did that, it's no longer sorted by homeroom teacher, so I need to go back and do a sort A to Z there. Okay, now we've got all of our students in Gross' class that have a 90 or above, and they are sorted now by last name, CD, F, and P. So now you can kind of see an idea of how you can use a combination of both filtering and sorting to see parts of your data. Remember, if you want to turn all of that off, you'll just click this button and the filter goes away. So now the question I have for you is what data could you sort or filter in the spreadsheets that you use regularly to save time for you this week? That's the thing I'd like for you to think about as we move forward into our next lesson. 6. Practice What You Learned: Are now ready to move on to our project for hands on practice in what we just learned in sorting and filtering. So here are your instructions. Right now, our practice spreadsheet is sorted alphabetically by first name. Now we want to change that so it's sorted first by last name and then by first name. So the students with the last name beginning with an A would be at the beginning and then B and C and so on, but also sorted by first name. The next thing we want to do is create a filter view to show only the students who have a grade below 70%. So those are your instructions. You'll use what you learned in the course to do those things. Pause the video now, give those a shot. And then when you're ready to check your work, come back here, and I'll show you exactly how to do those things so that you can check your work. Okay. Have you done those things? Have you done both number one and number two? If you have, let's do them together so that you can check your work and make sure it's correct. So the first instruction was to sort alphabetically. There's a couple ways to do that, but my favorite way to do that is to select the entire sheet by clicking on this little white rectangle and then going to data. Sort range, Advance sorting. And then hit data has a head a row, and I want to sort by last name. And then I want to sort by first name. I want to make sure both of those are A to Z and hit sort. So your data should look like this, Alcott Algire Allende. So those should be listed. And then I wonder if we have any last names that are I guess we don't have any people who have the same last name. Oh, there's Sa Pronte. So notice Charlotte comes before Emily, which is correct because they should also be sorted by first name. So number one is now complete. The next thing we want to do is create a filter view. When we want to filter our data, we make sure that we're clicked inside the data, and then this is the filter button, but we want to make a filter view. So create filter view. And then we want to filter for grades below 70. So hit the down arrow, filter by condition. And it says below 70. So we're going to say less than 70. Scroll down. Hit Okay. And that's showing us everything that's below a 70. And then we can click Save view. And you can call it whatever you like. I'll call it less than 70 and hit save. So this is what your data should now look like. Now that we have completed the project, you have your list of students here, and they all are less than 70, and they are all sorted alphabetically by last name and then by first name. So if you did that correctly, congratulations. It's done. If you had trouble with that, just go back and watch the lesson on filtering or on sorting to remind yourself how those things work. 7. Wrap-Up and Next Steps: Alright, we've done it. That is a wrap on the sorting and filtering course. As a quick reminder for this course, we started by dragging this first title header row. We dragged this little bar down so that when we scroll down in our data, the title and header row always stays there. We learned how to sort by clicking on this little down arrow here and sorting sheet A to Z or Z to A, depending on the data. We also learned how to select the entire spreadsheet by clicking this white box and go to data, sort range advanced range sorting options. That's going to allow you to sort by multiple things at a time. Okay. So we did that. And then we also learned about filtering. You click in the data, choose the filter button to then filter by something specific. Like we could filter by Gross and hit Okay, and that's going to show us all of Mr. Gross' students. We can turn off the filter button by clicking it. You can also make a filter view. That's going to allow us to save a filter and reuse it over and over again and just turn it on and off. So those are filter views. We learned a lot in this course. If there's anything you missed, make sure to go back and check it again. And as a preview, the next course that's coming is on formatting, and I have a lot more courses that are coming after that one. Thanks for joining, and I'll see you in the next one.