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.
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!
If you are interested in bringing Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact firstname.lastname@example.org.
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).