Sign in or start a free trial to avail of this feature.
15. Sorting and Filtering Data
Excel has some great in-built tools for sorting and filtering your data. Find out how to use them in this lesson.
Sorting and filtering data
- Sorting data allows you to re-arrange data any way you want
- You can apply single sort or multiple sorts on a dataset
- Filtering data hides rows of data rather than re-arranging them
- Multiple filters can be applied to a dataset
- Look for the filter icon in the drop-down icon to identify where filters are in place
Useful keyboard shortcuts
ALT + A, S, S: Show sort dialog box
CTRL + SHIFT + L: Add filters
ALT + A, C: Remove all filters
Most raw data sets are ordered by date of entry, as is our current data set. If you look at the order date on the right hand side, you can see that the dates flow from the 31st of March down as far as the third of January. Often times we'd like to order data by another metric, for example, order quantity or product price so that we can quickly draw some useful conclusions from the data. To sort data in this way, first select the full data set with Control + Shift + Right Arrow and Control + Shift + Down Arrow. I'll then use the shortcut Alt + A S S to bring up the sort dialog box. The mouse is probably the quickest way of performing a sort. So here we'll enter the column we wish to sort by. In this case I'm going to sort by customer. So I'll select customer in the sort by column and I'm happy with the order of A to zed and then press OK. And as you can see in our data set, all of Bobby's orders appear first, followed by Jimmy's, Joey's, and so on. This is pretty useful, but we can actually improve the functionality further, by performing a sort on multiple levels. For example, I might want to order all of Bobby's order by quantity with the largest at the top. To do this we go back into our sort dialog box with Alt + A S S and add a level. And this level will be based on order quantity and our order from largest to smallest.
And as you can see, for all of Bobby's orders, we now have the order quantity in descending order. In this way, by adding levels our sort, we can arrange our data in almost any way we want. If the need arises to return to the original order, I'll select the data again, Alt + A S S to return to the sort dialog box and I'll remove the current sorts.
I'll then add a new level based on date.
And as you can see, we have the correct order back again. Now let's move on to filtering. Filtering is different to sorting because it hides entries rather than just rearranging them. Let's see this in action by again selecting our data set and using the filter short Control + Shift + L. And as you can see in our top row, this adds a series of drop down arrows to each column. Let's start by filter based on the order date and I'd just like to see the order for March. So I got to the drop down and unclick January and February and when I press OK, you can see that the filter has performed and I have the seven transactions for March.
Let's now go one step further and apply another filter, say on the product type. And I'd like to focus on the tablet orders in March. So I got to the drop down and make sure only tablet is selected. And now we have the three tablet order for March.
As you can see, by applying multiple filters to a data set, we can quickly isolate the discrete set of data that we want, provided that we have the data cleaned beforehand. After applying multiple filters to a large data set, it's quite easy to forget where you've put the filters. Thankfully, when we apply a filter to a column, the filter icon appears next to that drop down arrow, so you can easily remember where you've applied your filters. We normally want to remove all filters after performing our analysis and to do this we have a shortcut, Alt + A C.
And now we have the full data set back again in our window.