I’ve found a bug in Excel 2010 that I don’t find described at all when I search the internet. It involves the Filter by Selected Cell’s Value command and blank cells.
In Excel 2010 when I select a blank cell in a table, or a non-table and then choose “Filter by Selected Cell’s Value” from the right-click menu, the column is filtered by what look like Chinese and non-printable characters. It occurs for me every time in a Table. In an informal list it sometimes only occurs after filters are applied.
I’ve seen the issue on all five Excel 2010 computers I’ve tried, all with Windows 7. I tried it on one computer with Office 2013 and couldn’t replicate the bug.
How to Replicate It
Create a table like this one. It can actually be just one column:
Right-click in one of the blank cells and choose Filter > Filter by Selected Cell’s Value. (The mouse-averse can do Menu Key, E, V as described in this wmfexcel post).
When I do this all rows are filtered away and I can see the odd filter criteria in the little tooltip that appears:
This image show the filter dropdown and the Number Filters dialog (if you do column A you’ll see a Text Filters dialog instead):
What I’d expect, and see in Excel 2013, is that the (Blanks) item in the dropdown would be checked.
You can see that there are a mix of non-printing characters and what Google Translate sometimes recognizes as Chinese characters. I see different character combinations on different computers. Here’s one of my home computers:
The obvious one is to select “(Blanks)” in the filter dropdown. Another is to upgrade to Excel 2013.
A partial one is to use add the “Autofilter” control to the Ribbon. This only works for non-Table lists though. With a Table it only turns the filters on and off. I think this control goes back to Excel 2003 – you can add it to a right-click menu by using MenuRighter and choosing Add Command > Data > Autofilter.
So, Did Everybody Else Already Know This?
I’m always hesitant to cry “bug,” but this seems pretty clear. My other fear is looking silly for announcing something everybody already knew. Feel free to comfort me, or not.
I haven’t tried it in Excel 2007 yet. If anybody wants to that would be great.