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 3rd 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 Ctrl + Shift + Right Arrow and Ctrl + Shift + Down Arrow. I'll then use the shortcut Alt + A S S to bring up the Sort dialogue 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 Z. I'll 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 orders by quantity, with the largest at the top.
To do this, we'll go back into our Sort dialogue box, with Alt + A S S and add a level.
And this level will be based on order quantity, And I'll 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 to 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 dialogue 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 onto 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 shortcut Ctrl + 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 filtering based on the order date. And I'd just like to see the orders for March, so I'll go to the drop down and unclick January and February, And when I press OK, you can see that the filter has been 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'll go to the drop down and make sure only Tablet is selected. And now we have the three tablet orders 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.