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

Chapter 13. Analyzing Data with Lists > Filtering a List with a Criteria Range

Filtering a List with a Criteria Range

After you've set up your criteria range, you can use it to filter the list. The following procedure takes you through the basic steps:

1.
Copy the list field names that you want to use for the criteria, and paste them into the first row of the criteria range. If you'll be using different fields for different criteria, consider copying all your field names into the first row of the criteria range.

TIP

The only problem with copying the field names to the criteria range is that if you change a field name, you must change it in two places (that is, in the list and in the criteria). So, instead of just copying the names, you can make the field names in the criteria range dynamic by using a formula to set each criteria field name equal to its corresponding list field name. For example, you could enter =B4 in cell B1 of Figure 13.13.

2.
Below each field name in the criteria range, enter the criteria you want to use.

3.
Select a cell in the list, and then choose Data, Filter, Advanced Filter. Excel displays the Advanced Filter dialog box, shown in Figure 13.14.

Figure 13.14. Use the Advanced Filter dialog box to select your list and criteria ranges.


4.
The List Range text box should contain the list range (if you selected a cell in the list beforehand). If it doesn't, activate the text box and select the list (including the field names).

5.
In the Criteria Range text box, select the criteria range (again, including the field names you copied).

6.
To avoid including duplicate records in the filter, activate the Unique Records Only check box.

7.
Click OK. Excel filters the list to show only those records that match your criteria (see Figure 13.15).



Figure 13.15. Set up a separate criteria range (B1:H2, in this case) to enter complex criteria.



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


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