PDA

View Full Version : Pivot Table - Hide Columns + Maintain Grand Total



philipq
10-20-2014, 08:34 AM
Greetings Friends,

I have been doing a bit of coding in VBA on some reports for the place I work.
I am already 90% through and up with this seemingly small challenge.

I need to display only one of the column value (count) and yet show the sum of all column values.
In other words I need to have the columns hidden for the other columns but total should show that of all.
So I only want certain columns hidden.
However, when columns are hidden on the interface, pivot table totals only what is displayed.
But in effect the pivot table object still has all the hidden data available to it, doesn't it?
It should be able to calculate the total of all columns and display it maybe as a separate calculated column via VBA or something in that line.
Do you think there could be such a possibility?
Could anyone explain if this can be done via VBA.
Greatly appreciate your help.

Sample data as below:
I need to display column labeled '2' only and hide all other possibilities and yet keep grand total values the same.



Calls Handled

Column Labels







Row Labels

1

2

3

4

Grand Total



ALA-ARA-SER

340

831


11

1182



ALA-ENG-SER

80

171


2

253



ALQ-ARA-SER

41

119



160



ALQ-ENG-SER

179

483



662



CEN-ARA-SER

866

2658

1

80

3605



CEN-ENG-SER

861

3595


50

4506



DIP-ENG-SER

5

2



7



FUJ-ARA-SER

29

55



84



FUJ-ENG-SER

9

7



16



GAR-ARA-SER

232

626


9

867



GAR-ENG-SER

319

901


1

1221



MUS-ARA-SER

783

1968


35

2786



MUS-ENG-SER

726

2016


31

2773



QAB-ARA-SER

65

145


1

211



QAB-ENG-SER

209

412



621



RAK-ARA-SER

212

358


6

576



RAK-ENG-SER

61

107



168



SHJ-ARA-SER

287

696


6

989



SHJ-ENG-SER

246

570



816



SZR-ARA-SER

498

1459


32

1989



SZR-ENG-SER

1452

4481


4

5937



Grand Total

7500

21660

1

268

29429

p45cal
10-21-2014, 02:03 PM
cross posts:
http://www.mrexcel.com/forum/excel-questions/812799-pivot-table-hide-columns-maintain-grand-total.html
http://www.ozgrid.com/forum/showthread.php?t=191504

Bob Phillips
10-22-2014, 12:26 AM
PivotTable Tools>Design>SubTotals>Include Filtered Items In Totals