Home » Basic-Excel » Excel-Filter

Related Page:  Excel Advanced Filter

Search this site: Custom Search 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. The user is then able to use the drop-down menu, to 'tell' Excel which rows to display, depending on the values in the cells of the current column.

Before activating the Excel Filter, it is advisable to select the header row or the header cells for the columns that you want to add the filter to. If you don't select a header row or cells, then Excel will attempt to 'guess' which cells are your headers and, as long as the current active cell is within the data range to be filtered, Excel will usually guess this correctly. However, there may be occasions when you need to specify which heading cells to add a basic Excel filter to - you do this by highlighting the cells.

The Excel Autofilter menu is slightly different in Excel 2003 and Excel 2007. Therefore Excel 2003 and Excel 2007 are addressed separately below:

Excel 2003 Basic Filter

Activating the Basic Excel Filter

In order to activate the basic Excel filter, 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.

The example spreadsheet on the right shows sales figures for different sales people, over 3 months. The basic Excel filter 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

Once selected, the (Top 10...) and the (Custom...) options are the same in Excel 2003 and Excel 2007. These options are described in the following 2 sections.

(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, Excel brings up an options box, as shown on the right. This 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...)

The (Custom...) option allows you to define a condition, which if satisfied by a cell, will result in the corresponding row being displayed. This option contains numeric 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 (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. You will see that, when you have a filter in your current spreadsheet, there is a tick next to the AutoFilter option. Selecting this option with the mouse, unticks the option and removes the filter.

Excel 2007 Basic Filter

Activating the Basic Excel Filter

In order to activate the basic Excel filter, 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.

The example spreadsheet on the right shows sales figures for different sales people, over 3 months. The basic Excel filter 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 a simple filter on the contents of the cells in the filtered column. These are:

• (Select All) - show all rows
• $x,xxx - Display if cell equals $x,xxx
• (Blanks) - Display all blank cells

The user can untick the values in rows that (s)he does not wish to display.

A new feature of Excel 2007 is the ability to filter by colour. This may be text colour or the colour of a cell background.

Also in the above image, is the option to apply number filters. Excel has displayed this option because the data in the filtered column is numeric (note that for columns containing text data, Excel offers text-specific filters instead, such as 'Begins With', 'Ends With', 'Contains', etc)

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

• Numerical Conditions - open up a box to define simple numerical condition and then display cells satisfying the condition
• (Top 10...) - Display rows containing the top N values
• (Above Average) - Display numerical values that are above the average value
• (Below Average) - Display numerical values that are below the average value
• (Custom Filter...) - Display rows satisfying one or more user-defined conditions

Further explanation of the (Top 10) and the (Custom) options are given 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, Excel brings up an options box, as shown on the right. This 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...)

The (Custom...) option allows you to define a condition, which if satisfied by a cell, will result in the corresponding row being displayed. This option contains numeric 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 (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 filter from an Excel 2007 spreadsheet, simply select the Data tab at the top of your spreadsheet, and from within this, click on the Filter option.

Where Did I Go Wrong?

Two commonly encountered problems with the Excel Autofilter are:

	The filter stops working part of the way down the spreadsheet
OR
	The 'Custom' filter fails to show rows that satisfy the specified condition

These are dealt with separately below.

For more information on the Excel filtering, visit the Microsoft Office website (link opens in a new window).