Advanced Filtering in Excel

By Bob Umlas, Excel MVP

In this blog I’ll continue the discussion from my last blog on basic filtering. This one is on advanced filtering.

Let’s continue with the same worksheet used from the last blog:

You can use advanced filtering for any of the features you use basic filtering. The advanced feature enables you to do things that can’t be done with basic filtering. For example, how would you filter this data for items in which the Sales times the Units is over 30,000,000? You can’t, unless you add another column containing this calculation and then filter on that new column. But as you’ll see, you don’t need to do that with the advanced filter. Let’s take a look at what the advanced filter looks like.

It’s on the Data tab:

When you select this, you see a dialog box which you fill in:

If the active cell is inside a range of data, then the first field is pre-filled with the address of the contiguous range of cells. You can change this to be any range, but you usually leave it as is. The worksheet I used for this blog has data in A1:F59. The next, and perhaps most important part of the advanced filter, is the Criteria range. This is a range of cells containing at least 2 rows and at least 1 column. Usually, the top row is one of the column header cells, like Product, or Region in the above illustration. For example:

Cell H5 contains the column header from cell F1, “Region”. The criteria range in the dialog was created by clicking in the field, and then dragging across the 2 cells in column H. (“Filtering” is the name of the sheet tab, and preceded the address if you drag across the cells). You could have simply typed H5:H6 in the Criteria Range box.

But what does this mean? The top row identifies the column, and the 2nd row identifies what to filter this column by. You would definitely not use the advanced filter for this simple filter; I’m just demonstrating how this works. The result, when clicking OK is as expected, rows containing only North Region:

Let’s examine a more involved filter.

This time, the criteria range is 3 rows, not 2. This is how you indicate an or condition. The region is North or West. The result:

When there’s more than 1 column, it becomes an and condition:

This means you want to see all rows which have a Region of North and a Salesperson of Davolio:

You’re not limited to 2 columns. This filter adds 3 fields together:

That means that the Region must be North and the Salesperson must be Davolio and the Product must be Produce. You would enter this information manually before using the Advanced Filter.

The above yields this:

You can also have more than 2 rows and more than one column! This filter will show those rows where the Salesperson is Davolio and the Product is Produce, or the Region is South and the Product is Meat:

Let’s get back to the filter which can show those rows where sales times units is over 30 million. There’s no column you can use, so you resort to what’s called a “computed criteria”. It’s ok to have some kind of labeling in the top row of this column (or it may be left blank) – just don’t use a label that matches something in the headers being filtered, or this will not work as it is intended.

It might look like this:

The top row is clearly blank (H2), so this is a computed criteria. But what’s that “TRUE” in H3? As in all computed criteria, it’s a formula which specifies the criteria, and refers to the first data row (row 2 in this example) as an example to apply to every row. Cell H3 contains the formula =C2*D2>30000000:

It’s the test we want to apply to every row: Sales times Units (columns C and D) must be more than 30,000,000. Here’s the result:

(It’s accurate. I tested it!)

You can combine computed criteria with non-computed criteria as well! In this example, using just B for the salesperson will treat it as a wild card, indicating any Salesperson beginning with B:

Cell H3 contains the formula =YEAR(B2)=2017. This criteria is getting all rows in which the year is 2017 (even though the dates contain month and year), and the Region is North and the Salesperson begins with B:

If I wanted to see some field which is exactly equal to just B, and not just beginning with B, you have to use a very non-intuitive formula: =”=B”.
If you want something to be not equal to something, you could use this as the criteria:

You may also have noticed the choice in the filter dialog to Copy to another location.

This advanced criteria will copy all records in which the region contains an s (all except North) to cell I1 as the starting cell:

Notice that the “North” records are filtered out (See column N).

Here’s a trick that not many people know about. If you have cells already filled in with the data headers you want, as in J1:K1 below, you can limit or reorder the extract columns. This is easier shown than explained. Suppose you have your worksheet set up like this:

Note the computed criteria of C2<3000, meaning the filter will be for Sales under 3000:

The “trick” part is that the copy to location is J1:L1, and those cells already contain information. See what happens:

Only those columns which were referenced are extracted from the source data!

What if I wanted to copy to a different sheet, like Sheet3, below?

This gives an error message:

Hmmm. What if we start from that sheet?

Notice I’m starting the advanced filter from a totally empty sheet! I can fill in the fields:

(The criteria range is pointing to Region of North or West):

These records were “pulled” from the Filtering sheet. Cool! And if the empty sheet had Units in A1 and Product in B1:

That extract which points to these cells:

gives:

Okay, nearing the end. What is Unique records only?

Using this advanced filter:

(Notice the list range was changed to be column F only), and cell H5 is empty. When I click OK:

I have a list of unique items from the Region field.

Happy Advanced filtering!

If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.


About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).

Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box


Basic Filtering in Excel

By Bob Umlas, Excel MVP 

In this blog I’ll discuss filtering. In another continued blog, I’ll talk about advanced filtering. First, what is filtering?

We’ll define it by looking at a very simple illustration. Suppose this is your worksheet:

If you were only interested in the West region, you can show just the West items by filtering. There are many ways to filter and we’ll show them all in this post, but for now, if you right-mouse click on the word West in cell A3 (or A6), you can filter this way:

And that will show:

The rows were filtered to only show what you requested (aside from the header row). Notice that rows 2, 4, 5, and 7 are not shown, and the row numbers have turned blue. They’re not deleted, they’re filtered. Also, at the bottom of the window is a small statistic on the results of what happened:

In this case, it showed 2 records out of the 6 available. Also notice the filter icons in row 1. The one in A1, , is an indicator that this column has a filter applied to it. The one in B1, , indicates this column has no filter applied, but it is available. These symbols came into view when you requested to filter by the cell’s value.

If you were to click on the icon in A1, you’d see this (I excluded the parts which apply to sorting):

Here, you can clear the filter, or you can check any of the checkboxes to show or hide (filter, actually) whichever items you want. We’ll discuss the disabled Filter by Color and the Text Filters and the Search box later.

Let’s take a look at a more involved worksheet:

You can put the filter icons on in several ways. Select one cell in the range and…:

  • Ctrl/Shift/L (a toggle)
  • Click the Filter icon in the Sort & Filter group of the Data tab (notice the tool tip says Ctrl+Shift+L):

  • Press Alt/A/T
  • Ctrl/T or Ctrl/L (aside from putting on the filter icons, it turns it into a table – a future blog post).

Once you have the filter icons present, you get different results depending on the data. If I put on filters and click the one in column A (Product), I see:

But if I click on one with dates (Column B):

And each of these can be expanded with the + signs – here, I opened up 2016 and then March:

Notice it shows only the available dates from the actual data in column B.

If I select column C, Sales, I see:

It shows all the various unique numbers, in sequence!

It’s time to look at some other features contained in these dropdowns. Let’s go back to the Product dropdown and look at that item called “Text Filters”:

You can filter by many choices. Suppose I choose “Contains…” and then decide to filter on any produce containing the letter “e” (each choice gives a different next option):

When I click OK:

Notice “Dairy” is not shown because there’s no “e” in Dairy! Clearly, I can’t show all possible combinations, and you’ll have to experiment, but this will give you a good idea of what’s possible.

When I look at column B and expand on Date filters, I get this powerful set of options. Shown also is choosing “All dates in Period…” which has a 2nd flyout:

When I look at column C and expand on Number Filters, I see this:

As you can see, there are many choices. For example, if I choose Greater Than, I get another dialog (any command which ends in “…” means there will be another choice):

Here I chose Sales greater than 7000 and saw that I also had another choice, so I selected the “And” option button, then “is less than”, then I entered 9000.

The result:

One last topic for this filtering blog. You may have noticed the Search field. Using a different set of data, here’s a sample of what I see when I click on the filter dropdown:

If I want to filter this long list to anything to do with maintenance, I could type “Maint” into the search box, and after a short pause (this file had 68000 rows!):

Notice it picked out all descriptions which had Maint (had I typed Maintenance, I would have missed the “Maint & Repair”):

Notice above that there’s a checkbox for “Add current selection to filter”. If I wanted to include all rows which have “rep” in the description without un-doing the current filter, I check this box:

Last item I’m covering in this blog is filtering by color. For this example, I colored several cells’ font, several cell’s fill color, and added conditional formatting, so when I choose Filter by Color, I see this flyout:

All the different combinations are listed. If you were to right-mouse click on a cell, your choices are given by this left- flyout when you choose Filter:

Notice all the filtering is done using the selected cell’s property, whereas in the previous filter by color (a misnomer, actually, because you can also filter here by icon), you can see all the choices available.

Next month, we’ll show what can be done with advanced filtering. For example, in the “more involved” worksheet shown earlier, there’s no way to filter the rows to only show those where the sales times the units is over 30,000,000! Stay tuned!

Happy filtering!

If you are interested in bringing Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.


About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).

Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box