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