Excel Advanced Filter – Criteria to Show Blank Cells

Today I had a problem in Excel that crops up quite often, namely how to filter to find blank cells or how to filter to exclude blank cells.

Your purpose in doing this may be to find rows that you wish to delete for example.

This filter type is not very well documentated in the Excel help system as far as I can see.

I have recorded a video below which covers most of the information in this article, use either or both and don’t forget to subscribe to our email list to get updates.

However stick with me here and I will show you the solution along with some possible reasons why it may APPEAR not work and a work-around for that situation.

Excel Advanced Filter To Show Empty Cells Example

Excel Advanced Filter On Ribbon Menu

To quickly create your Advanced Filter (Excel 2007 and newer). Click on the Data Tab of your ribbon menu then click advanced within the "Sort & Filter" area.

You should then see a dialog similar to the one on the spreadsheet image below.

Advanced Filter Setup

Why not create the example pictured here and see if you can get it working?

In a nutshell if you want to exclude blanks cells then put “<>” into the criteria cell (E2 in the example here).

If you wish to only show blanks then put “=” into the criteria cell.

Note that <> and = do not have quotes around them, even though Excel will try to prompt you for a range address after pressing enter on the = sign, this criteria must not be quoted.

Just press the equals sign and then press the return key without moving the cursor.

And that is how it’s done!

Sign up below and get notified when new Business Programmer blog or video tutorials are created.

Cases Where It May Appear Not To Work.

The problem here is that the “Blank” cells may not actually be blank and may in fact contain hidden text such as where the cell in question contains a hidden text marker like as a single quote ‘ for left aligned text or contains a Caret or Circumflex for center aligned text or double quote “” for right aligned text.

At first glance the cell appears to be empty but it in fact is not as this counts as the cell being “non empty”.

A good workarount for this problem is to add a column to your data range and add in the formula…

=len("cell containing possible blanks")

Which in the above example you could put the following formula (i.e the one above) into cell "D2" and copy it down through "D6"

=len(D2)

This will show 0 where the column is actually blank and the number of character (most likely one) when it appears to be blank but actually conatains a hidden character.

Excel Advanced Filter On Ribbon MenuJob done… You can now handle “clean” empty cells and “dirty” empty cells.

Thanks for reading this post

If you have any queries or comments please leave them below as they help to improve the quality of the information that I provide.

If you found this article useful then don't forget to share it by clicking on one of the social media buttons below.

2 Comments

  1. April Du

    October 8, 2016 at 2:19 am

    Hi Sean,

    My file has empty cells and their lengths are 0 as well. However the advanced filter still doesn’t work. Unless I delete the “empty” cells . I wonder what criteria I should put in. I also tried =””. It doesn’t work either. I really don’t want to clean up the whole column unless I have to ( which means I have to change the code due to the column being the results from other code). Is there a way to make advanced filter work?

    Many thanks,
    April

    • Sean Johnson

      October 10, 2016 at 11:42 am

      HI April
      Without seeing your spreadsheet, I think I have covered all the possibilities, that is to say…
      Exclude blank cells, put in “<>” in criteria.
      Show only blank cells put “=” in criteria.
      I can see from your comment that you have tested for the case where the cell APPEARS to be blank.

      Here’s the thing (probably my bad – kinda!).
      When I say put “=” or “<>” into the criteria cell I mean put = or <> into the criteria cell.
      The quotes are for emphasis (I just now realise it is confusing – it caught me out when I tested it just now and I will change the post to emphasis this.)

      Putting the = sign in without quotes can seem tricky because if you have Excel set to move selection after carriage return, Excel will PROMPT YOU for an address and thus you may think you are doing the wrong thing, but the equals and greater than and less than signs MUST NOT BE QUOTED.

      Hope this helps April.

Leave a Reply

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