In Excel, you can create simple filters and complex filters. Let’s start with simple filters. When working with filters, there should always be one line at the top, which is used for labels. This line is optional, but it makes working with filters a little easier.
Above, I have fake data and I want to create a filter on the City column. It’s really easy to do this in Excel. Go ahead and click the Data tab on the ribbon and then click the Filter button. You don’t need to select data on the sheet or click on the first row.
When you click Filter, a small dropdown button on the right is automatically added to each column in the first row.
Now click on the drop-down arrow in the City column. You will see several options which I will explain below.
At the top, you can quickly sort all rows by values ??in the City column. Note that sorting the data will move the entire row, not just the values ??in the City column. This ensures that your data remains the same as before.
It is also a good idea to add a column at the very beginning called ID and number it from one to the number of rows you have on your sheet. This way, you can always sort the data by the ID column and return the data in the same order in which it was originally, if that’s important to you.
As you can see, all the data in the spreadsheet is now sorted based on the values ??in the City column. So far, no row is hidden. Now let’s take a look at the checkboxes at the bottom of the filter dialog. In my example, I only have three unique values ??in the City column and these three appear in the list.
I went ahead and unchecked two cities and left one checked. Now I only have 8 rows of data showing and the rest are hidden. You can easily tell you are looking at filtered data by checking the line numbers on the far left. Depending on how many lines are hidden, you will see some additional horizontal lines and the color of the numbers will be blue.
Now suppose I want to filter on the second column to further reduce the number of results. Column C shows the total number of members in each household, and I only want to see results for families with more than two members.
Go ahead and click on the dropdown arrow in column C and you will see the same checkboxes for every unique value in the column. However, in this case, we want to click Number Filters and then click Greater Than. As you can see, there are other options as well.
A new dialog box will appear where you can enter the filter value. You can also add more than one criterion using the AND or OR function. You could say that you want strings that have a value greater than 2, not 5, for example.
Now I have only 5 rows of data left: families from New Orleans only and with 3 or more members. Simple enough? Note that you can easily clear the filter for a column by clicking the drop-down list and then clicking the Clear Filter by Column Name link.
That’s it for simple filters in Excel. They are very easy to use and the results are pretty obvious. Now let’s take a look at complex filters using the Advanced Filters dialog box.
Creating advanced filters in Excel
If you want to create more advanced filters, you should use the Advanced filter dialog. For example, let’s say I wanted to see all families that live in New Orleans with more than 2 members in their family OR all families in Clarksville with more than 3 members in their family AND only those with an email address ending in .EDU. Now, you cannot do this with a simple filter.
To do this, we need to set up the Excel sheet a little differently. Paste a couple of rows above the dataset and copy the header labels exactly to the first row as shown below.
This is how advanced filters work. You must first enter criteria in the columns at the top, and then click the Advanced button in the Sort & Filter section of the Data tab.
So what exactly can we put in these cells? Ok, let’s start with our example. We only want data from New Orleans or Clarksville, so let’s put that in cells E2 and E3.
When you enter values ??on separate lines, it means OR. We now need families in New Orleans of more than two and families in Clarksville of more than three. To do this, enter> 2 in C2 and> 3 in C3.
Since> 2 and New Orleans are on the same line, this will be the AND operator. The same is true for line 3 above. Finally, we only need families with a .EDU final email address. To do this, simply enter * .edu in D2 and D3. The * symbol means any number of characters.
Once you’ve done that, click anywhere in your dataset and then click the Advanced button. The List Range field will automatically detect your dataset since you clicked on it before clicking the More button. Now click the small button to the right of the Criteria Range button.
Select everything from A1 to E3 and then click the same button again to return to the Advanced Filter dialog box. Click OK and your data should now be filtered!
As you can see, right now I only have 3 results that meet all of these criteria. Note that the labels for the criteria range must match exactly with the labels for the dataset for this to work.
Obviously, you can create much more complex queries using this method, so experiment with it to get the results you want. Finally, let’s talk about applying summation functions to filtered data.
Summarize the filtered data
Now suppose I want to sum the number of family members based on my filtered data, how can I do that? Well, let’s clear our filter by clicking the Clear button on the ribbon. Don’t worry, it’s very easy to reapply the advanced filter just by clicking the More button and clicking OK again.
At the bottom of the dataset, let’s add a cell called Total and then add a sum function to add up the total number of family members. In my example, I just typed = SUM (C7: C31).
So if I look at all the families, I have 78 members. Now let’s apply an advanced filter and see what happens.
Oh! Instead of showing the correct number, 11, I can still see that the total is 78! Why? Well, the SUM function doesn’t ignore hidden rows, so it still performs calculations using all rows. Fortunately, there are several functions you can use to ignore hidden lines.
The first is an INTERMEDIATE TOTAL. Before we use any of these special functions, you need to clear the filter and then enter the function.
After clearing the filter, enter = INTERMEDIATE TOTAL (and you will see a drop-down box with many options. Using this function, you first select the type of summation function you want to use using a number.
In our example, I want to use SUM, so I enter the number 9, or just click on it in the dropdown. Then enter a comma and select a range of cells.
When you hit Enter, you will see the value 78 is the same as before. However, if you apply the filter again now, we see 11!
Excellent! This is what we want. Now you can set up filters and the value will always reflect only the rows that are currently displayed.
The second function, which works in much the same way as the INTERMEDIATE TOTAL function, is UNIT. The only difference is that the AGGREGATE function has one more parameter in which you must indicate that you want to ignore hidden lines.
The first parameter is the sum function you want to use, and as with the INTERMEDIATE TOTAL 9 represents the SUM function. In the second option, you need to enter 5 to ignore hidden lines. The last parameter is the same, it is a range of cells.
You can also read my article on pivot functions for more details on how to use the UNIT function and other functions like MODE, MEDIAN, AVERAGE, etc.
Hopefully this article is a good starting point for creating and using filters in Excel. If you have any questions, do not hesitate to leave comments. Enjoy!
–