How to do Percentage in a Pivot Table in Excel?

How to do Percentage in a Pivot Table in Excel?
Last Updated on 12/05/2024
Reading time: 3 minutes

To create percentages in a pivot table, you don't need to write any formulas 😀

  1. Drag the field where you want to apply the percentage

    Drag it in the Value text box

  2. 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 to see users add percentage formulas out of the pivot table. For example here, to calculate the percentage for each country, we have created the following formula.

=GETPIVOTDATA("Total",$A$3,"Country",A4)/GETPIVOTDATA("Total",$A$3)

Wrong way to calculate percentage in a Pivot Table

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 😀👍

  1. Drag and drop the same field 2 times
Drag and Drop the same field 2 times
  1. Click on the arrow (on the left of the field)
  2. Select the option Value Field Settings
Menu Value Field Setting
  1. In the dialog box, select the tab Show Values As
Pivot Table Tab Show Values As
  1. Then, in the dropdown list, you select % of Grand Total
Menu percentage of Grand Total

AND THAT'S ALL ! 😀😎

Percentage in the pivot table

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.

Percentage with a hierachy between the data

In this situation, the option % of Grand Total is correct only for the first level.

Percentage of the first level

When you expand the sub-levels, you can see that the percentages don't represent 100% of the previous level

Percentage not 100 percent 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.

Option percentage of Parent Total

And you select the upper-level field representing the Grand Total

Select the upper level field

Then, each sub-level represents 100% of the previous level 😀👍

Percentage parent return 100 of sub levels

3 Comments

  1. Patrick Lonchar
    11/07/2023 @ 18:37

    Great job! I searched through several videos but this did the trick!

    Reply

    • Frédéric LE GUEN
      12/07/2023 @ 02:04

      Thank you so much for this lovely comment

      Reply

  2. Caro Serra
    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?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to do Percentage in a Pivot Table in Excel?

Reading time: 3 minutes
Last Updated on 12/05/2024

To create percentages in a pivot table, you don't need to write any formulas 😀

  1. Drag the field where you want to apply the percentage

    Drag it in the Value text box

  2. 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 to see users add percentage formulas out of the pivot table. For example here, to calculate the percentage for each country, we have created the following formula.

=GETPIVOTDATA("Total",$A$3,"Country",A4)/GETPIVOTDATA("Total",$A$3)

Wrong way to calculate percentage in a Pivot Table

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 😀👍

  1. Drag and drop the same field 2 times
Drag and Drop the same field 2 times
  1. Click on the arrow (on the left of the field)
  2. Select the option Value Field Settings
Menu Value Field Setting
  1. In the dialog box, select the tab Show Values As
Pivot Table Tab Show Values As
  1. Then, in the dropdown list, you select % of Grand Total
Menu percentage of Grand Total

AND THAT'S ALL ! 😀😎

Percentage in the pivot table

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.

Percentage with a hierachy between the data

In this situation, the option % of Grand Total is correct only for the first level.

Percentage of the first level

When you expand the sub-levels, you can see that the percentages don't represent 100% of the previous level

Percentage not 100 percent 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.

Option percentage of Parent Total

And you select the upper-level field representing the Grand Total

Select the upper level field

Then, each sub-level represents 100% of the previous level 😀👍

Percentage parent return 100 of sub levels

3 Comments

  1. Patrick Lonchar
    11/07/2023 @ 18:37

    Great job! I searched through several videos but this did the trick!

    Reply

    • Frédéric LE GUEN
      12/07/2023 @ 02:04

      Thank you so much for this lovely comment

      Reply

  2. Caro Serra
    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?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *