Menu
I am trying to build a pivot table in Excel to summarize some data, like
I want columns A and B to be in compact form and column C to be outlined; also I need subtotals for the B column. Letting subtotals appear on the last row, everything works fine
But when I go to the B field contextual menu, select "Field Settings/Layout & Print" and select "Display subtotals at the top of each group" subtotals break down: some do not appear (for the "b/a" configuration) and some others give the wrong result (60 for the "a/a" combination).
Is there a way out of this? I am using Excel 2010 but tested the same file on Excel 2007 and the same issue seems to arise.
Any advice would be greatly appreciated.
290
In a PivotTable, subtotals are calculated automatically and appear by default. Here's how to hide them: Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. Click Design > Subtotals, and then pick Do Not Show Subtotals.
In all versions of Excel, pivot tables are far more flexible than Data > Subtotal. You can Group Dates, write formulas, use slicers, create charts from pivot tables. Data > Subtotal only summarises data - essentially generates a series of SUMIF(), COUNTIF(), MAX IF etc.
Since you're not interested in subtotals for the first column (A), I suggest creating a separate column containing the concatenation of A and B, which then yield the desired result:
Unique combinations are grouped and totalled correctly.
131
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
