How to Filter on Blanks quickly

A simple but very useful method. All of us use AutoFilter in Excel. The dropdown shows unique values in the column. Sometimes we just want to see the rows with blank cells. Unfortunately, the (blank) item appears at the end of the list. If the data contains many unique values, you have to waste time and scroll manually to the bottom of the long list. Often, after reaching the list we realize that we should actually have unselected all the items first. Then you go up again! Pathetic. Here is a smart way to Filter on Blanks instantly.

Here is a simple solution. Check if you have a Search textbox in the filter dropdown. If yes, just type an opening bracket there (

Now, the blank item is shown directly and it is the only one selected. Quick and Easy.

Filter on Blanks - search textbox

Filter on Blanks - Results

If you do not see the Search textbox, you are using an older version of Excel. This feature was added in 2010.  Same functionality works in Pivot Table field dropdowns as well.

Queries | Comments | Suggestions | Wish list