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

Creating and Using an Extract Range

You may create an extract range on the worksheet for Excel to display the filtered records in. Using an Extract range allows the complete database to remain displayed in it's original position, while the requested records are extracted and displayed for you to work with on another area of the worksheet.

Create the Extract Range

• Copy the row of headings to the bottom of the worksheet leaving at least one blank row between them and the end of the database (A75:J75).
• highlight the headings - do not include a blank row as with the criteria range (A75:J75)
• Click Insert>Name>Define, enter extract for the name & verify the range is A75:J75.
• Click Ok

Excel will now automatically find the range as the Extract destination for Advance Filters.

Note:  By including only the heading cells in the range - you are basically telling Excel that there is no limit to the space it can use to show the results.  Including one or more blank rows below the heading row tells Excel that it can not go beyond the designated range to show results.  In this case when more records are returned than will fit in the range you are prompted by an alert box to instruct as to  whether you want the data below the range deleted so that all the results may be displayed.  Answering no will result in a partial list of the results starting with the first record Excel found matching the criteria.

Using the Extract Range

To perform a filter and write results to the extract range, to the following:

• Define the filter criteria in the criteria range
• Click any cell of the database
• Data>Filter>Advanced Filter presents the Advanced Filter dialog.  Change the action to Copy to another location.  Enter the cell references for the Extract range if they are not already displayed.
• Click Ok.

Each of the records meeting the specified criteria is copied to the extract range.  The database range remains intact, displaying all 66 records.

Any of the Advanced Filter types discussed earlier may be performed using the extract range to display the records.

Note:  It is not necessary to clear the extract range after one filter to perform another.  Results from a filter automatically overwrite those that are currently there.  Even if a filter resulting in only one record is performed after one that displayed 100, the single record will display because the whole extract range is cleared first.

That completes the course.  I hope you enjoyed it and have a better understanding of the Filtering options you have in Excel.

Visit my site to find thousands of links to Free Online Courses and Tutorials.

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.