Sorting & Filtering6:39 with Michael Watson
Use spreadsheet sorting features to sort rows of data: for example, list the rows of sales data in the order in which the sales took place by sorting on a sales date. Filter options let you view just the data you're interested in — just the sales in December, or just the grades for a single student.
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up to this video
Another great thing about spreadsheets is that it's 0:00 easy to organize data in different ways. 0:03 In our example, what were our most expensive items, what were our total sales 0:06 at a specific location, how about a specific location and item. 0:11 There are multiple ways to approach and stream these questions with a spreadsheet. 0:16 But, in this video we'll explore the sort and filter aspects of spreadsheets. 0:20 Let's start with sorting, which lets us order our data. 0:26 For example, we may want to list orders by the date they were placed, 0:29 listing newest orders first and older orders last. 0:33 How about we sort all this data by price? 0:36 So we can see the most expensive item all the way down to the cheapest 0:39 product we sold? 0:42 To sort data in Sheets, first select the data you want to sort. 0:44 It's important that you select all the data you want to sort, 0:48 not just a particular parameter. 0:52 So, in this case if we're sorting by price, 0:53 I don't just select everything in column C, I want to select everything. 0:56 After you've selected all the data, go up to the data menu, 1:00 select sort range notice, that this box data has header row, is not checked here. 1:05 And that's because, I did not select the top row one, that has the headers, 1:11 for the data. 1:15 You can sort alphabetical data, like the names of cities from, A to Z or, Z to A. 1:16 You can choose from lowest to highest, or vise versa. 1:21 In this case, we're sorting by price, which is column C. 1:25 And I wanna see the most expensive item at the top, so I'm gonna do descending. 1:31 Click sort, and voila. 1:37 We can see that the most expensive item we sold was a statue of Mike the Frog. 1:40 As I said earlier, it's important to select all the data you want to sort, 1:45 not just the data you're interested in sorting. 1:49 See, if I select just Price here, and not the Location or 1:52 any of the other information in the sort and go run a sort. 1:58 We see that all that was sorted was the Price information, 2:08 but the rest of it did not go along with it. 2:11 And we know that Mike the Frog statues do not cost $5. 2:13 Not selecting all the data that needs to be included in a sort before running your 2:17 sort, is a common mistake newcomers to spreadsheets make. 2:21 And can lead to bad analysis, so watch out. 2:25 I just showed you how to sort based on one parameter, Price. 2:29 But if we want to ask questions that involve two different pieces of data like, 2:32 what is the cheapest product we sold downtown? 2:37 In this example, it's fairly easy to scroll down and look manually. 2:39 But that's not always the case. 2:43 Fortunately, we can sort on multiple parameters like Location and Price. 2:45 We start off the same. 2:51 We select al the data we want to sort. 2:53 We go to the data menu, select sort range, and start with our first parameter. 2:56 In this case, location which is column D. 3:03 Then click this link here, Add another sort column and 3:09 choose your second parameter, Price, boom, it's that easy. 3:13 You can add more sort criteria, if you like, but 3:20 be careful as trying to sort by too many parameters can become unwieldy. 3:22 And honestly, at that point you're approaching some use cases where 3:26 more advanced techniques are likely better. 3:30 Generally, when you start getting above three parameters, 3:33 you should be using filters or more advanced spreadsheet functionality. 3:36 Okay, so we've done the double sort and can see that the cheapest 3:40 item sold Downtown is a $5.00 Hankerchief. 3:44 Sorting is great, but if you think you're going to be doing a lot of sorting on 3:49 a spreadsheet, and regularly changing those sorts, It is better to use filters. 3:53 This is because filters are generally easier to turn on and off, and 3:59 don't require you to select all the data you want to sort 4:03 each time you run your filter. 4:06 So, if you think you'll just need to sort your data once or twice, sorting is fine. 4:08 If you're gonna be doing it much more than that, you use filters. 4:14 Don't worry, similar to sort, filters are pretty simple to set up. 4:18 Okay, so first we need to select the data we want to create filters for. 4:23 Unlike sort, we do not have to select all the data we want to sort or filter. 4:28 We just need to choose the data header rows. 4:34 Then, we can go to the tool bar menu and use the filter here, 4:38 or we can go back to the data menu and select Filter. 4:43 So we have selected our Filters and now can start using them. 4:47 How about we just choose items that are in the shoes department. 4:52 Department, I'm wanna go down and I'm wann to deselect General and 4:55 clothing and I've just kept shoes selected. 4:59 I click OK and bam. 5:02 All the other departments are removed from this set of data. 5:07 The items in the other departments have just been hidden, 5:11 they're not gone from the spreadsheet. 5:14 Filters is also allows to sort as well. 5:16 So, in this view we got just choice through the filters but 5:19 now let's sort by price. 5:23 We can go over to the filter button under price column and 5:24 see there is a sort option. 5:29 So filters are great. 5:32 Now only do they allow us to filter data, but 5:34 they also allowed us to sort it very easily. 5:36 So, I'm gonna undo all these filters. 5:39 I can either manually click them here. 5:42 I could also just click the Select All. 5:44 But now all the data's back for the different departments. 5:49 And I just wanna emphasize again that when you filter out data, 5:53 it does not get removed from the spreadsheet, 5:56 it's just hidden from your view, so the data is still there. 5:59 Here's an example of hiding rows when everything is still visible. 6:02 So, I'm selecting three rows, five through seven. 6:06 I'm going to right-click on them. 6:10 And then I scroll down and select Hide rows 5- 7. 6:12 And you can see that there's these two arrows which signal that there are hidden 6:17 rows in there. 6:20 And you can also see that we go right from row 4 to 8. 6:21 To show these back again, you can click here. 6:24 You could also right click and say, select or show hidden rows. 6:27 You can not only hide rows, you can also hide columns. 6:32
You need to sign up for Treehouse in order to download course files.Sign up