The Basic Excel Filter (Excel AutoFilter)


The basic Excel filter (also known as the Excel Autofilter) allows you to view specific rows in an Excel spreadsheet, while hiding the other rows in the worksheet.

When a filter is added to the header row of a spreadsheet, a drop-down menu appears on each cell of the header row. This provides you with a number of filter options that can be used to specify which rows of the spreadsheet are to be displayed.

The Excel Autofilter menu was updated in Excel 2007, so is slightly different in current versions of Excel, compared to Excel 2003. Therefore the different versions of Excel are discussed separately below:

Excel Autofilter Filter for Current Versions of Excel (2007 and Later)

Activating the Excel Autofilter

Before applying the Excel autofilter, first click on the range of cells that you wish to filter. Excel will automatically select the current data range when applying a filter. Alternatively, you can select the entire range of cell that you want to apply the filter to. This might be necessary if the range of cells that you want to filter has missing rows or columns of data.

Autofilter Selection in Current Versions of Excel

Next, select the Data tab at the top of your spreadsheet, and from this, select the option Filter.

You should now have the drop-down menus on each of your header cells, which can be used to select the rows to be displayed (see below).


Autofilter Display in Current Versions of Excel

The above example spreadsheet on the right shows sales figures for different sales people over 3 months. The basic Excel filter has been applied to all three columns and the drop-down menu for the 'Sales' column is displayed.

It is seen that the drop-down menu offers a check list for the contents of the cells in the filtered column. The user can opt to display all rows or to display cells containing one or more selected values (specified by checking / unchecking values from the list).

A new feature of Excel 2007, compared to Excel 2003 is the ability to filter by colour. This may be text colour or the colour of a cell background. The spreadsheet in this example has no colour applied to the cells to be filtered, so the 'Filter by Color' option has been greyed out.

Also, the above image shows the option to apply number filters. Excel has displayed this option because the data in the filtered column is numeric. However, columns containing dates will have date-specific filters and columns containing text data, will have text-specific filters.

The Excel filters in the Number Filters... category are:

Further explanation of the (Top 10) and the (Custom) options are given below.

(Top 10...)

Autofilter Top10 Option

The (Top 10...) option is only available for columns that Excel identifies as containing numeric values.

This Excel filter option allows you to display only the N highest or the N lowest values from the current column. If you click on this option, Excel brings up an options box, which contains 3 options:

  1. Select the Top (highest) values or the Bottom (lowest) values
  2. Option to select the number, N
  3. Select N Items or N Percent of entries to be displayed

(Custom...)

Autofilter Custom Option

The (Custom...) option allows you to define a condition which, if satisfied by the contents of a cell, will result in the corresponding row being displayed. This option contains numeric (or date) specific conditions and text specific conditions. However, some of the options are only suited to text data and some are only suitable for numeric data.

If your column contains numerical values, you can use the criteria 'equals', 'does not equal', 'is greater than', 'is greater than or equal to', 'is less than' or 'is less than or equal to'.

Note that, if the column contains dates, instead of the 'less than' and 'greater than' criteria, you will be presented with 'before' and 'after' options.

If your column contains text values, you can use the criteria 'equals', 'does not equal', 'begins with', 'does not begin with', 'ends with', 'does not end with', 'contains' or 'does not contain'

Up to 2 criteria can be combined, separated by an 'and' or an 'or'.

Removing the Excel Filter

To remove the filter from your spreadsheet, simply select the Data tab at the top of your spreadsheet, and from within this, click on the Filter option.



Excel Autofilter for Excel 2003

Activating the Basic Excel Filter

Before applying the Excel autofilter, first click on the range of cells that you wish to filter. Excel will automatically select the current data range when applying a filter. Alternatively, you can select the entire range of cell that you want to apply the filter to. This might be necessary if the range of cells that you want to filter has missing rows or columns of data.

Autofilter Menu in Excel 2003

