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

Warning - Do Not Use the Filter List Checkboxes to Filter Numbers in Excel

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.

Filter Zeros And Number With Filter Drop-down Menus (12.0 KB)

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.

Filter Out or Exclude Rows that Contain Zeros in the Column Cells

One easy way to do this is to uncheck the zero (0) item in the filter drop-down box.

Easy Way to Filter Out Zeros with Filter Item List 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:

  1. First apply a filter to column B for the East Region.
    Apply a Filter to Another Column First
  2. Then apply a filter to column D to exclude zeros by unchecking the zero item check box.
    Exclude Zeros by Unchecking Filter List Item

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.

Filters Applied to Two Columns for Region and Unit Price

Now, I want to clear the Region filter to view all Regions.

Clear the Region Filter from the Column that was Filtered First

I assume that I should see rows for all Regions where the Unit Price is not zero.

WRONG!!!

Not All Rows are Displaying After Filtered is Cleared

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.

Other Numbers Unchecked Besides Zero in Filter Drop-down Item List

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 for the Drop Down Item List is Set for All Checked Items

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.

=10.00,=2.99,=22.00,=3.50,=30.00,=38.00,=9.20

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.

Filter Drop Down Item List Only Contains Numbers for the Visible Cells

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.

Use the Number Filters Menu to Filter Numbers - The Safer Way

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.

The Custom AutoFilter Menu to Exclude or Filter Out Zeros

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”:

<>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.

Number Filters are Re-applied when Other Columns Are Filtered

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

Keyboard Shortcut to Filter Out or Exclude Zeros in Excel Filter Drop Down

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.

Conclusion

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… 🙂

Number Filters vs Item List for the Filter Drop-down Menu in Excel

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!

31 comments

Your email address will not be published. Required fields are marked *

  • Hi, I’ve searched your blog for my issue, but can’t find it. I’m using a version 2016 in Office 365. It’s a new computer. I’m trying to filter out numbers and it’s not finding the number. For example, if I type 2680 in the search, it returns nothing even though 2,680.00 is in the column. If I type 2,680 (with a comma) it will show up. I’ve never had to do that before. Is there a setting I can change so I can go back to typing 2680 and disregard commas. The same issue happens when I use the Number filtering. Thank you.

  • Hello Jon, why that filtering in Excel 0 and 0.00 are seen as different values. I tried filtering by zero 0, but number were formatted as 0.00 in a numeric field.
    Thank you.

    May-2017

  • This finally explains what has been driving me crazy when I use filters. I couldn’t figure out what I was doing wrong. I assume this logic applies with pivot table filters as well?

    • Thanks Wanda! Happy to hear it helped. Yes, the logic applies to filtering values in pivot tables as well. A lot will depend on the filters you have applied to each area in the pivot. The filtering options are different for the Filters area vs the Rows, Columns, and Values area. But this technique should help when filtering the Values area. Thanks again!

  • Thanks for the great article! I consider myself an advanced Excel user, and I never knew this. I also appreciate the keyboard shortcut so I can easily write macros with filters.

    • Thanks Doug! I know, I had to make the mistake a few times before I realized what was going on with the additive filters. Thanks for sharing the autofilter code. That is a great little solution for a common task! Have a good one!

  • The first step was filter column D ( we are already without Zero) then we filter column B, at this point is not necessary “eliminate” zeros, because the list is already with amount > 0
    When we clear the filter by Region, nothing happend. Anyway is interesting to learn another way. thank you!
    ROSA

    • Hi Rosa,
      The first step was applying a filter to a column with text (column B). If you apply that before filtering out zeros then you can get in trouble if you clear the filter on column B. This is a simple example, and can even more complex when more than two columns are filtered. I hope that helps. Thanks!

  • Nice! I was actually unaware of this; however, so rarely do I go from two filters down to one filter or three filters down to two filters, etc. Normally I just toggle the filters off and then on again going from a table filtered by multiple columns to an unfiltered table. Definitely something to keep in mind though!

  • Good point but I suspect it’s only half the story.

    Wouldn’t be true for all filters e.g. text, not just numbers.

    Think conditional “To do list”.

    • Hi Mike,
      Yes, the same theory can apply to text. It really applies to any time you are trying to “filter out” or exclude a value from the filter criteria, and the order in which you are filtering or clearing filters.

      The example I gave was very simple, to make it easy to illustrate the point. However, we probably all have filter criteria that are much more complex. Hopefully this will get everyone thinking, and I will follow up with other articles on different scenarios.

      Thanks for the comment, and have a good one!

  • Hi Jon

    Thank you for your tutorial

    I was not able to reproduce this using Excel 2013 french version

    I am wondering if this is because I am not using same separator for numbers than your English version

    Thank you

  • Thanks Jon for your excellent tutorial! I have faced this problem yesterday. I have AR Aging excel sheet to filter customers horizontally excluding zero for invoices past due more than 30,60,90 days up to 180 days. How to solve this problem.
    Best Regards,
    Khaled Naser

    • Hi Khaled,

      Great question! You might want to create a helper column that contains the max value of all the columns, then filter on that column.

      It would probably be best if I could look at an example file, to make sure I fully understand your question. If you want to email me your file I would be happy to take a look. jon@excelcampus.com

      Thanks!

  • Thanks Jon for the excellent explanation! This is very important to know! I’ll definitely share this!
    Cheers,
    Kevin

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly