Interesting bug – Filter with SUBTOTAL

In the process of writing my previous post, I found something wrong with Filter…  when Column A contains formula starts with SUBTOTAL, Auto Filter refuses to take the last row.

Excel Tips - Filter Bug

  • Note: Data Tab –> Filter does the same thing

Excel Tips - Filter Bug 1

What an unexpected result as there is no blank row in my range.  @_@ Then I decided to enforce the selection (manually select the whole range) before I applied Filter to the range, Filter still stood firm to refuse the last row….

Excel Tips - Filter Bug 2  Excel Tips - Filter Bug 3

I wondered if the order of the formula has any impact… so I swapped column A with column B. Same result. I tried a few other functions (including AGGREGATE) to replace SUBTOTAL, Filter works normally…

Excel Tips - Filter Bug 4

For whatever reason, SUBTOTAL is not a good friend of Filter. @_@ Anyone knows why? Please share in comments. p.s. I am using Excel 2010


I tried select one extra row; and even whole columns.  Neither worked.  The not-so-perfect workaround is to input a space below the data before you turn on the Filter.  However, there is always “Blanks” in your filter list.  Another option is to copy one extra row of SUBTOTAL…. Weird enough too… ~_~


Thanks Debra Dalgleish so much that she pointed me to the right direction for the reason and workaround  (please refer to the link of her blog post in comment below).  It inspired me to modify the formula just a little bit to make it work.

Excel Tips - Filter Bug 5

Do you see the difference?

Other thoughts:

To play safe, it seems better to make it a habit to check if Filter includes the very last row of your intended range. Also pay attention when you turn on and off Filter.

This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

3 Responses to Interesting bug – Filter with SUBTOTAL

  1. wil says:



  2. I’ve run into this problem too, and there are a couple of workarounds in my blog post here:


    • MF says:

      Hi Debra,
      What a pleasure to see your comment here! Thank you very much!
      The Workaround 2 in your post works great. And it inspires to try to wrap the formula with (), and it works exactly in the same way.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s