Excel 2016: Advanced Filtering

Updated 2017.07.30

Objectives:

·        Learn how to use advanced filters.

Sample data file: Advising.txt. Note that this is a text file and must be "imported" using the Text Import Wizard.

Advanced Filters

In addition to the "AutoFilter" command, there is an "Advanced Filter" command that allows more options than the "AutoFilter" command does. The "AutoFilter" command limits you to two conditions connected by the word "and" or the word "or". The "Advanced Filter" has no such restriction. You can use the "Advanced Filter" command to apply multiple criteria to a single column, apply multiple criteria to multiple columns, or create criteria that result from a formula. When you use the "Advanced Filter" no drop-down arrows appear at the top of a column. Instead, you must create a copy of the column headings in a separate area of the worksheet and specify your criteria below each heading.

Example: Select all Seniors

Assume that you want to select all of the seniors. Seniors are students with 90 or more credits. To create an advanced filter, we need to set up two areas on our worksheet:

·        The list range (the table to be filtered)

·        The criteria range (where you put your selection rules)

Setting up the list range

The list range—our data—already exists. However, our rules will be easier to create if we name the list range:

·        Select the data, including the headings (from A1 over to the bottom of column F).

·        Enter Data in the "Name Box" (above cell A1).

008-B-Lists-AdvancedFiltering_files/image001.jpg

Setting up the criteria range

Now we need to set up the criteria range. The criteria range is an area on your worksheet where you put the rules that Excel is to use to select records from the data range. To create the criteria range, you must first make a copy of the column headings. Below each column heading, you can create a filter for the data in that particular column. To create a criteria range:

·        Select the column headings.

·        Copy them to the clipboard.

·        Move to an empty row (I used cell H1—immediately to the right of the data. The area immediately below the data also works well.)

·        Paste the column headings.

·        The cells below each heading will be used to enter selection rules. These are also part of the criteria range. Select the heading cells that you have copied to H1, as well as the row immediately below the headings (the range H1:M2).

·        Enter Criteria in the "Name Box" (above cell A1).

Setting up the criteria

·        In the cell immediately below "Credits", put the expression >=90.

008-B-Lists-AdvancedFiltering_files/image003.jpg

·        Click on the Data tab.

·        In the Sort and Filter group, click on the Advanced button.

008-B-Lists-AdvancedFiltering_files/image004.jpg

·        Select the "Filter the list in place" option button. This will cause Excel to temporarily hide the records that are not selected when the advanced filter is applied.

·        For the list range, enter the data you want to filter.

·        For the criteria range, enter Criteria. Again, there is nothing special about the name "Criteria".  You could choose any name you like as long as there are no blanks or special characters in it. For example, you could call the criteria range Rules.

·        Click on OK. The records of all students who have 90 or more credits will be selected:

008-B-Lists-AdvancedFiltering_files/image005.jpg

Example: Select all Senior BUAD majors

·        Notice that our criteria range disappeared. It's still there, but rows 2 and 3 are now hidden. Let's get it back:

·        Click on the Data tab.

·        In the Sort and Filter group, click on Clear.

·        Now let's select all of the seniors who are also BUAD majors. Leave the rule for selecting seniors. Add the text BUAD to the cell below "Major" in the criteria range.

·        Click on the Data tab.

·        In the Sort and Filter group, click on the Advanced button.

·        Select the "Filter the list in place" option button.

·        For the list range, enter Data.

·        For the criteria range, enter Criteria.

·        Click on OK. The records of all students who are BUAD majors with 90 or more hours are selected.

 

Whenever you put multiple criteria on the same row of the criteria range, you are doing an implied and condition ("and" means "both"). That is, a record must match all of the criteria rules on that row in order to be selected. Since our rules are on the same row, only those records that match both criteria rules (credits >= 90 and Major = "BUAD") will be selected.

An or condition ("or" means "either") can be implemented using the Advanced Filter by putting each criterion on a separate row. Any record that matches all of the criteria on a single row will be selected. If a record doesn't match the criteria on the first row, but matches the criteria on the second row, the record will be selected.

Example: Select all seniors and sophomores

