Sort & Filter Video Tutorial

Sort and Filter

Sort & Filter Overview

We will use sort and filter to find and organize data in large databases. We're going to start off by taking a look at how you can sort and filter information and then we'll take a look at a topic called subtotals. Subtotals will allow you to create something that almost looks like a pivot table without creating a pivot table. 

So let's start with sort and filter. First, we need to know where to go on the ribbon to find, sort and filter. If you go to the home tab and you move all the way over to the right over in the editing group, you'll see the option to choose, sort and filter. 

The disadvantage of this location is you have to click the truck down to access any of the options. The advantage is that it's on the home tab. So if you're in the home tab already, it's easy to get to another place where you can find certain filters on the data tab. The benefit of using certain filter options from here is that you don't have to click on a dropdown. You can go right away to sort A to Z and so on. 

Sorting Example

I have a table here that I'd like to sort in order to sort. We recommend just selecting one cell within the data. You don't have to select the entire table and you don't even have to select the column. If you do, that actually gets in the way. For instance, if I try to go and sort from, let's say, largest to smallest, this dialog pops up. It's called Sort Warning for a reason. It's warning you that what you're attempting to do is only sort this column and have everything else stay the same. 

So Microsoft asks you, what do you want to do? It’s almost as if you don't know what you're doing. Microsoft is going to recommend that you expand the selection to include the entire table. Now, I'm going to cancel out of this. I'll go back, select one cell. And then if I click to A or A to Z, notice the entire table is shifting along with that one column. 

If you go over to a column that has text and you have your mouse over A to Z, Excel says sort A to Z or Z to A. If you go to a column that has numerical values, you'll see sort largest to smallest or sort smallest to largest. And if you go to a column that has dates, you won't be puzzled to know what A to Z stands for because it says sort newest to oldest for Z to A and sort oldest to newest for A to Z. 

Other Sorting Options

All right, so let's take a look at some other ways that you can sort your data. I'm going to go over to the quantity column and I want to sort these two yellow highlighted cells. I want to sort them in such a way that they're together. I'll click to Z to A, A to Z, etc. No amount of clicking on these two options are gonna allow me to bring those two colors together. So I'll have to go beyond that and go to sort. This brings up the sort dialog box. Now I'm in the quantity column and I want to sort by cell color. If I choose that option. I get to pick the cell color and I can sort those values on top. I'll click. OK, now I can actually bring those colors together. 

Font Color

Now I want to sort by font color because I do have font color within this table. I'll click on sort, I'll click the dropdown and I'll choose font color and then I'll go and choose the font color. And then I'll go and choose the font color - doesn't seem to be working. Why? Well, I mean the quantity column there is no font color in a quantity column. So this can be a common mistake that users of Excel can make. 

So make sure you're in the right column. I need to go over to sales rep because that's where the font color is. Now, when I click the dropdown, I actually see the font color. And then I can click, OK. 

I'm going to go over to the status column. I want to sort by conditional formatting icon. I'll click sort. Make sure that I go to the right column first. Then I'll choose conditional formatting icon. Sometimes it may be referred to a cell icon and then I'll choose the appropriate icon. Perhaps I want Red to be sorted at the top. I'll click OK. And there it is. 

Multi-Level Sort

Now I'd like to sort the region and the total. In other words, I want to create a multi-level sort. So if I click sort, one of the options I have is to delete a level. So I'll delete the level I currently have here because it's set to conditional formatting. Maybe I want to start over - delete level. 

Now add a level and here I'll choose region. I want those values sorted A to Z. Now, I could also copy level if I like the settings for this level, I can duplicate it and make one change to one of the dropdowns and then be able to get what I want for the second level. So I'll choose copy level just to show you the options. Then I'll simply choose total and that's going to sort smallest to largest. I'll click OK. And now I have my two, my multi-level sort. 

I have the UK all grouped together. Then the total is smallest to largest USA all grouped together and then smallest to largest. 

Filtering

Now let's take a look at filtering over on a certain filter group on the data tab. I'm going to click on the funnel and that's going to add a filter to the table. I want to filter for all the values that are greater than 7,000. I'm going to let you know this is not the best way to do it. If I have a long list of values, this will take forever. 

So what will I do instead? I'll notice I have a number filter and from the number filters I'm going to select “greater than” because I'm looking for values that are greater than and then I'll type 7,000. I've now filtered for all the values that are greater than 7,000. If I want to clear the filter, I could click the dropdown here - choose “Clear Filter” - and now I have my table back with all the information. 

Date Filter

Now I want to filter for all the dates that are before 3/1/2018. Well, I'll go and click the dropdown. I'll notice that instead of a number filter, I have a date filter and have a lot of useful options for filtering my information here. We're gonna focus on before and then I'll type in three slash 1 slash 2 0 1 8. I'll press enter. 

And now it filtered for all the dates before 3/1/2018. Another way that you can clear the filter is simply to go to the sort and filter group and click clear. 

