iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To show PivotTable values as percentages, select a value, open Show Values As, and choose the calculation that matches the denominator you want. Use % of Grand Total for a share of the whole report, % of Row Total for a share of the row, or % of Column Total for a share of the column. These options answer different questions, so choose the denominator before interpreting the result.
Choose the percentage calculation that answers your question
A percentage is only meaningful in relation to its denominator. For example, a value can be a small share of the entire report but a large share of its row. The PivotTable’s Show Values As setting determines which total or item Excel uses for the calculation.
| Your question | Choose | What the percentage represents |
|---|---|---|
| What share of the whole report is this value? | % of Grand Total | The value as a share of all values or data points in the report. |
| What share of this row or category is this value? | % of Row Total | The value as a share of its row total. |
| What share of this column or series is this value? | % of Column Total | The value as a share of its column total. |
| What share of a parent item is this value? | % of Parent Row Total, % of Parent Column Total, or % of Parent Total | The value as a share of the relevant parent, depending on the field layout. |
| How does this value compare with a particular item? | % Of | The value expressed as a percentage relative to a selected base field and item, where available. |
Microsoft documents these custom calculations in Show different calculations in PivotTable value fields. A row percentage and a grand-total percentage may both be correct for the same value; they simply describe different comparisons.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Show PivotTable values as percentages
- Click a numeric value in the PivotTable.
- Right-click the value field and choose Show Values As. Microsoft describes this route as: “In the PivotTable, right-click the value field, and then click Show Values As.” If the context menu does not offer the option you need, open the value field settings and use the Show Values As tab.
- Select % of Grand Total, % of Row Total, or % of Column Total. For hierarchical data, choose the appropriate parent-total calculation. For a comparison with a particular item, choose % Of and select the base field and item if those controls are available.
- Check the results against the intended denominator before using them in a report.
Microsoft lists this workflow for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 on Windows and Mac. On a Mac, the first Show Values As menu may not display every choice; use More Options if the calculation you need is missing. Exact menu labels can vary by edition or data source. See Microsoft’s PivotTable value-field calculation instructions for the documented interface.
Keep the original amount beside the percentage
To display both the source amount and its percentage, add the same source field to the PivotTable’s Values area a second time. Leave one copy as the original summary and set Show Values As on the other copy. This lets readers see the size of the value as well as its share without replacing one view with the other. Microsoft’s value-field guidance covers using duplicate fields for separate calculations.
Set the percent sign and decimal places
Show Values As determines the calculation; Number Format controls how the result looks. To format a value field, open its settings, choose Number Format, and select a percentage format and the desired decimal places. Changing number format does not change whether the denominator is the grand total, row, column, parent, or base item. Microsoft explains the distinction in Change the summary function or custom calculation for a field in a PivotTable.
Rank #2
- Used Book in Good Condition
Fix unexpected PivotTable percentage results
Excel summarizes the field as Count instead of Sum
Check the summary function before interpreting the percentage. Microsoft says numeric values placed in the Values area default to Sum, while values Excel interprets as text appear as Count. Inspect the source column for numbers stored as text or mixed data types, then confirm that the PivotTable is summarizing the field as intended. See Create a PivotTable to analyze worksheet data.
The percentage uses an unexpected base
Reopen Show Values As and verify the selected calculation. Confirm whether the intended comparison is against the grand total, row, column, parent item, or a selected base item. A correctly calculated percentage can still be misleading if the chosen denominator does not match the question.
The percentage option is missing
On Mac, check More Options in the Show Values As menu. Availability can also depend on the PivotTable’s data source or calculation type: Microsoft notes that sources such as OLAP data, calculated fields, and calculated items can restrict changes to the summary function. Consult Microsoft’s PivotTable summary-function and custom-calculation guidance if an option remains unavailable.
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
