How to Sort and Filter Your Data in Excel
HTML-код
- Опубликовано: 15 июл 2024
- This tutorial walks you through a few quick and easy ways to sort and filter your data in Excel.
Related videos:
5 Great Reasons to use Tables in Excel • 5 Great Reasons to Use...
Transcript (abridged)
My Olympic Athletes file is currently sorted on total medals but we can sort it on any of the columns. As always in Excel there's a couple of ways of doing this. The first is to click in any column that we want to sort on, eg if I want to sort by Athlete I just click anywhere in this column, come up to the Sort and Filter menu and select sort A to Z and we now have all our athletes in alphabetical order. The behaviour of the Sort and Filter menu changes depending on the data in the column. Eg if we wanted to sort by age click anywhere in the Age column, come up to this menu we've now got Smallest to Largest and Largest to Smallest. Likewise if we click in the Closing Ceremony Date column and come up here we've got Oldest to Newest and Newest to Oldest so you can see that's dynamic and it adapts depending on the type of data you're selecting. The other way of applying a sort is to do a custom sort select the Custom Sort from this menu and in here you can select any column in your table. You can sort on the values or various other options in there and you can determine the order that you want to sort it in, and you can get quite detailed with this and put custom lists in and things like that. Another thing you can do here is you can add additional levels of sorting. So what I mean by that is we can select our country here and that will sort all our athletes by country A to Z and then I can add a level eg within each country I want to sort the athletes by name A to Z. So if we apply this, our athletes are sorted by country and then within the country in alphabetical order. And you can add many levels. Another way to access the sort menu is through filtering. If we apply a filter to our data - we do this by clicking anywhere in our table of data, coming up to our sort and filter menu and selecting Filter. You can see that that's added these little drop down arrows next to every single column header. What these drop down arrows do is they give us a shortcut to the sort functionality. If eg we click on the Athlete one you can see we've got the sort A to Z, sort Z to A and Sort by Color and what the Sort by Color does is actually brings you to the custom sort menu, and that's the one I've just taken you through. The other thing we can do with filters is we can select a subset of our data. Eg, we might want to look at every record pertaining to Chris Hoy so if we start typing Hoy you'll see that this is adapting the list below to our search criteria. We've got two records so we can just unselect Andrew Hoy press OK and we've now just got the records relating to Chris Hoy. The other nice thing about this is it gives you a count at the bottom, so it's telling us there's four of our 8613 records that relate to Chris Hoy. Just to show the value of that I'm going to clear this filter (to do that we come up to the Athlete drop down and select Clear Filter from "Athlete"). If we wanted a quick count of how many athletes had competed in Athens, we'd select Athens in our filter click OK and you can see that 1839 of our athletes competed in Athens. If we applied filters across multiple columns so eg if I filtered on everybody who was 20 when they competed in Athens and competed in football we've got three filters applied now so we could clear them by coming into each drop down and clearing the filter. A quick way to do it is to come back to the Sort and Filter menu and click Clear which clears all filters from all columns. As always in Excel you can do these actions with keyboard shortcuts. To apply a filter all we need to do is press and hold CTRL+SHIFT on the keyboard and press L and that's applied the filter to all our columns. If we want to remove the filter CTRL+SHIFT and L will toggle it off. Another useful shortcut is for doing a quick sort A to Z or in ascending number order, and to do this we press ALT and then H S S and you'll see that's immediately sorted by athletes A to Z. If we moved into a column with numbers in it, for example our Age column, and do the same ALT H S S that's now sorted the numbers in ascending order to do the reverse sort, so for example Athlete Z to A or Age oldest to youngest, press ALT and H S O and if we want to quickly access the Custom Sort dialog box we press ALT and then H S U and that box pops up for you. And if we apply a filter to our data eg everybody who competed in Athens and we now want to clear that filter, again we press ALT H S C and that's back to our full list of athletes. If you're applying filters to your data you might want to put your data in a Table: this gives you the filter functionality and also a lot more besides. I've done a video on reasons to use Tables - do have a look because they save lots of time!
#excel #exceltips #exceltutorial #excelsort #excelfilter
I need to watch more of these, great way to refresh the memory when you're a bit rusty like I am.
There’s plenty more on my channel and if there’s anything specific you’d like me to cover, let me know
This was extremely helpful. I needed to add filters to each column and this video was successful in assisting me.
Thank You! You saved my excel spreadsheet!
Really helpful thank you!
Glad it was useful for you 👍
Very clearly explained at a sensible pace for the fat finger brigade.😊
I’m so glad you found it useful :)
We cant understand your fluent & speedy English hence we skip video . Video would be such which understand easily .
Thank you for the valuable feedback. I am sorry that you found my speaking too fast. Would it help if I translated the subtitles into another language for you to follow along? Kind regards, Charlie
Pradeep, man. Get real. Hers is the sort of English shy of RP that people want to speak. Maybe you. Pradeep, need to listen to Amitabh speaking English more (his is actually quite excellent), unlike that of typical politicians and actors in the subcontinent.