To create percentages in a pivot table, you don’t need to write any formulas 😀
- Drag the field where you want to apply the percentage
- Change the calculation options. In the calculation options of the field, you will find the percentage option.
Don’t create formulas
It is very common for users to add percentage formulas to the pivot table. For example, to calculate each country’s percentage, we have created the following formula.
=GETPIVOTDATA(“Total”,$A$3,”Country”,A4)/GETPIVOTDATA(“Total”,$A$3)
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 percentages in a pivot table?
Adding percentages to a pivot table, it’s very easy and doesn’t need a formula 😀👍
- 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 ! 😀😎
Related Articles
- Calculate Percentage Change in Excel
- How to add percentages to the chart’s data labels?
- Calculating Percentage Without Using Formulas 😀
- How to add filter options to all your pivot table columns?
- Split Pivot Table report to many sub-reports automatically
Percentage parent
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 two 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
However, you can correct this situation by changing the calculation mode option 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 😀👍
11/07/2023 @ 18:37
Great job! I searched through several videos but this did the trick!
12/07/2023 @ 02:04
Thank you so much for this lovely comment
12/08/2022 @ 20:53
I have tried to drag and drop the Sum of Total Filed to have it twice, however, the sysetm does not let me do it, so I copy pasted it, but it won’t work as part of the pivot, not sure what am I missing. Could you please help?