A participant in one of my workshops had built a separate calculated field, three helper columns in her source data, and a manual formula outside the pivot table entirely, all just to show each region’s sales as a percentage of the company total.
She had never discovered the Show Values As dropdown, a built-in pivot table feature that would have replaced all of that work with a single menu selection. This is genuinely common — the feature exists, but it is tucked inside a settings dialog most users never open, so they rebuild its functionality manually instead, every time, without realizing a native option already exists.
This tutorial walks through what each Show Values As option actually calculates, with particular attention to the one mistake that produces technically-running-but-wrong-looking numbers almost every time it happens.
Where to Find Show Values As
Right-click any value in your pivot table’s Values area, choose Show Values As, and a submenu appears with all available calculation options. Alternatively, right-click the field itself in the Values area of the field list, choose Value Field Settings, and select the Show Values As tab in the dialog that opens — this second route also lets you rename the resulting field, which the right-click shortcut does not.
% of Grand Total
This calculates each cell as a percentage of the single overall total across your entire pivot table, regardless of how your data is grouped by rows or columns. If your pivot table shows regional sales, every region’s percentage will sum to 100% across the whole table.
Worth using when: You want to know each item’s share of the absolute overall total, with no further breakdown by any other dimension.
% of Column Total and % of Row Total
These calculate percentages relative to a subset rather than the grand total. % of Column Total makes each value a percentage of its specific column’s total (useful when you have, say, years as columns and want each region’s share within each individual year). % of Row Total does the equivalent calculation across each row instead.
The mistake this catches people on: Selecting % of Grand Total when you actually wanted % of Column Total produces numbers that are mathematically correct but answer a different question than intended — every percentage will look smaller than expected, because it is being measured against the entire table’s total rather than the specific column or row you actually care about.
How to tell which one you need: Ask directly whether you want each value’s share of everything in the table, or its share within one specific column or row grouping. If you find yourself looking at a percentage and thinking “that seems too small,” this mismatch is the first thing worth checking.
% of Parent Row Total and % of Parent Column Total
These are specifically useful when your pivot table has nested row or column groupings (for example, Region nested under Country). Rather than calculating against the absolute grand total or the outermost row/column, these calculate each value’s percentage relative to its immediate parent grouping — a city’s percentage of its specific region, rather than of the entire country.
Worth using when: You have a multi-level hierarchy and want each level’s percentage to reflect its share within its own direct parent group, not the entire table.
Running Total In
This calculates a cumulative sum as you move down through your data in a specified field’s order — each row’s value plus every value above it. This is genuinely useful for tracking cumulative totals over time (cumulative sales by month, for example).
The order-dependency trap: Running Total In depends entirely on your data being sorted in the order you actually want accumulated. If your months are not sorted chronologically in your pivot table, your “running total” will accumulate in whatever order they currently appear, producing a cumulative figure that does not represent what you actually intended.
How to confirm this is set up correctly: Before trusting a running total, explicitly check that the field driving the accumulation (typically a date or sequence field) is sorted in the order you actually want it accumulated in, rather than assuming it is automatically sorted correctly.
% Difference From and Difference From
These compare each value against a specific base item or field you choose — commonly a previous period, for calculating period-over-period change.
The base-field trap that catches almost everyone: When setting this up, the dialog asks you to choose a Base Field and a Base Item. If you select “(previous)” as the base item, the comparison shifts dynamically relative to whatever item comes before each one in your field’s order. If you instead select one specific fixed item (like a specific baseline year), every other value compares against that single fixed point rather than shifting relative to its immediate predecessor.
These produce genuinely different results, and selecting the wrong one for your intended comparison is the single most common mistake with this option. A workshop participant comparing “this year vs last year” accidentally selected a single fixed base year instead of “(previous),” and every subsequent year ended up compared against that one original year rather than the year immediately before it.
How to confirm this is the cause: If your percentages look unexpectedly large or strange for later periods specifically, check whether your Base Item is set to “(previous)” or to one specific fixed item, since this single setting determines what every other value is actually being measured against.
Rank Largest to Smallest and Rank Smallest to Largest
Rather than showing the actual value or a percentage, these display each item’s rank position relative to the others in your specified base field — useful for quickly seeing which categories perform best or worst without needing to manually sort and count.
Worth using when: You want to highlight relative standing (top performer, bottom performer) rather than the underlying value itself, particularly in a dashboard view where rank communicates the point faster than raw numbers.
A Quick Reference for Choosing the Right Option
| What You Want to Know | Use This Option |
|---|---|
| Each item’s share of the entire table’s total | % of Grand Total |
| Each item’s share within its specific column or row group | % of Column Total / % of Row Total |
| Each item’s share within its immediate parent in a hierarchy | % of Parent Row/Column Total |
| Cumulative total building up over a sorted sequence | Running Total In |
| Change relative to the previous period specifically | % Difference From, Base Item = “(previous)” |
| Change relative to one fixed baseline period | % Difference From, Base Item = that specific period |
| Relative standing among categories | Rank Largest to Smallest / Smallest to Largest |
The Habit Worth Building
Before selecting any Show Values As option, explicitly state the actual question you are trying to answer — share of the whole table, share of one specific group, change from the immediately preceding period, or change from one fixed baseline. Matching that explicit question to the corresponding option, rather than picking whichever one sounds closest and checking if the numbers look reasonable afterward, is what separates a quick correct setup from the kind of confused manual rebuilding that workshop participant had been doing for weeks.
What comparison or percentage are you trying to show in your pivot table? Describe what you want each value measured against, and I can tell you exactly which Show Values As option fits your specific case.