Basic Filter (Autofilter) in Excel 2003
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.
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.
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.
Activating the Basic Excel Filter
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).
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.
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:
- (All) - show all rows;
- (Top 10...) - Display rows containing the top N values;
- (Custom...) - Display rows satisfying a supplied condition;
- Value Selection - Display rows having the selected value;
- (Blanks) - Display all blank cells;
- (NonBlanks) - Display all non-blank cells.
The (Top 10...) and the (Custom...) options are described below.
(Top 10...)
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:
- Select the Top (highest) values or the Bottom (lowest) values;
- Option to select the number, N;
- Select Items or N Percent of entries to be displayed.
(Custom...)
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'.
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.
Excel Autofilter Problem
Excel Autofilter Common Problem: 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:
To resolve this problem:
- Remove the autofilter;
- If your data has blank rows, use the mouse to manually select the entire data range;
- Activate the autofilter again.