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 8 of 10

Advanced Filters - Setting up the Worksheet

Advance Filters make it possible to retrieve records from an Excel database based on multiple conditions for one or more columns.

Advance Filters do not use the AutoFilter drop box arrows.  Instead, a criteria range is created and used to define the conditions for the search.  Like using AutoFilter, records that do not meet the conditions defined in the criteria remain part of the spreadsheet, but are hidden until you redisplay them.   I will once again use the Black Hills Trails database to demonstrate how this is done.

If you skipped the first chapter on simple filters - please note - the link above is to a database of the trails in the Black Hills.  You're welcome to copy it and use it to practice.  There is also a Comparison Operator and Wildcard cheat sheet here.

Set up the worksheet

Open Excel and the database.

For the criteria range to work properly there must be at least three blank rows at the top of the worksheet.  If you're using the Black Hills Trails database clear the contents of cell A1 - "back to the course".  Those using their own databases simply need to insert blank rows at the top if necessary so that least rows A1-A3 are clear.

Note:  Do not use the space bar to clear the contents of a cell.  A space is considered data and makes a cell no longer Blank.  Instead, press the delete key or right click the cell or selected range of cells and use Clear Contents.

Name the database range - named ranges are easier to refer to than cell ranges.

	Select (highlight with your mouse) all the rows and columns of the database, include the headings, but do not include any blank rows or columns bordering the database. A5:J71 of the Black Hills Trails database.
	Click Insert on the menu bar hover Name and click Define.

The Define Name dialog box appears.  The first box in the dialog automatically picks up the value that is located in the first cell of the range.  In our case TrailGroup and the range of the highlighted cells is defined in the refers to range. 

	Type database where TrailGroup is, leave the range as is
	Click Ok.
	Click any cell in the database to de-select the range

Create the criteria range

Copy the Headings row of the database to Row A1. The worksheet should look similar to this.

	Highlight the newly copied Headings and the blank cells directly below them. (A1:J2)
	Name the range criteria - Insert>Name>Define

Next Page - Performing Advanced Filters >>

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.