PivotdrillPercent of grand total
Right-click a value in the pivot, choose Show Values As, then % of Grand Total. Every cell becomes its share of the overall total and the Grand Total reads 100%. Drag the field into Values a second time first if you want the raw numbers alongside the percentages.
Syntax
Right-click a value > Show Values As > % of Grand Total| Part | What it is |
|---|---|
% of Grand Total | Cell divided by the total of the whole pivot |
% of Parent Row Total | Cell divided by its group's subtotal, for nested rows |
% of Parent Total | Same idea but you choose the base field |
Examples
Rows: Region. Values: Sum of Revenue, Sum of Revenue (% of Grand Total)Result: East 12,000 and 30.0%, North 8,000 and 20.0%, South 14,000 and 35.0%, West 6,000 and 15.0%, Grand Total 40,000 and 100.0%.
Rows: Level. Values: Count of Employee ID (% of Grand Total)Result: Headcount share per level, totaling 100%.
Rows: Region, Rep. Values: Sum of Revenue (% of Parent Row Total)Result: Each rep as a share of their own region; each region block sums to 100%.
Common mistakes
- Percentages change when you filter: the base is the visible total, so a filtered pivot shows shares of the filtered total.
- The column still shows Sum of Revenue as its name: rename it to Share in Value Field Settings so readers know it is a percent.
- Too many decimals: Show Values As formats as 0.00%; change it under Value Field Settings, Number Format.
Practice Percent of grand total
These drills check your answer on live data, free, no account needed.
- Share of revenueShow each Region's Revenue as a percentage of total revenue.
- Headcount share by levelShow how the staff is split by Level, as a percentage of all staff.
Questions
How do I show both the number and the percentage?
Drag the same field into Values twice. Leave one as a plain sum and set the other to % of Grand Total.
What is the difference between % of Grand Total and % of Column Total?
With only Rows they match. When a Columns field exists, % of Column Total uses each column's total as the base instead of the whole table.
Related
- Percent of row and column totalUse Show Values As with % of Row Total or % of Column Total in a region by product grid. Which direction adds up to 100 percent and how to read each one.
- Difference from previousCompare periods inside a pivot table with Show Values As, Difference From and % Difference From. Pick the base field and (previous); why period one is blank.
- Multiple value fieldsAdd two or more fields to Values, decide whether they sit side by side or stacked with the Sigma Values item, and show one field twice as a sum and a count.
- Number format in a pivot tableFormat pivot table values so the format survives a refresh: use Value Field Settings, Number Format rather than the Home ribbon. Currency and percent.