This page describes how to use the Autofilter in Excel 2003. If you have a more recent version of Excel, see the Excel Filter (2007 and later) page.
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.
When a filter is added to the header row of a spreadsheet, a drop-down menu appears in 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.
To apply the autofilter in Excel 2003:
First click on the range of cells that you want to filter.
Excel should automatically detect the current data range when applying the autofilter. However, if the range of cells that you want to filter has missing rows or columns of data, Excel will not select all the data, so you may need to manually select the range of cells that you want to apply the filter to.
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 (see rightabove).
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:
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, this brings up a dialog box, which contains 3 options:
The (Custom...) filter option pops up a dialog box, which allows you to define conditions that specify the rows to be 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'.
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.