Pivot Table Values as a Percentage of Total

Pivot Table Values as a Percentage of Total

Pivot Table Values as a Percentage of Total

There are 2 ways to show Pivot Table values as a percentage of the total. The first one which is commonly used isn’t very nimble. When your data changes the formulas may not compute correctly. I always say the worst mistake you can have in Excel doesn’t show an error message, just the wrong number!

Problem Choice: Formulas Outside the Pivot Table

You may have figured out how to turn off the Generate GetPivot option for your Pivot Totals. This allows you to create a formula outside your Pivot Table that performs the same as any other relative cell reference formula. In other words, A1 + B1 becomes A2 + B2 as you copy it down. The problem is that you are dividing a value in the pivot table by its Grand Total which can move depending upon how the Pivot Table changes in length as it is refreshed.

Perc of Total2

Better Choice: Show Values as a Percentage of Grand Total

The easiest way to do this is also the best way. If you want to show both the percentage of total AND the values themselves, simply add the value field a second time. Then, right click on the newly added “duplicate” column. Choose Show Values As and then % of Grand Total.

Perc of Total3

 

No Comments

Post a Reply