How to Filter an Excel Database
by docnmail.com - Free Online Courses for Everyone

Free Excel Course

• Introduction

• Data Form

• AutoFilter

• Uniques, Blanks...

• Display Top 10

• Custom Filters

• Match Selection

Advanced Filters

• Set up Worksheet

• Perform Filters

• Use Extract Range

More Free Courses:

Computer Courses

Excel Courses

Database Courses

All Topics

MS Excel books:
from Amazon.com

Excel 2003 Formulas

Microsoft is either a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries.

 

Page 9 of 10

Performing Advanced Filters

The criteria range may now be used to define multiple conditions for one or more columns.  To demonstrate how it works lets do a simple filter and work from there.

• define a condition under any heading in the criteria range - Example type >5 in the cell below Length (D2)
• click any cell inside the database range
• click Data on the menu bar, hover Filter, and select Advanced Filter

The Advanced Filter dialog box appears.

• Make sure that Filter the list, in place is selected

The ranges criteria and database are automatically inserted into the proper slots in the dialog.

Note: If you named the ranges something other that database and criteria, simply insert the cell references for each of the two ranges by typing them or highlighting the range on the worksheet.

• Chose Unique records only if you wish and click Ok.

Only the trails over 5 miles long remain displayed.

• Redisplay the entire database.  Data>Filter>Show All

Now, here are several types of advanced filters with examples using the BH Trails database.  Remember to redisplay the entire database after each filter and clear any data in the criteria range from a previous filter before performing a new one.  One more thing - there must always be a blank row of cells between the criteria range and the database range.  If you need more rows, add them, but be sure to add them directly under the heading row, rather than at the bottom of the range.  This will automatically adjust the range name criteria to include the new rows.

Multiple conditions in a single column
records meeting any of the conditions will be displayed

To find trail numbers 3, 5 and 7.

• Type each value in it's own cell under the heading TrailNo in the criteria range (cells C2, C3, C4).
• Click a cell in the database.  Data>Filter>Advanced Filter

Note that the criteria range and database ranges are defined already and have been adjusted if rows were added.

• Click Ok.

One condition in two or more columns
the record must meet all the conditions to be displayed

To locate difficult trails over 6000 feet, near Harney Peak

• Type *harney under the TrailGroup heading (cell A2)
• Type difficult under the Difficulty heading (cell E2)
• Type >500 under the ElevHigh heading (cell G2)
• Click a cell in the database. Data>Filter>Advanced Filter>Ok

One condition in one column or another
the record must meet at least one of the conditions to be displayed

To show the any trials that are either a mile or long or easy

• Type 1 under the Length heading (D2)
• Type easy under the Difficulty heading (E3)
• Click a cell in the database.  Data>Filter>Advanced Filter>Ok

One of two sets of conditions for two columns
two sets of records, meeting different criteria, are displayed

To show only the easy trails that are either less than a mile long or those more than a hundred miles long

• Type <1 in the Length & easy in the Difficulty fields of the first row (cells D2 & E2)
• Type >100 in the Length & easy in the Difficulty fields of the second row (cells D3 & E3)
• Click a cell in the database.  Data>Filter>Advanced Filter>Ok

As you can imagine your searches can become quite complex using the Advanced Filter features.

You may find it'll come in handy to have a separate section on the worksheet to write the filtered records to. The next section covers creating and writing to the Extract range.

Next Page - Using an Extract Range >>

Site Map  •  FAQs  •  Link to Us  •  Educational Gift Ideas  •  Add a Course  •  Contact Us

© 2008 Learning Link Finders, Inc. - All Rights Reserved - Modified on 01/01/08

All trademarks and registered trademarks appearing on the Docnmail.com website are the property of their respective owners.
By providing links to other sites, Learning Link Finders, Inc. does not guarantee, approve or endorse the information or products available
at these sites, nor does a link indicate any association with or endorsement by the linked site to Docnmail.com.