• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 15. Using Advanced Excel Databas... > Advanced Filtering Using a Criteria ...

Advanced Filtering Using a Criteria Range

You can get about as precise as you want when filtering information out of your database when you use a criteria range and indicate the exact criteria for which you are searching. This technique takes a small amount of set up before you can begin to use it.

  1. Display your database on-screen and turn off the AutoFilter feature if it is turned on.

  2. Insert at least four blank rows at the top of your database. This area is your criteria range.

    Tip

    You can quickly insert blank rows by selecting the rows you want to be blank and then pressing Ctrl++ (plus sign).


    Note

    The most convenient place for a criteria range is often the first few rows of your spreadsheet. The criteria range must be on the same sheet as your database, but can actually appear anywhere on the sheet. It is not recommended that you place your criteria range beneath your data because there is no room for the database to grow if the criteria range blocks such growth.


  3. Copy the header row of your database to the first blank row (see Figure 15.7).

    Figure 15.7. The top row of your criteria range will contain the same headers as your database.

    Tip

    Use the Copy and Paste features to copy your header row to the first row of your criteria range, rather than retyping the headers. This way there is no chance of making a typographical error. The header row of the criteria range must exactly match the header row of your database


  4. In the first blank row of the criteria range (row 2 on your worksheet), enter criteria you want to match in your database search. For example, if you search for items from a particular location, enter the name of the location in the first blank row of the location field (see Figure 15.8).

  5. (Optional) If you want to search for matches in other fields at the same time, enter that information in the first criteria row as well. By entering all search criteria in one row, you are requesting that the search results meet all of the criteria. You can use greater than (>) and less than (<) operatives in your search to indicate that you will accept any records that include data greater than or less than the specified amount (see Figure 15.9).

    Figure 15.8. Use the first blank row of the criteria range to display an example of the information you want to match in your database.

    Figure 15.9. Using the fields in the criteria range, enter any additional information for which you want to find records that match.

  6. (Optional) To establish an either/or criteria search, enter one set of criteria in the first row of the criteria range, and enter a second set of criteria in the second row (see Figure 15.10). Results of the search will display records that meet all of the criteria in the first row or all of the criteria in the second row. You can enter as many rows of criteria as you like as long as no blank rows separate the criteria. Each row of criteria presents an "or" situation for your search.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint