|
How to Filter an
Excel Database |
|||||
|
Free Excel Course Simple Filters Advanced Filters More Free Courses:
MS Excel books: Microsoft Office Excel 2003 Inside Out
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.
Create the criteria range Copy the Headings row of the database to Row A1. The worksheet should look similar to this.
|
|
|||
|
Site Map • FAQs • Link to Us • Educational Gift Ideas • Contact Us © 2012 Learning Link Finders, Inc. - All Rights Reserved - Modified on 05/08/12
All trademarks and registered trademarks
appearing on the Docnmail.com website are the property of their respective
owners. |
|||||
