Sign in or start a free trial to avail of this feature.
16. 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.
To explore more Kubicle data literacy subjects, please refer to our full library.
Sorting Data (00:04)
Sorting a data set allows you to rearrange the data whatever way you want. To sort a data set, open the Sort dialog box by selecting the data set and pressing Alt A, S, S. You can then select one or more columns to sort by, and select the order of the sort.
If you want to return the data to the original ordering, then you can open the Sort dialog box and select Delete Level to remove the applied sorts.
Filtering Data (02:29)
Filtering hides entries in the data instead of rearranging them. To apply a filter, select the data set and use the shortcut Ctrl + Shift + L. This adds a series of dropdown arrows to each column. You can use these dropdowns to view only values of interest. For example, you can filter the Order date column to see only orders from a particular month.
You can apply multiple filters to see only rows matching all the filter selections. Columns that are filtered can be identified by a filter icon alongside the dropdown arrow. Finally, to remove all filters on a data set, use the shortcut Alt, A, C.
In the previous lesson, we learned how to use the pay special options in Excel. In this lesson, we'll learn how to sort and filter data in an Excel sheet. 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, oftentimes 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 a 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 column, and I'm happy with the order from A to Z.
I'll then press okay. 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 dialog 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 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 from 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, Control + Shift + I, and as you can see in our top row, this adds a series of dropdown arrows to each column. Let's start by filtering based on the order date. And I just like to see the orders from March. So I'll go to the dropdown and unclick February and January, and when I press okay, you can see that the filter has been performed and I have the seven transactions from March.
Let's now go one step further and apply another filter, say on product type, I'd like to focus on the tablet orders in March. So I'll go to the dropdown and make sure only tablet is selected.
And now we have the three tablet orders from March.
As you can see by applying multiple filters to a dataset, 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 dropdown arrow, so you can easily remember where you've applied your filters.
We'll normally want to remove all filters before 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.