How to Properly Filter Zeros & Numbers - The Excel Filter Menus
30

How to Properly Filter Zeros and Numbers with the Filter Drop-down Menus

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 30 comments
ug ug - August 30, 2017

filter: > -0,999999999 and < 0,000000001

Reply
Carlos - May 19, 2017

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

Reply
Wanda - September 11, 2016

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?

Reply
    Jon Acampora - September 12, 2016

    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!

    Reply
Timothy Skalaban - June 13, 2016

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.

Reply
Doug Glancy - May 9, 2016

This is a wonderful post. I had never put together that the filter is additive. I mean I had, but it never really sank in until I read this. Nonetheless I did manage a rather in-depth post describing the many VBA Autofilter parameter settings which you and your readers might enjoy: http://yoursumbuddy.com/autofilter-vba-operator-parameters/

Reply
    Jon Acampora - May 11, 2016

    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!

    Reply
Rosa B. - April 29, 2016

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

Reply
    Jon Acampora - May 2, 2016

    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!

    Reply
Ryan Wilson - April 22, 2016

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!

Reply
    Jon Acampora - April 26, 2016

    Thanks Ryan! I agree that clearing all filters is probably the safest way to go when multiple columns are filtered. Have a good one!

    Reply
Michelle Watkins - April 16, 2016

Brilliant, thankyou. I have had exactly this problem this week.

Reply
Mike Benstead - April 14, 2016

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

Reply
    Jon Acampora - April 14, 2016

    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!

    Reply
Hi Jon - April 14, 2016

This will help me great as need to filter zeros in AR calculation.Thanks and i will share with my team

Reply
Adel - April 14, 2016

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

Reply
    Jon Acampora - April 14, 2016

    Hi Adel,

    I’m sorry, which part were you trying to produce? I don’t believe you need comma separated values to apply the filter criteria.

    Thanks!
    Jon

    Reply
Craig Riley - April 14, 2016

Hi Jon,

Great tip, will definitely pass this on.

Reply
Khaled Naser - April 13, 2016

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

Reply
    Jon Acampora - April 14, 2016

    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!

    Reply
Waledd - April 13, 2016

Thanks jon

Reply
Kevin Lehrbass - April 13, 2016

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

Reply
Milt - April 13, 2016

I was doing it the wrong way but not now. Thanks Will past this on to my friends.

Reply
    Jon Acampora - April 13, 2016

    Thanks Milt! It’s a hard habit to break, and I must admit that still use the checkboxes sometimes. Thanks for sharing! Have a good one! 🙂

    Reply
Jon Acampora - April 13, 2016

What other tips do you have for filtering numbers? Please leave a comment below with any questions or suggestions. Thanks!

Reply

Leave a Reply: