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.
This page describes how to use the Autofilter in current versions of Excel (2007 and later). If you have Excel 2003, see the Excel Autofilter 2003 page.
To apply the Excel autofilter:
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.
Select the Filter option from Data tab on the Excel ribbon (see rightabove).
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).
Current versions of Excel also allow you to filter by color. This may be text color or the color of a cell background. The spreadsheet in this example has no color 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 this option will vary depending on the data within the filtered column. Columns containing dates will have date-specific filters and columns containing text will have text-specific filters.
The Excel filters in the Number Filters... category 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, Excel brings up a dialog box which contains 3 options:
Selecting the (Custom...) filter option causes the 'Custom Autofilter' dialog box to open. This dialog box allows you to define the conditions that specify which rows you want to be displayed.
The conditions that are presented to you in the 'Custom Autofilter' dialog box depend on the type of data that is present in the column to be filtered:
To remove the filter from your spreadsheet, simply select the Data tab on the ribbon at the top of your spreadsheet, and from within this, click on the Filter option.
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.
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 (i.e. '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 (i.e. 'is greater than', 'is greater than or equal to', 'is less than' or 'is less than or equal to') on text values.
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.
See the Microsoft Office website for further details on Excel filtering.