Advanced Filtering in Excel

You learned about normal filtering in Excel. This can’t handle all filtering features, however. For example, you can’t filter a range based on the relationship between columns, like finding all items where Sales times Units is over 30,000,000:

In the above figure, only rows 2 and 7 meet that criterion.

To do that, you could add another column which multiplies the sales and units together, then filter on that column, but you could also take advantage of the Advanced Filter, found on the Data tab, Sort & Filter group:

This brings up this dialog:

The options are:

• Filter the list, in place: The list will remain where it is, with filtered rows hidden
• Copy to another location: The list will not be filtered – a filtered version will appear elsewhere (where you specify, in the “Copy to:” section in the dialog)
• List range: The address of the list being filtered. If you have one cell in the list selected, this will be automatically filled with the address of the current region
• Criteria range: The address of the range where the criteria is specified. This will be discussed further, below.
• Copy to: The address of the range where the result of the filter will show
• Unique records only: The filtered list will contain no duplicate values.

The criteria range is at least 2 rows and one column, and can be more. The top cell of this range is the column you’re referencing. For example, this criteria range:

could be used to filter the Salesperson column by the name Buchanan. (Yes, this could also simply be done with a regular filter). This criteria range:

 (2 rows and 2 columns) could be used to filter the range by both the salesperson and region.

Let’s look at this example:

After pressing OK:

As you can see, only records for both Buchanan and South are shown. All others are filtered out – note the blue row numbers, and also note there are no dropdown arrows in row 1, as are usually present in the usual filtering.

Since there were 2 columns, this is considered an “and” condition. If there are more than 2 rows, it’s considered an “or” condition. For example, this criteria:

will only show regions of north or west. (In English, one could say North and West, but for Excel, there’s no way a region could be both!)

This shows:

You can use more than 1 column and more than 2 rows in the same filter. In this example:

this would not be good for Salesperson of Buchanan and region of South or North. What this says is Salesperson of Buchanan AND region is South, OR any salesperson with North region! To do the former, you need:

Another type of criteria is called “computed” criteria. This is one where the top row is either blank or does not match an item from the list’s title row. In this case, the 2^nd row is a formula which points to the first row of the data (as relative reference), and will be applied to every row. This is how you would find records where the sales times units is over 30 million:

Notice cell H2 is blank and H3 contains the formula =C2*D2>30000000. Using these 2 cells as the criteria in the advanced filter produces this:

If I use the feature to copy to another location, I only need to reference one cell – the top, left cell of the result:

Here, we’ve combined a computer criteria with regular – cell G2 has the formula =YEAR(B2)=2022. This will give all records for 2022 with a region of North and a salesperson of Davolio. The result will go to cell O1:

The last item to discuss is the checkbox Unique records only.

Look at this dialog:

Notice that the List range was modified to look at column F only, and the result should go to cell G9. Since the Unique records only is checked, the result is this, in G9:G13:

From here, you might use the SUMIF function in cells H9:H13 to summarize sales by region:

For completion, here’s the FILTER function article, repeated from the article on dynamic arrays:
The FILTER function enables you to have a dynamic filter, instead of using the filter from the ribbon. The syntax is =filter(array,include,if_empty). That is, you specify what the range is you’re filtering, the rule determining what to show, and what to display if nothing is found.

Here’s a small example:

The range being filtered is A1:D12. The “include” argument is A1:A12=F1 which winds up being an array of TRUEs and FALSEs. Wherever this is a TRUE, it shows in the filter. Note that neither range needs to be an absolute reference. Simply by changing the value in F1, we get a new list:

And if we put in an invalid name:

If we left off the “No Match”, we’d see this:

You can simulate multiple filter criteria (like AND and OR) by using * for AND and + for OR as seen in these examples:

First, OR:

The formula says filter the array based on column A being “Daily” and column C being “Central”. If either is true, it shows in the filter.

Carefully note the use of the parentheses. (A1:A12=F1) and (C1:C12=F2) each produce an array of TRUE/FALSE, When added together, it becomes an array of 1’s and 0’s. The 1’s are what’s shown in the filter. By simply changing the + to *, we have an AND condition: