Filtering Data in Microsoft Excel

Excel offers two types of filters - Auto Filter and Advanced Filter.

First download the sample spreadsheet that we will use for this exercise here.

Auto Filter - The simple way to quickly find the records you want

Select any cell inside the table, then select Data > Filter > AutoFilter. The Auto Filter drop down menus appear.

Example 1

Lets say we want to show only those customers who bought apples. From the Products drop down menu select Apples. Only those records that contain the product apples are now shown. To show all records again select Data > Filter > Show All or select (All) from the Products drop down menu.

Example 2

We now want to view the top 7 sales by amount. Select (Top 10...) from the Amount drop down menu, change the number from 10 to 7 and then press OK. The top 7 records by amount are now shown. The Top 10 filter can be used to show the Top or Bottom 10 records or the Top or Bottom 10% of records - of course you can change 10 to be any number you wish.

Example 3

Auto Filters can also have custom criteria and all of your Auto Filters can be used simultaneously. Suppose we want to show only those records for the South Region where sales are more than £2,000. Select South from the Region Auto Filter and then Select (Custom...) from the Amount Auto Filter - now select is greater than from the first drop down menu and then type 2,000 in the next field to the right - press OK - the desired result is shown.

Caution - If you combine a Top 10 Auto Filter with say a Region Auto Filter - You will not get the Top 10 records for that region only - Excel will perform the Top 10 Filter on all records and then only show you those records that match the criteria you entered in the Region Auto Filter. Try it and see.

Example 4

Custom Auto Filters can also use wildcards. You can use wildcard characters if you are not sure of the spelling of your search term to expand your search.

Asterisks (*) substitute for a string of characters of any length.

Question marks (?) substitute for a particular character.

Lets say we want to find all the records where the customer is a PLC and the Product name includes the letters "an". Select Custom from the Customer Auto Filter and type *PLC into the field on the right and then in the Custom Auto Filter for Products type *an*. Of course you can achieve the same results without using the wildcard characters by using the ends with and contains criteria from the drop down menu on the left in the Custom Auto Filter dialog box.

Tip - To toggle AutoFilter off select Data > Filter > AutoFilter.

Tip - Any Auto Filter that has filtering criteria will have a blue colored down arrow.

Tip - Auto Filters can also be used to sort your records in ascending (lowest on top) order or descending (highest on top) order. Just select the desired option from the relevant Auto Filter drop down menu.

Question: How do you remove the Region and Product Auto Filter drop down menus while leaving the Customer, Month and Amount Auto Filter drop down menus in place ?

Solution: In VBA enter the following code:

Sub HideAutoFilter()

With Cells(1, 1)
.AutoFilter Field:=2, VisibleDropDown:=False
.AutoFilter Field:=4, VisibleDropDown:=False
End With

End Sub

Question: I have a table containing my data and I would like to use AutoFilter to filter my records according to the background color. Is there a way to do this ?

Solution: Create a Custom Function by entering the following code into VBA:

Function InteriorColor(CellColor As Range)
InteriorColor = CellColor.Interior.ColorIndex
End Function

Now add a column to your data and then use the function to return the Interior Color Index of the row - and then use this to set your criteria. The sample spreadsheet above contains an example. The function does not recalculate automatically when you apply a new color to a cell - so you should press Shift + F9 before applying your filter.

Advanced Filter - Complex but powerful

We will use the same spreadsheet for Advanced Filter. Toggle AutoFilter off by selecting Data > Filter > AutoFilter.

Firstly copy the headings in range B7:F7 to B1:F1. We can now use rows 2 to 6 for our criteria.

Example 1

Advanced Filter can be used to create a list of unique records. Suppose we want a list of all unique customers - select Data > Filter > Advanced Filter. In the List range enter B7:B107, then click Unique records only and click OK. Excel will display the 5 unique customers in place of the table. We also have the option to Copy to another location - which is probably better for displaying a single column of unique items under its field heading.

Example 2

In this example we want to view all records for Oranges and those records for the East Region where the Amount is more then £3,000. In cell E2 type Oranges, in cell C3 type East and in cell F3 type >3000. In the Advanced Filter dialog box enter B7:F107 into the List range, enter B1:F3 into the Criteria range, and enter H7 into Copy to. Click OK - the results are shown.

Note - Criteria that are entered horizontally in rows are an AND operator, and criteria that are entered vertically in columns are an OR operator.

Example 3

It is possible to copy only certain fields to another location. Lets say we only want to view the Customer and Amount fields for the criteria in example 2. Type Customer in cell H7 and and Amount in cell I7. Set the List range and Criteria range as in example 2 - Now select Copy to another location and type H7:I7 in the Copy to box and then click OK.

Example 4

Criteria can include conditions created as a result of a formula. The formula must return a value that is either True or False. The criteria is met and the records displayed when their value is True.

In this example we want to find all those records where the Amount is at least 20% greater than the average amount. Each record will either be equal to or greater than the average plus 20% or not - i.e. True or False.

In cell F3 type =F8>=AVERAGE($F$8:$F$107)*1.2 - Note the following:

The cell above (cell F2) is blank - the Criteria range must include this cell.

The cell being evaluated (cell F8) is a relative reference - when Excel performs the filter it will evaluate all cells in column F not just F8.

Cell F8 must relate to the column that the criteria relates to (column F) and the first record within the table (row 8).

The criteria contains a range that is an absolute reference - although only the rows need to be absolute, the columns do not need to be.

Using Advanced Filter type F2:F3 as the Criteria range and then Copy to another location and click OK - Excel will now display those records where the amount is equal to or greater than the average plus 20%.

Example 5

Suppose we wanted to view all the records for either Mill Limited or Ocean PLC or Timber Inc, for either East or West or North, and for either Apples or Lemons or Oranges - our criteria would need to look like this:

Because there are 3 fields, each with 3 criteria and we want to view all possible combinations of these criteria - we have 3 x 3 x 3 = 27 rows of criteria. If we had 6 fields and 8 criteria for each field then we would need 8 x 8 x 8 x 8 x 8 x 8 = 262,144 rows of criteria.

Such criteria would be impossible to build - the solution is to use conditions created as a result of a formula.

First - build your criteria list as follows:

And then in cell B6 type =NOT(ISNA(MATCH(B8,B$2:B$4,FALSE))) and then copy this formula across to cells C6 and E6. We can examine this formula as follows:

The Match function looks to find B8 inside the range B2:B4 and then return the relative position if found or #N/A if not found.

We want the result of the formula to be either True or False - so the function ISNA is used to return False if B8 was found and True if B8 was not found.

But we want the result to be True if B8 was found and False if B8 was not found - so the Not function is used to reverse result of the ISNA function from True to False or from False to True.

Now filter your data using the results of these formulas as your Criteria range - remember that your Criteria range must include the blank row above the formulas (so the Criteria range will be B5:F6). The results should look like this:

This is the end of our tutorial on Filtering Data in Microsoft Excel.

Return to Excel Exchange homepage.