Filtering

AutoFilter is the quickest and easiest way to filter a table (or list) of data.
When we use the word "filter" we are just temporarily hiding the rows that we do not want to see.
This feature displays drop-down lists at the top of each column and allows the user to select unique values within each column in order to filter the data accordingly.
These drop-down lists can then be used either individually or in combinations to filter the data within your table.
When a table is "filtered" the rows that do not match the value you have chosen from the drop down list are hidden.

Filtering Restrictions

There are a three very important restrictions you need to be aware of:
1) Your table must contain column labels because any filter is not applied to the first row.
2) You can only have one AutoFilter per worksheet.
This means that you cannot have multiple filtered tables on the same worksheet.
3) An AutoFilter drop-down list will only show 1000 entries, and that setting cannot be changed.
If your column has more than 1000 "unique" items you might have to use the "custom" option from the drop-down list to enter your filter criteria. For more details, please refer to the AutoFilter - Custom page.

Applying the Filtering

You can apply auto filtering to a table of data by selecting any cell in the table (in this case "B5") and selecting (Data > Filter > AutoFilter).
Drop-down menus will then appear to the right of each column heading in the first row of the table.
Clicking the arrow on the drop-down list will display all the unique items in that column (in alphabetical order).

The options "All", "Top 10" and "Custom" will appear automatically.
The "Sort Ascending" and "Sort Descending" options will also appear automatically although these were only added in 2003.
If a particular column contains any empty cells then "Blanks" and "NonBlanks" will also appear automatically at the bottom of the list.

One Column Filtering

It is then extremely easy to filter the table so it only displays unique items from a particular column.
This is very useful when you have large table containing hundreds or maybe thousands of rows.
In this example we select "Steven" in the first drop-down list.
Any column that has a filter currently applied has its drop-down arrow changed to blue.
The row numbers of the filtered table also have been changed to blue. Again this is to remind you that the rows have been filtered.

All - To display all the rows in the table after a filter has been applied, select "(All)" in the drop-down list that contains a filter. Alternatively you can select (Data > Filter > Show All).
Top 10 - This is discussed in detail later on. For more details, please refer to the AutoFilter - Top 10 page.
Custom - This is discussed in detail later on. For more details, please refer to the AutoFilter - Custom page.

The bottom left corner of the status bar is used to indicate if a Filter has been applied to a table on the active worksheet.

Several Column Filtering

It is possible to refine your filtering even further by using drop-down lists in the additional columns.
Notice that when a table has been filtered by one column the other drop-down lists only display items from the "filtered" table.
You can filter by another criteria by selecting another item in one of the other drop-down list boxes.
Select the third drop-down list and select "22".
This will filter the list even more to only display rows that have 22 in column D.

The rows that do not have the number 22 in column "D" have been hidden.

Using Filtering (specific columns)

If you only want the drop-down lists to appear in certain columns, select only these column labels before selecting (Data > Filter > AutoFilter).
Note that the columns must be next to each other.
If you only want the drop-down list to appear in one column, select the first two cells in this column.
In this example we only want a filter drop-down list displayed for the first column so we select cells "B2" and "B3".

Removing the Filtering

To remove an AutoFilter altogether select (Data > Filter > AutoFilter) to remove the check box.
To display all the rows in the table after a filter has been applied, select "(All)" in the drop-down list that contains a filter. Alternatively you can select (Data > Filter > Show All).
Need a quick way to toggle/remove all filters ?

Remember

If you select more than one cell within a table before applying the AutoFilter only the selected cells will have the drop-down lists added and not the whole table. Unlike the Sort command there is no prompt or warning.
You can apply AutoFilter to a selection of columns in your data table by selecting the column labels first before selecting (Data > Filter > AutoFilter).
You can only have one AutoFilter per worksheet, which means that you cannot have multiple filters below each other ??
Once you have applied a filter, either using AutoFilter or Advanced Filter the table of data is given the named range "_filterdatabase". This name does not appear in the named range dialog box but typing this named range into the (Edit > GoTo) dialog box will select the whole table.

AutoFilter

Allows you to quickly display only the rows your are interested in.
This method can be used to only display the record
You can only have one AutoFilter per worksheet, which means that you cannot display several tables with different filters on the same worksheet.

You can change the filter at any time by just selecting another entry from one of the drop-downs
Using filters to locate records encourages you to maintain consistent data as any spelling mistakes or mis-typed entries will appear as additional entries in the AutoFilter drop-down lists.
To apply an Autofilter just select any cell in the list of data and select (Data > Filter > AutoFilter).
You do not have to include a header row on your table of data but if you don't then the first row will always be visible ??

Multi-Select in 2007

AutoFilter Restrictions

If you have over 1000 unique elements in your list there are a couple of workarounds. Either split your list into two smaller lists that do not contain more than 1000 unique elements or extract a number of characters from the front of a column filter by that and then by the original column.

Search Filters

Type in the box to quickly filter with an expression
to remove all column filters use the Clear button on the Data tab next to Filter

Slicers

Slicer Tools - Options Tab

Dates

Text

Number Filters

Number of Rows

As soon as you have performed a filter on a table of data, the total number of rows that have matched your condition is displayed in the status bar. Bottom left hand corner.
This however is only displayed for a short period of time. After that the status bar will display the text "Filter mode" to indicate that there is a table of data currently filtered on the worksheet.

The following mesage is displayed in the bottom left corner of the status bar.

Using the SUBTOTAL worksheet function

The subtotal worksheet function can returns the total numbers of displayed values in a list or database column.
The advantage of the SUBTOTAL function is that it only displays visible rows.

The first parameter passed to the SUBTOTAL() indicates which function to use.
Passing the number 3 indicates that we are using the COUNTA() function
Passing the number 9 indicates that we are using the SUM() function
Alternatively if your list of data is going to change regularly then an alternative to using the cell reference "D3:D12" is to refer to the whole column.
The formula for this would be: =SUBTOTAL(3,D:D)-1

In this case the function returns the total number of cells in the "whole" column that are visible and not empty.
You must be sure to subtract the number of cells that you do not wish to include.
In this case there is one cell which we do not want to include. D2, which is the heading column.
Note that this formula is not in column D, but in column E. The reason for this is to prevent a circular reference.

Remember

The bottom left corner of the status bar is used to indicate if a filter is currently being applied.

Top 10

Selecting (Top 10) in a drop-down list allows you to display a certain number of items from the top or bottom of the column.
You can also use this option to display a certain percentage of items in the column.
This can only be used on Numerical Columns and not text columns.
In column "D" select the drop-down arrow and select (Top 10).

This will display the "Top 10 AutoFilter" dialog box.

Top 4 Items

In this example we are going to display the top 4 items from the Age column.
Select (Top 10…) to display the "Top 10 AutoFilter" dialog box.
Select "Top" in the first drop-down, "4" from the spinner control and then "Items" in the last drop-down list.

Press OK to display the top 4 items in the "Age" column.

Bottom 20 %

In this example we are going to display the bottom 20% of items from the "Age" column.
Select (Top 10…) to display the "Top 10 AutoFilter" dialog box.
Select "Bottom" in the first drop-down, "20" from the spinner control and then "Percent" in the last drop-down list.

Press OK to display the top 4 items in the "Age" column.

By Color