Home

Pivot tables - column grand total no longer available

%3CLINGO-SUB%20id%3D%22lingo-sub-889386%22%20slang%3D%22en-US%22%3EPivot%20tables%20-%20column%20grand%20total%20no%20longer%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889386%22%20slang%3D%22en-US%22%3E%3CP%3EA%20few%20days%20ago%20I%20right%20clicked%20on%20a%20pivot%20table's%20column%20header%20and%20selected%20to%20remove%20the%20column%20grand%20total...%20now%20Excel%20behaves%20as%20if%20I%20permanently%20deleted%20the%20option.%20I%20could%20use%20assistance%20with%20figuring%20out%20how%20to%20add%20it%20back.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20go%20into%20the%20Pivot%20Table%20Options%2C%20the%20column%20grand%20total%20is%20checked%20off%2C%20and%20if%20I%20click%20on%20the%20Pivot%20Table%20Design%20tab%2C%20the%20Grand%20Totals%20for%20both%20rows%20and%20columns%20has%20been%20turned%20on%2C%20yet%20the%20column%20will%20not%20reappear.%20This%20happens%20will%20any%20spreadsheet%2C%20so%20it%20is%20not%20spreadsheet%20specific.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20recommendations%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135143iA5F4D2C007C5256E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-02_13-18-05.png%22%20title%3D%222019-10-02_13-18-05.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20307px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135140iD657FF10D0BE4FAF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-02_13-18-34.png%22%20title%3D%222019-10-02_13-18-34.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-889386%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889522%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%20-%20column%20grand%20total%20no%20longer%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889522%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418923%22%20target%3D%22_blank%22%3E%40jstanford%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAre%20Mailing%20City%20and%20Mailing%20Country%20column%20names%20in%20your%20source%3F%20In%20such%20case%20it%20shall%20be%20no%20Grand%20Total%20for%20rows%20since%20Pivot%20Table%20calculates%20totals%20based%20on%20fields.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20drag%20any%20column%20into%20Columns%20part%20of%20right%20pane%2C%20grand%20total%20shall%20appear.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20228px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135170iE0AB5BF61E84E6AD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889908%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%20-%20column%20grand%20total%20no%20longer%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889908%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bsorry%2C%20I%20should%20have%20provided%20more%20screenshots.%20It%20happens%20if%20the%20variables%20are%20listed%20under%20Columns.%20I%20tried%20everything%20I%20know%20but%20nothing%20worked.%20BUT%20it%20just%20hit%20me%20that%20I%20haven't%20rebooted%20my%20laptop%20in%20a%20few%20days%2C%20so%20I%20tried%20that%2C%20and%20the%20issue%20is%20gone.%20Smh%2C%20knowing%20it%20is%20Windows%2C%20a%20reboot%20should%20have%20been%20one%20of%20the%20first%20things%20I%20tried!%20Thanks%20for%20trying%20to%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889910%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%20-%20column%20grand%20total%20no%20longer%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418923%22%20target%3D%22_blank%22%3E%40jstanford%3C%2FA%3E%26nbsp%3B%2C%20okay%2C%20great%20to%20know%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-995735%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%20-%20Row%20grand%20total%20no%20longer%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-995735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BHello!%26nbsp%3B%20I%20just%20encountered%20the%20same%20issue%20but%20with%20rows%20not%20totaling%20anymore.%26nbsp%3B%20I%20shut%20down%20my%20laptop%20yesterday%20when%20this%20happened%20but%20the%20issue%20still%20there%20today%20when%20I%20started%20up.%26nbsp%3B%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155882i70805C1942A2AFF3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-995867%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%20-%20Row%20grand%20total%20no%20longer%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-995867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F452371%22%20target%3D%22_blank%22%3E%40decko%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPower%20Pivot%20adds%20totals%20for%20rows%20if%20as%20columns%20you%20have%20values%20of%20one%20field.%20Here%20you%20have%20two%20separated%20fields%2C%20REVENUE%20AMT%20and%20FREIGHT%20AMT.%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20total%20as%20Calculated%20field%2C%20let%20name%20it%20Total%20and%20expression%20will%20be%20%3DREVENUE%20AMT%2BFREIGHT%20AMT.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20use%20similar%20measure%20if%20you%20added%20your%20data%20to%20data%20model.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
jstanford
New Contributor

A few days ago I right clicked on a pivot table's column header and selected to remove the column grand total... now Excel behaves as if I permanently deleted the option. I could use assistance with figuring out how to add it back. 

 

If I go into the Pivot Table Options, the column grand total is checked off, and if I click on the Pivot Table Design tab, the Grand Totals for both rows and columns has been turned on, yet the column will not reappear. This happens will any spreadsheet, so it is not spreadsheet specific.

 

Any recommendations would be greatly appreciated!

 

2019-10-02_13-18-05.png

 

 

2019-10-02_13-18-34.png

 

 

5 Replies
Highlighted

@jstanford 

Are Mailing City and Mailing Country column names in your source? In such case it shall be no Grand Total for rows since Pivot Table calculates totals based on fields.

 

If you drag any column into Columns part of right pane, grand total shall appear.

image.png

Highlighted

@Sergei Baklan sorry, I should have provided more screenshots. It happens if the variables are listed under Columns. I tried everything I know but nothing worked. BUT it just hit me that I haven't rebooted my laptop in a few days, so I tried that, and the issue is gone. Smh, knowing it is Windows, a reboot should have been one of the first things I tried! Thanks for trying to help!

Highlighted

@jstanford , okay, great to know

Highlighted

@Sergei Baklan Hello!  I just encountered the same issue but with rows not totaling anymore.  I shut down my laptop yesterday when this happened but the issue still there today when I started up.  Any ideas?

 

clipboard_image_0.png

Highlighted

@decko 

Power Pivot adds totals for rows if as columns you have values of one field. Here you have two separated fields, REVENUE AMT and FREIGHT AMT.

You may add total as Calculated field, let name it Total and expression will be =REVENUE AMT+FREIGHT AMT.

 

Or use similar measure if you added your data to data model.

Related Conversations
Filter complex table to simple table
Sam2009 in Excel on
1 Replies
Excel Table Size in Macro
WayneEK in Excel on
6 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies