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
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.
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 workaround 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.
Job done… You can now handle “clean” empty cells and “dirty” empty cells.
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
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.
For blanks, I had a lot of problems — what I finally did was to use the filter command, and filter for blanks. Then I did clear contents of all the blank cells. I did the same in another column, and then did the or type advanced filter and it worked well.
Sir, Sean Johnson,
I m very impressed with such a beautiful solution of “Filtering records containing blank cells in data by Advance Filter”. I was searing, exploring, hunting the solution very badly since a few days. Ultimately I got it.
Thnx a lot.
Nitin Shukla,
Surat, India.
Sir,
I want both empty and non empty cell for advance filter. when i use “” then empty cell excluded, when i use “=” then only empty cell filtered. so i need both empty and not empty cell for filtering multiple column.