Forum Discussion

tmoerke's avatar
tmoerke
Copper Contributor
May 09, 2020

Excel subtotal command ignores filters

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? 

  • Khizar_Hayat's avatar
    Khizar_Hayat
    Brass Contributor

    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 

    • GeorgeF65's avatar
      GeorgeF65
      Copper Contributor

      Les_Worrall 

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

      • Raerea's avatar
        Raerea
        Copper Contributor
        Formulas tab, under calculation Options, switch to automatic. This solved my issue
  • mnikhil31's avatar
    mnikhil31
    Copper Contributor

    tmoerke 

    I know its too late to reply to your query however check and see if the cell next to the SubTotal formula is blank and is being selected when you use filter. I faced the same problem with my data in excel and it turns out that you also have to select the column or row which has the subtotal formula for it to show the result of your filter. I hope this helps.

Resources