Recently I was creating a pivot report with multiple items in row labels area. I had to show sub-totals, but only for one of the fields. Something like this:
How to show selective sub-totals in Pivot Tables
First instinct suggests that using Design ribbon > Sub-totals we can tell pivot table how we want the sub-totals.But this is like a master switch. It will turn off or on all sub-totals in the report. What we need is sub-totals only at one level of the report (in this case, sub-totals by department, but not by month).
The answer is simple.
- Click on any month label. This will select all months in the row label area.
- Right click > Field settings (you can also access this from Analyze Ribbon > Field Settings)
- In the “Subtotals & Filters” tab, choose “None”
- You are done.
Happy Pivoting. I am off to a short spin on my cycle before heading to work.
7 Responses to “Selective Sub-totals in Pivot Tables [Quick Tip]”
Found another way, just right click on any month label and then de-select Subtotal.
very comoon action for me - I use the "None" for subtotals more often than not with pivot tables- it's a pity that there isn't a way to enable it as the default setting. Also find that the field/row behaviour for applying this is different (in Excel 2010) between the non-classic and classic pivot modes when this is applied, so I always revert back to classic to retain the field names. Of course I may be missing something that the non-classic mode can do ...
Hi Chandoo,
I have a question. I have last name and first name in this format i.e. Negi, Sonu M then rest details are right below them like
Negi, Sonu M
Sharma, Soniya F
I want to correct the format like Mr. Sonu Sharma and Mrs. Soniya Sharma according to their gender respectively. Please suggest.
Hi Manoj,
I dont believe pivot tables have any decision making ability in the filter/subtotal functions to do such such a change of row level information. You would have to add Gender and Title fields into you data to do what your asking, and/or create a calculated field.
Hope that helps!
Cheers
How i can Learn Excel Basic? with Suitable example
first start learning.. automatically everything will come...
I work on pivots daily and wanted to know if there exist a solution to count number of entries for each month. On back of above example other than sub total I want to know that it relates to 3 individual , in June it relates to 1 and in July 4