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.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

4 Responses to Interesting bug – Filter with SUBTOTAL

  1. Wunder-Mold says:

    hi good morning
    I am in word Excel trying to put sequential numbers and skip 17 cells down the way it looks is in pages I don’t want to have to put down every number I would like someone to help me with he formula please .


  2. wil says:



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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.