Selective Sub-totals in Pivot Tables [Quick Tip]

Posted on May 2nd, 2017 in Excel Howtos , Pivot Tables & Charts - 5 comments

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:

selective-subtotals-excel-pivots

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.

  1. Click on any month label. This will select all months in the row label area.
  2. Right click > Field settings (you can also access this from Analyze Ribbon > Field Settings)
  3. In the “Subtotals & Filters” tab, choose “None”
  4. You are done.

disabling-subtotals-for-field
Happy Pivoting. I am off to a short spin on my cycle before heading to work.

Click here for more pivot table tips & tricks.

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

5 Responses to “Selective Sub-totals in Pivot Tables [Quick Tip]”

  1. sonitcell says:

    Found another way, just right click on any month label and then de-select Subtotal.

  2. Mark says:

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

  3. Manoj says:

    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.

    • Mark says:

      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

  4. Shailesh Chaudhary says:

    How i can Learn Excel Basic? with Suitable example

Leave a Reply