I didn’t think of blogging about this topic before. I thought that it is so straight-forward. Nevertheless, there is another way of doing so which is even more straight-forward BUT may give you unexpected results later on. And to my surprise, that is the way most people (I encountered) do. Maybe it’s worth blogging about it.😁

You may download a sample file to follow along.

Situation

It is not uncommon to filter out all values that are not equal to zero. Let’s say we want to filter all records with Unit Sold not equal to zero, it is very common for us to simply uncheck 0 from the list of values. I did that (long before) too. 😅

By unchecking “0” from the list, we thought we have told Excel to keep all values but 0. However, Excel interprets it differently. Let’s see by hovering on the filter icon for unit sold:

What??? 🤔🤔

Apparently, Excel takes it as Text filter and it keeps all current values but 0. To be more precise, It keeps the records that is equal to 100 or 19 or 24 or 31 or 42…… etc.

The Problem

What’s the problem? We still have a list of values that we want. You are right. It does not really matter if we want to just have a quick look at the data. Nevertheless, if we revise the data, and then Reapply the filter set, the problem arises.

Let’s say, we have revised two values from 24 to 0 in D4, and from 72 to 70 in D6, as shown below:

Then we Reapply the filter as we want to focus on values that are not equal to 0. We expect that row 4 will be filtered out while row 6 will stay, right? However it is not the case!

Ooops… Row 6 is filtered out after we Reapply the filter.

Why?

It is because the filter that was set up is to keep the records that is equal to 100 or 19 or 24 or 31 or 42…… etc., which a value of 70 was not included.

Got it. Now we know the problem, so what’s the solution?

Using Number Filter

Instead of unchecking 0 from the filter menu, we should apply Number Filters.

(Note: when the column of data is numeric, we will see “Number Filters”; when the column of data is text, we will see “Text Filters”)

There are many options for Number Filters, which I believe are self-explanatory. In our example, we want to focus on values that are not equal to zero, so we pick “Does Not Equal…” and input the following on the Custom AutoFilter dialog box:

Let’s check the filter criteria again:

It looks good. You know what I am going to do? Test the result:

Bingo! Reapply filter returns expected result.

As simple as this. 😉