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

Free Excel Course

Simple Filters

Introduction

Data Form

AutoFilter

Uniques, Blanks...

Display Top 10

Custom Filters

Match Selection

Advanced Filters

Set up Worksheet

Perform Filters

Use Extract Range

Bookmark Course

More Free Courses:

Computer Courses

Excel Courses

Database Courses

All Topics

MS Excel books:
from Amazon.com

Excel 2003 Bible

Microsoft Office Excel 2003 Inside Out

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 >>

Online Courses
Excel Basics for Beginners

This is a class for people who just cannot get the hang of Excel and would love to learn it. In this class you will learn to explore the Excel interface. How to Enter and Edit Data, Work with Excel documents & Help, how to enter Formulas & Functions, Formatting Data, how to Print worksheets, customizing Excel to the way you work, (which entails creating and modifying templates, Displaying and hiding toolbars, Add buttons to existing toolbars, add items to existing menus & how to create a new toolbar). You will also learn how to sort and filter worksheet data, Use charts to display data and how to enhance your worksheets with graphics.

Microsoft Excel 2002 Tutorials
First 3 lessons are free.  VTC Online University is one of the most valuable training resources on the web. For just $30 (USD) per month you will have online access to this tutorial set and in-depth training on over a hundred other of today's most sought-after applications - tutorial CDs are also available.

Microsoft Excel

Books at Amazon.com

Excel 2003 Bible

Excel 2003 Bible

Excel 2003 for Dummies

Excel 2003 for Dummies

Excel 2003 Formulas

Excel 2003 Formulas

Excel Best Practices for Business: Covers Excel 2003, 2002, and 2000

Excel Best Practices for Business: Covers Excel 2003, 2002, and 2000

Microsoft Office Excel 2003 Inside Out

Microsoft Office Excel 2003 Inside Out

 

 

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.