Prevent subtotals in certain columns of a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-206876%22%20slang%3D%22en-US%22%3EPrevent%20subtotals%20in%20certain%20columns%20of%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206876%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20prevent%20subtotaling%20in%20one%20of%20the%20columns%20of%20my%20pivot%20table.%26nbsp%3B%20All%20the%20help%20pages%20I%20can%20find%20on%20this%20topic%20tell%20me%20to%20click%20on%20the%20field%20and%20then%20choose%20Field%20Settings%2C%20and%20that%20in%20that%20Field%20Settings%20dialogue%20box%20there%20will%20be%20a%20Subtotals%20and%20Filters%20tab.%26nbsp%3B%20I%20am%20using%20Excel%202016%2C%20and%20I%20see%20no%20Subtotals%20and%20Filters%20tab.%26nbsp%3B%20The%20two%20tabs%20I%20see%20are%20%22Summarize%20Values%20By%22%20and%20%22Show%20Values%20As%22.%26nbsp%3B%20I%20see%20nowhere%20to%20remove%20the%20subtotals.%26nbsp%3B%20What%20am%20I%20missing%20here%3F%3F!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20screen%20shot%20of%20what%20I%20see%20when%20I%20open%20the%20Value%20Field%20Settings%20dialogue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-206876%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2790086%22%20slang%3D%22en-US%22%3ERe%3A%20Prevent%20subtotals%20in%20certain%20columns%20of%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2790086%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F37127%22%20target%3D%22_blank%22%3E%40Matt%20Mickle%3C%2FA%3E%3CBR%20%2F%3EThe%20question%20is%20about%201%20column%2C%20not%20all.%20How%20is%20this%20done%20if%20it%20is%20only%20for%201%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206984%22%20slang%3D%22en-US%22%3ERe%3A%20Prevent%20subtotals%20in%20certain%20columns%20of%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206984%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Jonathan-%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20you're%20doing%20well.%26nbsp%3B%20Did%20you%20try%20doing%20the%20following%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Clicking%20in%20the%20Pivot%20Table%20Area%3C%2FP%3E%0A%3CP%3E2.%20Navigating%20to%20the%20PivotTable%20Tools%20%26gt%3B%20Design%20Contextual%20Tab%20on%20the%20Ribbon%3C%2FP%3E%0A%3CP%3E3.%20Go%20to%20SubTotals%20%26gt%3B%20Do%20not%20show%20SubTotals%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20this%20doesn't%20work%20for%20your%20scenario%20could%20you%20please%20provide%20a%20bit%20more%20detail%20on%20your%20data%20structure%20%2F%20PivotTable%20structure%20please.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2790147%22%20slang%3D%22en-US%22%3ERe%3A%20Prevent%20subtotals%20in%20certain%20columns%20of%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2790147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1168249%22%20target%3D%22_blank%22%3E%40JenBo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight%20click%20on%20the%20field%20and%20enable%20or%20disable%20subtotal%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20290px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313224i9B91CCFD0D014FB1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20question%20was%20about%20subtotals%2C%20not%20about%20grand%20totals.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to prevent subtotaling in one of the columns of my pivot table.  All the help pages I can find on this topic tell me to click on the field and then choose Field Settings, and that in that Field Settings dialogue box there will be a Subtotals and Filters tab.  I am using Excel 2016, and I see no Subtotals and Filters tab.  The two tabs I see are "Summarize Values By" and "Show Values As".  I see nowhere to remove the subtotals.  What am I missing here??!

 

I have attached a screen shot of what I see when I open the Value Field Settings dialogue.

4 Replies

Hey Jonathan-

 

Hope you're doing well.  Did you try doing the following?

 

1. Clicking in the Pivot Table Area

2. Navigating to the PivotTable Tools > Design Contextual Tab on the Ribbon

3. Go to SubTotals > Do not show SubTotals

 

If this doesn't work for your scenario could you please provide a bit more detail on your data structure / PivotTable structure please.

@Matt Mickle
The question is about 1 column, not all. How is this done if it is only for 1?

@JenBo 

Right click on the field and enable or disable subtotal

image.png

The question was about subtotals, not about grand totals.

Easily done in Power Pivot.
Would you like to change the sum to average
in one of the 7 columns that I can see so clearly?