Excel Pivot Table Grand Total as Percentage

Charts & pivot tables · Updated November 22, 2026

To show a pivot table’s Grand Total as a percentage, you add a calculated field that divides the Grand Total by itself (or by a specific reference total) and format it as a percentage. The simplest method: right-click any cell in the Grand Total row or column, choose Show Values As > % of Grand Total. This instantly converts every value, including the grand total, into a percentage of the overall total.

Why the Grand Total Doesn’t Automatically Show as a Percentage

By default, Excel’s pivot table Grand Total is the sum (or count, average, etc.) of all values in that row or column. It’s a raw aggregate, not a computed ratio. When you apply a value field setting like “% of Grand Total,” Excel recalculates each individual cell as a percentage, but the Grand Total itself remains a sum of those percentages—which, logically, equals 100% (or the sum of the percentages if multiple fields are involved). This is why you often see 100% or a number like 200% in the Grand Total after applying a percentage view. To get the Grand Total to display as a meaningful percentage (e.g., 100%), you need to adjust how that total is calculated.

How to Display the Grand Total as a Percentage (Step-by-Step)

There are two main approaches, depending on what you want the percentage to represent.

Method 1: Show Values As > % of Grand Total (Quickest)

  1. Click any value cell inside the pivot table (not the Grand Total).
  2. Right-click and choose Show Values As > % of Grand Total.
  3. Now every cell shows its share of the overall grand total. The Grand Total row/column will show 100% (if there’s only one row/column of data) or the sum of the percentages if multiple fields are in the Values area.

Note: This method changes the entire value field. The Grand Total becomes 100% only if you have a single row or column field. If you have multiple fields (e.g., both Region and Product in Rows), the Grand Total will show the sum of the row percentages, which may exceed 100%.

Method 2: Add a Calculated Field for a Custom Grand Total Percentage

If you need the Grand Total to show a specific percentage (like 100% always), create a calculated field that divides the value by the grand total of that value. This is more flexible.

  1. Click anywhere in the pivot table.
  2. Go to PivotTable Analyze (or Analyze in older versions) > Fields, Items & Sets > Calculated Field.
  3. In the Name box, type something like “Pct of Grand Total”.
  4. In the Formula box, enter: = 'Sales' / GETPIVOTDATA("Sales",$A$3) – but replace ‘Sales’ with your actual value field name, and $A$3 with the top-left cell of your pivot table (the anchor cell).
  5. Click Add, then OK.

This calculated field divides each value by the grand total from the GETPIVOTDATA function. The Grand Total of this calculated field will then be 100% (since the sum of all values divided by the grand total equals 1). Format the field as a percentage by right-clicking it > Number Format > Percentage.

Understanding the Difference: % of Grand Total vs. % of Column/Row Total

Excel offers several “Show Values As” options, and they affect the Grand Total differently:

  • % of Grand Total: Each cell is divided by the grand total of all values. The Grand Total row/column shows 100% if there’s only one dimension.
  • % of Column Total: Each cell is divided by that column’s total. The Grand Total column will show 100% for each row, but the Grand Total row will show the sum of those percentages (often not 100%).
  • % of Row Total: Each cell is divided by that row’s total. The Grand Total row shows 100% for each column, but the Grand Total column shows the sum.

Choose the one that answers your business question. If you want each product’s share of all sales, use % of Grand Total. If you want each region’s share within a specific product, use % of Row Total.

Common Problems and How to Fix Them

Problem 1: Grand Total shows 100% but you wanted a different percentage. That’s correct for % of Grand Total. If you expected something else, you likely need a different base.

Problem 2: Grand Total shows a huge number like 500%. This happens when you have multiple row or column fields. For example, if you have Year and Quarter in Rows, the Grand Total sums the percentages for each quarter, which can exceed 100%. To get a clean 100%, use a calculated field with GETPIVOTDATA.

Problem 3: The percentage doesn’t update when you filter the pivot table. The % of Grand Total option recalculates based on the visible data. If you filter out rows, the grand total changes, and percentages update accordingly. That’s usually desired.

Problem 4: You see blanks or errors in the Grand Total. This can occur if you have empty cells or if the calculated field references a field that doesn’t exist. Double-check the field names in the formula.

Using PivotTable Options to Control Grand Totals

You can also turn Grand Totals on/off and choose whether they appear for rows or columns. Right-click the pivot table > PivotTable Options > Totals & Filters tab. Here you can check/uncheck “Show grand totals for rows” and “Show grand totals for columns.” This doesn’t change the percentage calculation but affects whether you see the totals at all.

Alternative: Using a Regular Formula Outside the Pivot Table

If you prefer not to modify the pivot table, you can create a separate table that references the pivot’s Grand Total cell. For example, if your pivot’s grand total is in cell B10, you can write =B2/$B$10 in a cell next to the pivot to get each value’s percentage. This gives you more control but requires manual updating if the pivot changes size.

Frequently Asked Questions

Q: How do I show the grand total as a percentage in a pivot table?
A: Right-click any value cell > Show Values As > % of Grand Total. The Grand Total will then show 100% if you have a single row/column field. For multiple fields, use a calculated field with GETPIVOTDATA to force 100%.

Q: Why is my pivot table grand total showing 200%?
A: This occurs when you have multiple row or column fields (e.g., Year and Quarter). Each cell is a percentage of the overall total, so the sum of those percentages across all rows exceeds 100%. To get a clean 100%, use a calculated field that divides by the grand total.

Q: Can I format the grand total as a percentage without changing the other values?
A: Yes, by using a calculated field. Add a calculated field that references the original value field and divides it by the grand total (using GETPIVOTDATA). Then format only that calculated field as a percentage. The original values remain unchanged.

Q: Does the % of Grand Total option work on Mac Excel?
A: Yes, the steps are identical. Right-click the cell and choose Show Values As > % of Grand Total. The ribbon path is the same, though the tab might be called “Analyze” on Mac.

Q: How do I get the grand total to show as 100% when I have multiple fields in the rows?
A: Use a calculated field. For example, if your value field is ‘Sales’ and the pivot table starts at A3, add a calculated field with formula =Sales/GETPIVOTDATA("Sales",$A$3). This will give each row its percentage of the grand total, and the grand total of that calculated field will be 100%.

Q: Can I show the grand total as a percentage of a subtotal instead?
A: Yes, use “% of Parent Row Total” or “% of Parent Column Total” in Show Values As. This calculates each cell as a percentage of its parent subtotal, not the grand total. The grand total will then show the sum of those parent percentages.

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.