In order to activate the Excel autofilter, select the Data menu at the top of your spreadsheet, and from this, select the option Filter and then the option AutoFilter.

You should now have the drop-down menus on each of your header cells, which can be used to select the rows to be displayed.


Autofilter Display in Excel 2003

The above example spreadsheet on the right shows sales figures for different sales people over 3 months. The Excel autofilter has been applied to the header rows of the three columns and the drop-down menu for the 'Sales' column is displayed.

It is seen that the drop-down menu offers numerous filtering options. These are:

Once selected, the (Top 10...) and the (Custom...) options are the same in all versions of Excel (from 2003 onwards). These options are described in the following 2 sections.

(Top 10...)

Autofilter Top10 Option

The (Top 10...) option is only available for columns that Excel identifies as containing numeric values. This Excel filter option allows you to display only the N highest or the N lowest values from the current column. If you click on this option, Excel brings up an options box, which contains 3 options:

  1. Select the Top (highest) values or the Bottom (lowest) values
  2. Option to select the number, N
  3. Select Items or N Percent of entries to be displayed

(Custom...)

Autofilter Custom Option

The (Custom...) option allows you to define a condition, which if satisfied by the values in a cell, will result in the corresponding row being displayed. This option contains numeric specific conditions and text specific conditions. However, some of the options are only suited to text data and some are only suitable for numeric data.

If your column contains numeric values (including dates), you can use the criteria 'equals', 'does not equal', 'is greater than', 'is greater than or equal to', 'is less than' or 'is less than or equal to'.

If your column contains text values, you can use the criteria 'equals', 'does not equal', 'begins with', 'does not begin with', 'ends with', 'does not end with', 'contains' or 'does not contain'

Up to 2 criteria can be combined, separated by an 'and' or an 'or'.

Removing the Excel Filter

To remove the basic Excel filter, simply select the Data menu at the top of your spreadsheet, and from this, select the option Filter and then the option AutoFilter. Unchecking the AutoFilter option removes the filter.


Common Problems with the Excel Autofilter

Two commonly encountered problems with the Excel Autofilter are:

The Filter Stops Working Part of the Way Down the Spreadsheet

The 'Custom' Filter Fails to Show Rows that Satisfy the Specified Condition

These are dealt with separately below:

Common Problem No. 1: Filter Stops Working Part Way Down the Spreadsheet

If your Excel Autofilter works for the top part of the data in your spreadsheet, but stops working part way down, this is probably because you have added more data since initially activating the autofilter or because you had a blank row in your data when you activated the filter.

Solution

In this case:

  • Remove the autofilter.
  • If your data has blank rows, use the mouse to manually select the entire data range.
  • Activate the autofilter again.
This causes the new data to be added to the range of filtered cells.

Common Problem No. 2: The 'Custom' Filter Fails to Display Rows That Satisfy the Specified Condition

If you use the (Custom...) option and the condition that you specify fails to work, this may be because you are using a text condition (ie. 'begins with', 'does not begin with', 'ends with', 'does not end with', 'contains' or 'does not contain') on numerical values, OR because you are using a numerical condition (ie. 'is greater than', 'is greater than or equal to', 'is less than' or 'is less than or equal to') on text values.

Solution

In this case, you can get the filter to work as expected by changing the column data to the type that relates to the condition you wish to apply. For example, to get the 'begins with' condition to work on a column of numbers, you will need to convert the numbers to text before applying the Custom filter. One way to do this is using Excel's Text To Columns tool:

  1. Use the mouse to select the cells you want to convert to text (this must not span more than one column)
  2. From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
  3. Click next and then next again
  4. You should now be offered a selection of Column Data Formats. Select Text and click the Finish button

The data in your selected cells should now be stored as text within Excel.

Similarly, you could have chosen to convert text values (consisting of numbers) to numerical values, by simply selecting the column data format General within the Text To Columns tool.


For more information on the Excel filtering, visit the Microsoft Office website.