Search Contextures Sites
Custom Search

Excel Filters: Excel 2003 AutoFilter Basics

Use an Excel AutoFilter to hide some of the data in your worksheet. For example, you can focus on sales of a specific product, or print a list of your largest orders.

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

Prepare the Database

Filter the Database

Remove a Filter

Create a Custom Filter

Download the Sample File

  Excel AutoFilter 01
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.

 

Excel AutoFilter Tutorials

  1. Excel 2007 AutoFilter Basics
  2. Excel 2003 AutoFilter Basics
  3. Excel AutoFilter Tips
  4. Excel AutoFilter Programming

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

30 Excel Functions in 30 Days

 

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

 

Last updated: March 1, 2016 10:49 AM