Our next example is something that cannot be done with the Autofilter feature of Excel. We want to select the sophomores and the seniors. Before we begin, however, let's clear the previous filter results and criteria:

·        Click on the Data tab.

·        In the Sort and Filter group, click on the Clear button to clear the existing filter.

We have 3 conditions, two of which are combined:

·        Rule for selecting seniors: credits >= 90

·        Rule for selecting sophomores: credits >= 30 and credits < 60

·        The rules for sophomores need to go on the same line, which means that we will need two columns labeled "Credits". Change the heading in cell N1 to "Credits".

·        Leave L2 with the rule for seniors: >=90

·        In L3, enter >=30

·        In N3, enter <60

008-B-Lists-AdvancedFiltering_files/image006.jpg

·        Because we are entering part of our criteria in row 3, we cannot use the "Criteria" named range (Criteria only went as far as row 2). We have two choices: we can delete the existing name and re-define it, or we can just enter the range in the dialog box. We will do the latter.

·        For the list range, enter Data.

·        For the criteria range, enter H1:N3.

·        Click on "OK". The records of all students who meet both of the criteria in row #1 (seniors) are selected. The records of all of the students who meet the criterion in row #2 (sophomores) are also selected.

 

Warning: You must make sure that you spell the column names exactly the same in the criteria range as you do in the list range. The smart way to get your column names for the criteria range is to copy them from the top of the list range.

Example: Select all senior BUAD majors, and all sophomore ACCT majors

Here is another rule that cannot be applied using the Autofilter feature of Excel. Select all senior BUAD majors and all sophomore ACCT majors.

A senior BUAD major is a student who:

·        Has "BUAD" in the Major field

·        Has a value >=90 in the Credits field.

A sophomore ACCT major is a student who:

·        Has "ACCT" in the Major field

·        Has a value >=30 and <60 in the Credits field

The rules for selecting a senior BUAD major will go on one row.

The rules for selecting a sophomore ACCT major will go on a separate row.

Select the criteria range:

Enter the rules in the criteria range:

·        Enter BUAD in row 2 of the Major column.

·        Enter >=90 in row 2 of the Credits column.

·        Enter ACCT in row 3 of the Major column.

·        Enter >=30 in row 3 of the first Credits column and enter <60 in row 3 of the second Credits column.

008-B-Lists-AdvancedFiltering_files/image007.jpg

Apply the filter:

·        Click on the Data tab.

·        In the Sort and Filter group, click on the Advanced button.

·        Select the "Filter the list in place" option button.

·        For the list range, enter Data.

·        For the criteria range, enter H1:N3.

·        Click on "OK". The records of all students who meet all of the rules on row 2 (sophomore BUAD majors) OR who meet all of the rules on row 3 (senior ACCT majors) will be displayed.

Example: Select all sophomore and senior BUAD and ACCT majors

This is actually four rules:

1.   Sophomore AND BUAD

OR

2.   Senior AND BUAD

OR

3.   Sophomore AND ACCT

OR

4.   Senior AND ACCT

We want to select a student if any one of the four rules is true. This means the rule will take four lines in our criteria range.

Enter the criteria:

Apply the filter:

·        Click on the Data tab.

·        In the Sort and Filter group, click on the Advanced button.

·        Select the "Filter the list in place" option button.

·        For the list range, enter Data.

·        For the criteria range, enter H1:N5.

·        Click on "OK". The records of all students who meet all of the rules on row 2 (senior BUAD majors) OR who meet all of the rules on row 3 (sophomore BUAD majors) OR who meet all of the rules on row 4 (senior ACCT majors) OR who meet all of the rules on row 5 (sophomore ACCT majors) will be displayed.

Example: Testing for equality in a text field

When you enter a text value in a criterion field, Excel selects all text that begins with the given text. This can cause problems. Consider a movies spreadsheet.

SNAGHTMLdd45336

And assume that you want a list of all of the PG movies. If you create a criteria range like this:

SNAGHTMLdd615d9

Excel will return every movie whose rating begins with "PG", which will also include "PG-13".

If you only want the PG movies and not the PG-13 movies, change your rule to ="=PG":