Turn pivot totals into shares of the whole with Show Values As — no formula needed. See what fraction each region, product, or month contributes to the grand total.
The example
Regional sales as a percent of the total.
| A | B | |
|---|---|---|
| 1 | Region | % of total |
| 2 | East | 32% |
| 3 | West | 29% |
| 4 | North | 39% |
The formula
A right-click setting, not a formula:
How it works
Show Values As reframes the numbers:
- Add the value field to the pivot a second time (so you keep the totals too, if you want).
- Right-click the value → Show Values As.
- Pick % of Grand Total (or % of Row/Column Total, or % of a parent).
- Excel converts the field to percentages — the underlying data is untouched, just the display.
So many options: Show Values As also does Running Total, Rank, % Difference From a base, and more — all without formulas. The raw-data equivalent of % of total is simply =value / SUM(values).
Try it: interactive demo
Values (one per line) → % of total.
Variations
% of row/column
Show Values As → % of Row (or Column) Total.
Running total
Show Values As → Running Total In.
Formula equivalent
On raw data:
Pitfalls & errors
Display only. Show Values As changes how numbers appear, not the source data — other formulas still see the raw totals.
Pick the right base. % of Grand Total, Row Total, and Column Total give different answers. Choose the one that matches your question.
Add the field twice if you want both the count/total and its percent side by side.
Practice workbook
Frequently asked questions
How do I show PivotTable values as a percent of the total?
Does Show Values As change my data?
What's the formula version of percent of total?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class