Excel 2019/365: File Management: Advanced Filtering

Updated 2019-08-05

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—F43).

·        Enter Data in the "Name Box" (above cell A1). Note that there is nothing special about this name, it can be anything that is made up of letters and has no blanks in it. For example, we could have called it Students instead.

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. We will insert our criteria range above our data range. To create a criteria range:

·        Drag the mouse through rows 1-10 (the row numbers on the left margin—the green area below).

·        Right-click and click on Insert.

·        We now have 10 blank rows for our criteria range. Select the column headings (row 11).

·        Copy them to the clipboard (Ctrl+C).

·        Move to A1.

·        Paste the column headings.

·        The cells below each heading will be used to enter selection rules. These are also part of the criteria range.

Setting up the criteria

·        We are selecting the seniors. A senior is a student with 90+ credits, so in the cell immediately below "Credits", put the expression >=90.

·        We will name this range as well. Drag the mouse from A1 through F2. In the Name box, type Criteria. Note that there is nothing special about this name, it can be anything that is made up of letters and has no blanks in it. For example, we could have called it Rules instead.

·        Before proceeding, put the cursor anywhere in your data range (below A10).

·        Click on the Data tab.

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

·        The Advanced Filter dialog box will appear.

·        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.

·        If you put the cursor in the data before selecting the Advanced Filter, Excel will automatically select the List Range for you (on the left below). If you did not put the cursor there, you will have to enter the list range. In this case, we have called the range Data (on the right below).

 OR THIS:

·        For the criteria range, enter Criteria. Again, there is nothing special about the name "Criteria" (on the left below). You could choose any name you like as long as there are no blanks or special characters in it. You could also just use normal range notation (on the right below).

OR THIS:

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

Example: Select all Senior BUAD majors

·        Let's try another advanced filter.

·        Click on the Data tab.

·        In the Sort and Filter group, click on Clear. Now we are back to our unfiltered list.

·        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 first row of the criteria, 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 two 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 G1 to "Credits".

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

·        In E3, enter >=30

·        In G3, enter <60

·        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. Click anywhere in your data range. Then, on the Data tab, click on Advanced Filter.

·        Excel should automatically select the list range for you: $A$11:$F$54.

·        For the criteria range, enter E1:G3.

·        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. Both of these must be true, so they must go on the same line.

A sophomore ACCT major is a student who:

·        Has "ACCT" in the Major field

·        Has a value >=30 and <60 in the Credits field. Both of these must be true so they must go on the same line.

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.

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 C1:G3.

·        Click on "OK". The records of all students who meet all of the rules on row 2 (sophomore AND BUAD majors) OR who meet all of the rules on row 3 (senior AND 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 C1:G5.

·        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.

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":