Data Organization: Sorting, Filtering, and Subtotals in Excel

Sort and filter. 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 your 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. Now the disadvantage of this location is you have to click the drop down to access any of the options. The advantage is that it's on the home tab.

If you're on the home tab already, it's easy to get to. Another place where you can find sort and filter is on the data tab. The benefit of using sort and filter options from here is that you don't have to click on a drop down.

You can go right away to sort A-to-Z, Z to A, and so on. 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? 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 Z 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 hover 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. 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 Z to A, A-to-Z, Z to A, A-to-Z. No amount of clicking on these two options are going to 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. 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 drop down 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 and then I'll go and choose the font color. It doesn't seem to be working. Why? Well, I'm in the quantity column.

There is no font color in the 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 drop down, 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. Now I'd like to sort the region and the total.

In other words, I want to create a multi-level sort. 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 I'll add a level and here I'll choose region. I want those values sorted A-to-Z. Now I could also copy a level.

If I like the settings for this level, I can duplicate it and make one change to one of the drop downs 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 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. 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'll click OK. 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 drop down here, choose clear filter and now I have my table back with all the information. Now I want to filter for all the dates that are before 3-1-2018. Well, I'll go and click the drop down.

I'll notice that instead of a number filter, I have a date filter and I have a lot of useful options for filtering my information here. We're going to focus on before and then I'll type in 3-1-2018. I'll press ENTER and now I 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. Finally, I want to apply subtotals. I want to figure out the total for the UK and the total for the USA, 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 a particular way. I'm going to approach this dialog box by thinking to myself that I would like to create a sentence that makes sense for the result that I want. I want to create a sentence that makes sense for the result that I want.

Here's 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 anyway, 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 okay. And now I have a subtotal for 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 three. If we go to level one, we just see the grand total.

And if I go to level two, 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 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. 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. And the last task is a subtotal. So let's start with task one.

I want to perform a sort by order date with the most recent on top. I'll click anywhere in the order 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 two because task one is now complete. Perform a multi-level sort first by sales rep and then by total.

I'll click sort. Now, I just look at my instructions first by sales rep. So I'll choose sales rep.

It says A-to-Z. So I'm going to choose A-to-Z. It says then by total. So I'll need to add a level. And it literally 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 OK. And now I've completed task two.

Task three. Filter to discover which customer is responsible for order ID 13411. I need to add a filter.

If I hover my mouse over filter, I'll see the keyboard shortcut is control shift L. So I might try that right here. Control shift L. And I've added the filters. I'll click the drop down and I'll go directly to the search bar and type 13411.

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.

I'll clear the filter. How many orders for Boston Crabmeat were sold by sales rep Fred? I'll click the drop down. 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 okay.

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 six. Clear the filter.

How many orders had a quantity less than four? So I'll go over to the quantity column. I'm going to use the number filter to look for values that are less than and I'll type in four. I'll click okay.

Down at the bottom, I see 16. So I'll go in and type 16. Finally, I want to sort the data by sales rep and then apply subtotals by sales rep.

I'm going to clear the filter. Then 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 choose A-to-Z 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 okay and I have my result.

But this is quite a big table and it might take me a while 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 two. I now see the total sales for each of the sales rep and I also see the shipping cost.

Now if I want to return to my original table, I'll simply click subtotal and click remove all. So in this section, we took a look at how to sort and filter your information as well as how to apply subtotals.