Excel subtotal command ignores filters

Copper Contributor

I have a simple range of rows and columns. The first column is department and another column is location. The rows consist of descriptions of projects taking place within a department at different locations. I have used the subtotal command which enables the expansion of each department listing their projects. The subtotal rows count the number of projects and various other metrics in other columns. Then, of course, there is a grand total row at the bottom. So far, so good, the perfect solution I was looking for. However, now I simply want to filter the location column so I just show projects at a single location. This works fine when the table is fully expanded. However, when I compress the range to just the subtotal rows and then expand a particular department or the whole table, it ignores the filtering and shows all the locations again, even though that column is filtered. Is this a bug? I've search for hours on the web to see if anyone has had a similar problem but have not found a mention. I don't see another simple solution to this. Pivot tables won't work because I don't want to summarize all the data, I want to show the rows and subtotals for those rows filtered one way or the other. Seems like this should be easy. The only other solution I can envision is to create my own table and use macros to do the same thing the subtotal command does, but have it work properly which would be a lot of work. Or create a separate tables for each location. Any thoughts? 

5 Replies

the best way is to convert your range into table then go to the table option and select total row, it will not effect the filter as well, try it and tell me what happen ?@tmoerke 

@tmoerke Hi. I am loading this word document explaining step for step how I think you are trying to acheive your objective. It's totally different data, but it shows you how to get what I think is the result you are trying to acheive. Have a read and hope it helps.


I think you are missing our problem.  When filtering, the subtotal, 9 does not change the sum to reflect what is being filtered (as would be expected).

Formulas tab, under calculation Options, switch to automatic. This solved my issue

@Raerea It's already on Automatic.