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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]||Start your bar charts from zero – Excel geeks screaming at you from mountain top »|