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

Advanced Filters

Advanced filters enable you to specify even more criteria than you can with AutoFilters. First set up a criteria range, then use the Advanced Filter dialog box to perform the search.

To use advanced filters

Create a criteria range by copying the data labels in the list to a blank area of the worksheet and then entering the criteria in the cells beneath it (Figure 38).

Figure 38. Create a criteria range with field names and values that you want to match.

Choose Data > Filter > Advanced Filter (Figure 30).

In the Advanced Filter dialog box (Figure 39), select a radio button to specify whether the matches should replace the original list (Filter the list, in-place) or be created elsewhere (Copy to another location).

Figure 39. The Advanced Filter dialog box, all set up to filter a list.

In the List range edit box, confirm that the correct cell references for your list have been entered.

In the Criteria range edit box, enter the cell references for the range containing your criteria (including the field labels).

If you selected the Copy radio button in step 3 above, enter a cell reference for the first cell of the new list in the Copy to edit box.

To omit duplicate records from the results list, turn on the Unique records only check box.

Click OK. Excel searches for records that match the criteria and either replaces the original list or creates a new list with the matches (Figure 40).

Figure 40. The criteria in Figure 38 yielded these records.



Not a subscriber?

Start A Free Trial

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