Contextures

Excel 2003 AutoFilter Basics

How to use Excel 2003 AutoFilter to show or hide specific data in a list, to focus on a product or customer, or print selected data

Note: For current Excel AutoFilter instructions, please go to Excel AutoFilter Basics

Prepare the Database

1. Set up the database

a) The first row (A1:D1) has headings.

b) Subsequent rows contain data.

c) There are no blank rows within the database -- you can leave some cells blank, but not an entire row.

d) There is a blank row at the end of the database, and a blank column at the right.

Excel Database

2. Turn on Excel AutoFilter

a) Select a cell in the database.
b) From the Data menu, choose Filter, AutoFilter.

A dropdown arrow appears beside each column heading.

Excel AutoFilter 02

Filter the Database

To filter the list, for example to view orders for one Product, choose a criterion from one of the dropdown lists. To further filter the list, choose from another column's dropdown list, e.g. Customer.

Rows that don't meet the criteria will be hidden. Rows that remain visible have a blue number in the row button. The dropdown arrow for column(s) in which a criterion has been applied will also be blue.

 Excel AutoFilter 03

Remove a Filter

To remove the filter, and leave AutoFilter turned on:

In each column in which a filter has been applied, choose (All), the first item in the dropdown list
 OR
From the Data menu, choose Filter, Show All

To remove the current filter, and turn off AutoFilter:

From the Data menu, choose Filter, AutoFilter

Excel AutoFilter 04

Special Filters

Blank Cells in a Column

If there are any blank cells in the column, the drop down list will contain two additional items -- (Blanks) and (NonBlanks).

Excel AutoFilter 05

Filter Highest and Lowest Numbers

To find the highest or lowest numbers in the table, choose (Top 10...) from the number column dropdown.

1. In the first box, choose Top or Bottom.
2. In the middle box, enter a number.
3. In the third box, choose Items or Percent.

Note: The results are the highest or lowest values for the entire list, not the currently filtered list. If other columns are also filtered, you may see fewer than the specified number of items.

Excel AutoFilter 06

Create a Custom Filter

When you choose a criterion from a dropdown list, the list is filtered for rows that are equal to the criterion. If you need more options while filtering, you can choose (Custom...) from the dropdown list. This opens the Custom AutoFilter dialog box.

To filter for one criterion:

a) From the first dropdown list, select an operator.
b) In the text box, type a value.
c) Click OK.

Excel AutoFilter 07

To filter for two criteria:

a) From the first dropdown list, select an operator.
b) In the text box, type a value.
c) Choose And or Or
d) From the second dropdown list, select an operator.
e) In the text box, type a value.
f) Click OK.

Excel AutoFilter 08

Download the Sample File

Download a zipped workbook with Excel 2003 AutoFilter sample data.

More Tutorials

Excel AutoFilter Basics

Excel AutoFilter Tips

Excel AutoFilter Programming

Search Contextures Sites

 

Excel Tools Add-in

 

 

 

Excel Data Entry Popup List

 

Last updated: November 22, 2016 7:02 PM
Contextures RSS Feed