This article explains how to do a Percentage in a Pivot Table. NO FORMULAS are needed.
Don't create formulas
It is very common to see users add percentage formulas out of the pivot table.
For example here, to calculate the percentage for each country, we have create the following formula.
This technique is wrong because when you will refresh your pivot table, the references of the cells will change. And then the result won't be correct.
How to add percentage?
Adding percentage to a pivot table it's very easy.
- Drag and drop the same field 2 times
- Click on the arrow (on the left of the field)
- Select the option Value Field Settings
- In the dialog box, select the tab Show Values As
- Then, in the dropdown list, you select % of Grand Total
AND THAT'S ALL ! 😀😎
But, if your pivot table presents a hierarchy between your data, the calculation of the percentage could be inaccurate.
For instance, in this example, you have a pivot table for the categories and the sub-categories. We have 2 columns : the sales and the percentage.
In this situation, the option % of Grand Total is correct only for the first level.
When you expand the sub-levels, you can see that the percentages don't represent 100% of the previous level
But you can correct this situation by changing the option of calculation mode in the pivot table. Instead of % of Grand Total, you select % of Parent total.
And you select the upper level field representing the Grand Total
Then, each sub-level represents 100% of the previous level 😀👍