Filter by Selected Cell’s Value – Bug With Blanks

Filter by Selected Cell’s Value – Bug With Blanks

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:

table

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).

filter menu

When I do this all rows are filtered away and I can see the odd filter criteria in the little tooltip that appears:

preview

This image show the filter dropdown and the Number Filters dialog (if you do column A you’ll see a Text Filters dialog instead):

filter dialog

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:

translation

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.

4 thoughts on “Filter by Selected Cell’s Value – Bug With Blanks

  1. 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

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>