Excel Pivot Table Show Percentage of Total

Charts & pivot tables · Updated November 22, 2026

Yes, you can show a percentage of total in an Excel pivot table by adding a calculated field or by using the “Show Values As” option on a value field. The fastest method is to right-click a value in the pivot table, choose Show Values As, then select % of Grand Total.

What “Percentage of Total” Means in a Pivot Table

In a pivot table, a percentage of total expresses each value as a share of a larger total. For example, if you have monthly sales figures, showing “% of Grand Total” turns each month’s sales into a percentage of the year’s total. This makes comparisons easier, especially when the raw numbers vary in scale.

Excel offers several related options under Show Values As:

  • % of Grand Total – each value divided by the grand total of all values in the pivot table.
  • % of Column Total – each value as a percentage of its column’s total.
  • % of Row Total – each value as a percentage of its row’s total.
  • % of Parent Row Total – percentage of the parent row’s subtotal (useful in multi-level hierarchies).
  • % of Parent Column Total – percentage of the parent column’s subtotal.

These options are available in Excel 2010 and later, including Microsoft 365, Excel 2021, and Excel for the web. The menu path is identical on Windows and Mac.

How to Show Percentage of Grand Total (Step by Step)

Here’s the quickest way to change a value field to show a percentage of the grand total:

  1. Click anywhere inside the pivot table to activate the PivotTable Analyze tab (or Analyze on Mac).
  2. In the Values area, right-click the field you want to change (for example, “Sum of Sales”).
  3. Select Show Values As.
  4. Choose % of Grand Total.

Alternatively, you can use the Value Field Settings dialog: right-click the value field, choose Value Field Settings, go to the Show Values As tab, and pick the option from the dropdown.

After applying, the values in that field will display as percentages, and the field header will change to something like “Sum of Sales1” (the original field remains unchanged). If you want to keep the original raw numbers, you can add the same field twice to the Values area and format one as a percentage.

Using a Calculated Field for Custom Percentages

Sometimes you need a percentage that is not directly one of the built-in options, such as “percentage of a specific region’s total” or “percentage of a previous month.” In those cases, you can add a calculated field.

To add a calculated field:

  1. Click anywhere in the pivot table.
  2. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
  3. Give the field a name, for example “Pct of Total”.
  4. In the Formula box, enter a formula that divides the field by the grand total. For instance, if your original field is named “Sales”, use =Sales/GETPIVOTDATA("Sales",$A$3) – but note that GETPIVOTDATA can be tricky because it references the pivot table’s location.

A simpler approach is to add a helper column in your source data that calculates the percentage before creating the pivot table. For example, if you have a column of sales figures in B2:B100 and a grand total in B101, you can add a formula in C2: =B2/$B$101 and format it as a percentage. Then include that column in your pivot table.

However, for most needs, the built-in Show Values As options are sufficient and easier to update when the data changes.

Choosing the Right Percentage Option

Selecting the correct percentage type depends on the structure of your pivot table and the question you want to answer.

  • If you want to see each item’s share of the entire dataset, use % of Grand Total.
  • If your pivot table has multiple columns (e.g., different years) and you want to compare within each column, use % of Column Total.
  • If you have multiple rows (e.g., product categories) and want to see the composition within each row, use % of Row Total.
  • If you have a hierarchy (e.g., Region > Country > City), % of Parent Row Total shows each city as a percentage of its country, not of the grand total.

For example, in a pivot table with Product in Rows and Year in Columns, choosing % of Row Total will show each product’s sales as a percentage of that product’s total across all years. Choosing % of Column Total will show each product’s share within a specific year.

Common Problems and How to Fix Them

Many users run into a few recurring issues when working with percentages in pivot tables.

  • Percentages show as decimals (0.25 instead of 25%) – Right-click the value field, choose Value Field Settings, click the Number Format button, and select Percentage.
  • The percentage option is grayed out – This happens when the field is not in the Values area. Only value fields can be changed to percentages.
  • After changing to %, the original numbers disappear – Add the same field a second time to the Values area, then format one as % and keep the other as a number.
  • Percentages don’t add up to 100% – If you have multiple value fields, each is calculated independently. Also, if you have filters or multiple rows, the grand total may be based on visible data only.

If you need to show both the count and the percentage, you can drag the same field into the Values area twice, then use Show Values As on one of them.

Comparing Percentages with Other Show Values As Options

Besides percentages, Excel’s Show Values As menu includes other useful calculations:

  • Running Total In – shows cumulative totals.
  • Difference From – compares values to a base item (e.g., previous month).
  • % Difference From – shows percentage change relative to a base item.
  • Index – a more advanced measure that compares relative importance.

For example, to see month-over-month growth, you would choose % Difference From and set the base field to the month and the base item to (previous). This is different from a simple percentage of total because it compares each value to the prior one, not to the whole.

Frequently Asked Questions

How do I show percentage of total in a pivot table in Excel 2016?

The steps are the same as in newer versions. Right-click the value field, choose Show Values As, then select % of Grand Total. The option exists in Excel 2010 and later.

Can I show both the count and the percentage in the same pivot table?

Yes. Drag the same field into the Values area twice. For one copy, leave it as a count or sum. For the other, right-click and choose Show Values As > % of Grand Total.

Why does my percentage show as a decimal like 0.15?

Excel is showing the raw decimal value. To display it as a percentage, right-click the value field, choose Value Field Settings, click Number Format, select Percentage, and set the desired decimal places.

How do I show percentage of a subtotal in a pivot table?

Use % of Parent Row Total or % of Parent Column Total. These options calculate the percentage relative to the parent subtotal in a hierarchy. For example, if you have Region > Country, choosing % of Parent Row Total will show each country as a percentage of its region’s total.

Can I use a formula to calculate percentage of total in a pivot table?

Yes, you can add a calculated field, but it’s often simpler to use the built-in Show Values As options. If you need a custom calculation, add a helper column in your source data with a formula like =B2/SUM($B$2:$B$100) and include that column in the pivot table.

Does the percentage update automatically when I refresh the pivot table?

Yes, the percentage is recalculated whenever the pivot table refreshes, because it is based on the underlying data. If you add new rows to your source data, refresh the pivot table to update the percentages.

Final Tips for Using Percentages in Pivot Tables

When presenting percentage data, remember to format the cells as percentages with the desired number of decimal places. Also, be aware that the percentage is based on the pivot table’s current filter and slicer state. If you filter out some rows, the percentages will recalculate based on the visible data, which may or may not be what you intend.

If you need to freeze the percentage as a static value, you can copy the pivot table and paste it as values, but that breaks the link to the source data. For most reporting, keeping the dynamic percentage is more useful.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.