Filter Bug in Excel 2007

Question

I've discovered a bug in Excel 2007 that causes some odd behavior with table filters. It's a little hard for me to describe what's happening to you in words, so here's a link to an example file that exhibits this behavior:

https://skydrive.live.com/redir?resid=D9668D8C6D7BCB10!17317&authkey=!AEf7zekMsmZY6NI

Note that I was also able to reproduce this same bug in Microsoft's web-based version of Excel in SkyDrive. Here's the steps to reproduce the problem using the spreadsheet I linked to above:

1. Open the Spreadsheet I linked to above using Excel 2007 or the web based version of Excel in SkyDrive.
2. Click the drop-down menu in the "Filter Column" in cell B1.
3. Remove "A" from the filter for this column and click OK. Rows with "A" in the filter column should disappear.
4. Now add A back to the filter column and click "OK". All rows should re-appear.

--- Now here's where the bug occurs ---

5. Open the drop-down menu for the Filter Column again. Note that "C" is missing from the list of options that can be filtered from the column.
6. Uncheck "B" and click OK. Note that there are still rows that contain "B" visible in the table. The filter is no longer working correctly. You can continue to check and uncheck these filter options until the filter becomes almost unusable.
7. Go to the data tab in Excel's ribbon interface and toggle the Filter option off and then back on again. (This step cannot be performed in the web-based version of Excel.) Note that the filters and columns have returned to normal.

I believe this bug is somehow caused by the presence of the "Visible?" column in the example spreadsheet. After removing that column I was unable to reproduce the problem. Note that cell C13 in my example illustrates a practical reason for having that column.

I'd appreciate it if Microsoft would fix this issue. Also, please let me know if you know of a way around this problem. (Perhaps there is an alternative way of checking if a row is visible that doesn't trigger this issue?)

Answer 1

I note that the problem only occurs if the range is a "table".  If you convert the table back to a range, the problem no longer occurs.

Answer 2

I've encountered this issue and it can be a serious problem, especially if you're not aware it's happening.

What I have started to do as a workaround is completely remove all filtering between queries. Do not just unfilter each column, but actually turn off the filter, then back on again. So far it seems to be working but I've only tested it a few times. It adds a few steps to each procedure but I can't afford to have inaccurate reports.

Interested to hear how that works out for you.

Answer 3

Is that a question or a statement? ;) Yes, those were the only workarounds I was able to find. Why? Are they not working for you?

Answer 4

Those are the only workarounds which you were able to find in the link which you have given.

Answer 5

Looks like I'm not the only one who had this problem. This article has a detailed explanation of what's causing the problem and a few possible workarounds: http://blog.contextures.com/archives/2010/03/19/number-the-visible-rows-in-excel-autofilter/