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.
The Issue
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.
Testing
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:
Workarounds
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.
First of all, thanks for mentioning! 🙂
Secondly, simply want to echo your post as I encountered this bug before, just that I did not look into the details as you did… Let’s me see what “Chinese Characters” I would see when I back to work where I have Excel 2010.
Finally, I’ve found another bug with Filter in Excel 2010. That’s quite strange actually. When you have range of data with a column of formula with SUBTOTAL (not other functions), AutoFilter refuses to take the last row of your data… even if you select the whole range manually before applying the Filter, it still won’t take the last row. @_@
I am going to post my findings of that in next next week. Stay tuned. 🙂
Cheers,
MF
This is a “Know Issue” – Was not there is 2007 got introduced in 2010 and got fixed in 2013.
Thanks Sam. I also remember now that this filter feature and related ones were only added with Excel 2010.
Woops, that’s not true. These features were there in Excel 2007.