Subtotals

Finally, I want to apply subtotals. I want to figure out the total for the UK and the total for the US. Eh? But I don't necessarily want to create an auto sum function. I want the subtotal to be displayed in the table. So how can I do that? I can simply go over to the data tab and I'll move further to the right until I get to the outline group. Once I'm in the outline group, I will click Subtotal. 

Now, this dialog box can seem intimidating, but it's actually very simple once you approach it in a particular way. I'm going to approach this dialog box by thinking to myself, but I would like to create a sentence that makes sense for the result that I want. 

Here is the sentence I'm going to construct at each change in and I'll choose region. I would like to use the sum function and add a subtotal to the total column, not the region column, because I can't add up the region any way, that's text. That is the entire sentence. At each change in region, use the sum function to add a subtotal to the total column. 

I'm going to leave the other defaults the way they are. It's perfect the way that they're set up right now. All I need to do is click ok. And now I have a subtotal for the UK and USA. As a side effect, I have buttons to the left over by the row numbers where I can choose to group this information on multiple levels. 

Right now we're looking at level 3. If we go to level 1, we just see the grand total. And if I go to level 2, I see subtotals and grand totals. This is probably the sweet spot for what I would want for a summary report. I can then copy this information and paste it somewhere else to display a summary of all the UK USA totals as well as the grand total. If I want to return to my original table, all I have to do is click subtotal and then I'll choose the obvious option which is remove all. 

Full Exercise

Next, let's take a look at an exercise that's going to incorporate everything we just went over. I'll head over to the practice sheet. 

First, let's take a look at how these tasks are organized. The first two tasks deal with sorting. The next four tasks deal with filtering. The last task is a subtotal. So let's start with Task 1. I want to perform a sort by ordered date with the most recent on top. 

I'll click anywhere in the ordered date column and then I'll simply head over to Z to A. That's going to give me newest to oldest. I'll click that. Then I'll move to Task 2, because Task 1 is now complete. Perform a multi-level sort first by sales rep and then by total of sort. 

Now, I just look at my instructions first by sales reps. So I'll tell you, sales rep says it says A-to-Z. So I am going to choose A to Z. It says then by total. So I'll need to add a level. And it says, then by and I'll choose total largest to smallest order here for total says smallest to largest. I'll change it to largest to smallest. One click on. Okay. 

And now I complete a task to test three filter discover which customers are responsible for order ID 13411. I need to add a filter if I hover my mouse over the filter, I'll see the keyboard shortcut is control shift L so I might try that right here. Control shift L. Oh and I've added the filters. I'll click the dropdown and I'll go directly to the search bar and type 1 3 4 1 1. 

When I press enter I can see that the customer for that order number is the Dandelion. I'll press control C. I'll head over to the cell where I want to paste that value. I'll use control alt V and just paste the values which is V and press enter if I want to keep the background color which I did clear the filter. 

How many orders for Boston Crabmeat were sold by sales rep Fred? I'll click the dropdown only choose Boston crabmeat, then go over to sales rep and only choose Fred. I'd like to get a count. I can simply select the cells that contain the sales rep value and look down at the bottom and I get a count of nine. I'll go in and enter nine. Clear the filter. 

How many orders were placed after 2/1/2018. I'll go to the order date. I'll look for a date filter that's after. And then I'll type 2/1/2018. I also have the option to click on the date picker and choose my dates from this dialog box. I'm simply going to click. OK, now I could select the cells to get a count, but if I look down at the bottom left hand corner I see six of 502 so I’ll enter 6. Clear the filter. 

How many orders had a quantity less than four? So I'll go over to the quantity column. I won't use the number filter to look for a value for values that are less than and I'll type in four. I'll click OK. Down at the bottom. I see sixteen. So I'll go in and type sixteen. 

Finally, I want to sort the data by sales rep and then apply subtitles by sales rep, I'm going to clear the filter. Than anywhere within the table. I'm going to click on Subtotal before I do, though. You need to make sure that whatever you're grouping by is sorted in such a way that all the common values are together. So I'll go in. And she was a.D.A. Just to confirm that for sales rep. Then I'll go over to Subtotal. The sentence I'm constructing here is at each change in sales rep. I would like to use the sum function to add a subtotal to the total column. Now you can also come up with a subtotal of another column. As long as it's numerical. In this case, I'll get the subtotal for the shipping cost as well as the total one. Click on OK and I have my result. But this is quite a big table and it might take me awhile to scroll down before I get to the very first result. So I'll cut to the chase and simply go over to my grouping buttons and choose 2. I now see the total sales for each of the sales rep. And I also see the shipping cost. 

So now if I want to return to my original table, I also simply click subtotal and click remove all. 

Recap

So in this section, we took a look at how to sort and filter your information as well as how to apply subtotals.

This document has been edited with the free version of the instant HTML edior. Try it here and use it every time for your projects.