Bottom line: Learn the correct way to filter out zeros and numbers with the filter drop-down menus in Excel, and avoid embarrassing mistakes.
Skill level: Intermediate
The image above shows a way to filter out numbers or zeros using the check boxes in the filter drop-down list. This way of applying number filters has gotten me in trouble. I have seen others make this common mistake too, and don't want it to happen to you. 🙂
Download Example File
Download the example file to follow along.
The Easy, But INCORRECT Way to Filter Out Zeros
Often times we are working with a list or data set that contains a lot of zeros in a column. We might want to filter these zeros out to shorten the list, so that we only see numbers that are greater than or less than zero.
One easy way to do this is to uncheck the zero (0) item in the filter drop-down box.
This does work, and will filter out (hide) the rows that contain zeros in the cells for the column. There won't be any issues if you only have that one column filtered.
However, problems arise when you have filters applied to more than one column.
For example, let's say that we:
- First apply a filter to column B for the East Region.
- Then apply a filter to column D to exclude zeros by unchecking the zero item check box.
Everything is fine at this point.
We are seeing rows for the East Region where the Unit Price is not zero. This is our filter context.
Now, I want to clear the Region filter to view all Regions.
I assume that I should see rows for all Regions where the Unit Price is not zero.
If we look at the filter drop-down for the Unit Price we can see that there are some amounts that are unchecked. That means that these rows are hidden, even though the amount is not zero.
What's going on???
The Filter Drop-down List Checkboxes Explained
The filter list in the filter drop-down menu displays a list of unique items for that column (field). We can check or uncheck items in the list to apply filters and hide rows in the range/table.
When we uncheck one or more items, the filter creates a filter criteria to apply to the list. This filter criteria is a list of all the checked items. This is important.
The filter criteria does NOT consider the unchecked items. Even if you have one item unchecked, and 1,000 items checked, the filter criteria will be for the 1,000 checked items.
In the example above, when I unchecked the zero in column D, the filter criteria became.
I used a little VBA code to get the filter criteria.
Even though I meant to apply a filter to exclude zero, I actually applied a filter to include all the other numbers besides zero.
Since I had already applied a filter for the East Region, the Unit Price item list only contained numbers for the visible rows.
When we clear the filter in column B to show all Regions, the filter criteria for column D does NOT change. This means that there is still a filter applied for the numbers that are not zero for the East Region only.
Some of these numbers might also exist in rows for other regions, but overall it just becomes a meaningless mess. 🙂 This can lead to a lot of confusion when you are trying to tie out numbers or validate a summary report calculation.
So let's take a look at a safer approach.
The Correct Way to Filter Numbers
We saw how using the filter list checkboxes can get us in trouble when filtering numbers.
The safest way to filter numbers is by using the Number Filters menu on the filter list drop-down.
Selecting the Number Filters option will display a sub-menu with different number filter options (Equals, Does Not Equal, Greater Than, Less Than, Top 10, etc.)
Most of these commands open the Custom AutoFilter menu. This menu allows you to specify two criteria with an AND or OR condition.
It is easy to create a filter to exclude zeros. We will set the filter criteria to “does not equal”, put a zero in the combobox to the right of the criteria, and press OK.
This sets a number filter with a criteria of “does not equal 0”:
The less than and greater than symbols together “<>” is the comparison operator for Not Equal, or the opposite of “=”.
This filter criteria helps us avoid the problem we experienced with the filter checkbox list.
Now when we clear or change the Region filter in column B, the number filter in column D will be re-applied to exclude all cells that contain a zero.
This is what we want. We don't have to go back to column D and re-select the checkboxes. The number filter will automatically be applied.
Keyboard Shortcuts for the Number Filters
The number filters take a few extra steps to apply, but it is well worth the time and effort if you are filtering multiple columns.
We can also use keyboard shortcuts to quickly apply a filter to exclude zeros.
With the header cell selected (the cell that contains the drop-down icon), press:
Alt+Down Arrow, F, N, 0, Enter
That is the keyboard shortcut to apply the number filter does not equal 0. There are many variations to this keyboard shortcut combination. Each of the underlined letters in the filter drop-down menu is the shortcut key for that menu item.
Checkout my article on 7 keyboard shortcuts for the filter drop-down menus that will save you a lot of time when working with filters. I also explain my favorite shortcut key to put the cursor in the Search box.
As we've seen, the filter drop-down item list can get us in trouble. I hope this article helps you avoid some of the mistakes I have made, along with the embarrassment… 🙂
In another article I will follow up with the VBA code and macro to get the filter criteria for the filter range or Table.
Please leave a comment below with any questions. Thanks!