|
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 6 of 10 AutoFilter - Custom Filters The option Custom allows you to apply 1-2 criterion to the filter using Comparison Operators and Wildcard Characters. Cheat Sheet In each of the filters previously covered in this course, the comparison operator used by Excel to filter the records is "equal to". When clicking a unique entry in the drop box - the command "return records where this field is equal to this" is issued. Even the Top 10 filter issues a command something to the effect "return the first 10 records where this field is equal to..." To allow more complex filters to be performed Excel includes, in each of the drop boxes, the Custom option. The option gives you a chance to change Comparison Operators and use Wildcards in place of actual text. Choose Custom from any one of the drop boxes. I'll use the column Uses in the following example. The Custom AutoFilter dialog box appears.
Click Ok. The only records remaining displayed are the trails where horses are allowed and cross country skiing is not, such as at Mount Roosevelt in Deadwood. Display all records. Click the Uses Column AutoFilter arrow. Select All. Another example -- to display only the very short and very long Black Hills trails do the following. Click Custom in the Length AutoFilter drop box. Specify to include the trails where the length is less than 1 OR where the length is greater than 100 as shown below.
Click Ok. The 2 trails over a hundred miles long and the numerous short trails less than a mile long are displayed. Forgetting to select Or instead of And will result in zero records, as there are no trails here that are both less than 1 mile and more than 100 miles long. The operator And requires that both conditions exist for the record to be included while the Or operator displays records that meet one of either or both conditions. Redisplay the whole database. Data>Filter>Show All One more Example, this time to show how Wildcard Characters are used. Perhaps you'd found this really great trail but couldn't remember the name of it but you're sure it started with either i or a. Click Custom in the TrailName AutoFilter drop box.
Reminder: the asterisk replaces any number of characters in the same position and the question mark replaces only one. Click Ok. Four possible answers are displayed. Redisplay the whole database. Data>Filter>Show All |
|
|||
|
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. |
|||||
Lets
say we're interested in the trails where horseback riding is allowed but
cross country skiing is not. Define the filter so that your entries
are similar to that displayed here. Note that "horse" and
"cross country" are not listed in the drop boxes as separate
options because Excel did not find either as a unique entry in the column
of data, therefore the entry will have to be typed in.
