none
PowerPivot - how to create row subtotals that shift with table data and maintain formatting RRS feed

  • Question

  • Hi,

    I have tried this with a standard Excel table and it is failing badly.  The reason being that I need to add subtotals to a result of data (preferably with a blank spacer row underneath each subtotal and the remaining result) and retain all formatting.  You can't have subtotal rows in Excel tables, so I figured perhaps I could do this with PowerPivot?

    Is it worth me bothering, or is this not possible?

    Thanks

    Thursday, November 13, 2014 2:39 PM

Answers

  • Hi,

    You can't have subtotal rows in Excel tables (driven by MS Query), only totals.

    Anyway, I went the wrong way with it.  Instead of PowerPivot, I used a standard pivot table and it dealt with everything as apart from the fact that I want it to Repeat All Items on all rows except for subtotal and grand total rows... no biggie - unless someone knows how to do that...?

    Cheers



    Thursday, November 13, 2014 6:26 PM

All replies

  • There's a Subtotal feature built-in to Excel to insert those subtotals (and remove!) for you.
    Find it on the Data tab, Outline group.  Sort your data by the column to break on and tell  it which column has the values to subtotal.

    But I've abandoned that model completely.  It's old-fashioned paper-emulating design.  

    Instead, I put =SUBTOTAL() functions at top of sheet where users can see them and let them filter the table however they like (or use slicers).  Try it, you'll like it.  ;-)

    Thursday, November 13, 2014 4:30 PM
  • Hi,

    You can't have subtotal rows in Excel tables (driven by MS Query), only totals.

    Anyway, I went the wrong way with it.  Instead of PowerPivot, I used a standard pivot table and it dealt with everything as apart from the fact that I want it to Repeat All Items on all rows except for subtotal and grand total rows... no biggie - unless someone knows how to do that...?

    Cheers



    Thursday, November 13, 2014 6:26 PM