There’s a moment with every PivotTable where the sums are right and the insight still hasn’t arrived. North: £1.42M. South: £1.18M. Fine — but is North growing? What share is that? How does the year accumulate? You can haul the pivot’s output into percent formulas by hand, and most people do, monthly, forever.
The pivot already knows how. Right-click any value → Show Values As — the most underused menu in Excel — and the same field answers a different kind of question, recomputed live through every slicer click and refresh.
The four settings that carry working life
% of Grand Total / % of Column Total. Every value becomes its share — the percent-of-total pattern with zero formulas and zero pinning. The trick that unlocks it fully: drag the value field in twice — once as plain Sum, once shown as % of total. Amount and share, side by side, one refresh.
Difference From / % Difference From (previous). Set the base field to your grouped months, base item to (previous), and the pivot writes the month-on-month change column — the exact “new minus old over old” you’d have built by hand, computed at every level and immune to the stretched-range bug because there are no ranges.
Running Total In. Cumulative revenue through the year, month by month — the year-to-date line every dashboard wants, and the natural partner of a target line on the chart.
Rank. The league table, as a pivot column — re-ranked on refresh, ties handled, no LARGE folklore.
Why this beats formulas-on-pivot-output
The monthly ritual this replaces — copy the pivot, paste values, add percent columns — breaks in all the familiar ways: it’s stale by the next refresh, its ranges stop a row short when a region appears, and the percent-of-total maths goes quietly wrong when someone filters. Show Values As recomputes inside the aggregation, so the shares are right at every grouping — the same virtue you’ll later meet as measures in the data model, of which this menu is the friendly foothill. (When you outgrow it — ratios of two different fields, time-intelligence beyond “previous” — that’s precisely the DAX doorway.)
Two crafts notes. Label the lens. A column headed “Sum of Amount” that’s secretly % of total is a chart-crime in table form — retitle the field (“Share %”, “vs prev month”) so the reader knows which question is being answered. And percent views deserve percent costumes — set it in Value Field Settings → Number Format so the lens survives refresh, per the usual rule.
Sums answer “how much”. Shares, changes, cumulations and ranks answer “so what”. The second brain was in the right-click menu the whole time — and it refreshes with everything else.