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)
- Click any value cell inside the pivot table (not the Grand Total).
- Right-click and choose Show Values As > % of Grand Total.
- 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.
- Click anywhere in the pivot table.
- Go to PivotTable Analyze (or Analyze in older versions) > Fields, Items & Sets > Calculated Field.
- In the Name box, type something like “Pct of Grand Total”.
- 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). - 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
- Excel Conditional Formatting Color Scales Explained
- Excel Center Louisville KY: What It Is & How It Works
- Excel Choose Date from Calendar Cell: How to Add a Date Picker
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table: How to Remove a Calculated FieldTo remove a calculated field from an Excel pivot table, click anywhere inside the pivot table, go to…
- Google Sheets Chart Series Name: Meaning and FixesIn Google Sheets, a chart series name is the label that identifies one set of plotted values, usually…
- Excel Histogram Function: Charts, Bins, and FREQUENCYExcel does not have a worksheet function named HISTOGRAM. To make a histogram in Microsoft 365 for Windows,…
- Zero Line on a Graph: Meaning and How to Show It in ExcelA zero line on a graph is the reference line marking a value of zero on an axis.…
- Automatic Pareto Chart in Excel: Make One That UpdatesTo make an automatic Pareto chart in Excel for Microsoft 365 on Windows, select a single column of…
- Excel Line Chart With Error Bars: How to Add ThemTo add error bars to a line chart in Excel for Microsoft 365 on Windows, select the chart,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.