Say you are trying to show what percentage each product category contributed to total sales last quarter. You drag Category into rows, Sales into values, right-click, find “Show Values As,” and pick “% of Grand Total.” The numbers appear instantly and they look reasonable — until someone in the meeting asks why the percentages don’t match what they expected, or why adding a Region field to the columns suddenly changed every number on the sheet.
That reaction is common because “percentage of total” isn’t a single setting. It’s a family of related calculations, each dividing by a different base, and Excel’s default choice is rarely the one that matches the question you actually meant to ask. Below is a checklist for diagnosing which percentage problem you’re facing and applying the right fix, organized by the symptom you’re seeing rather than by feature name.
Symptom: The percentages add up to 100%, but not the 100% you meant
Cause: You’re looking at “% of Grand Total,” which divides every cell by the single overall total in the entire pivot table — regardless of how many row or column fields you’ve added. If you have Category in rows and Region in columns, each cell shows its share of the entire dataset, not its share within its own region or its own category.
Fix: Right-click a value cell, choose Show Values As, and pick a more specific base depending on what you’re trying to isolate:
- % of Column Total — shows each cell as a share of its own column’s total. Use this when you want to know, within each region, how the categories split up.
- % of Row Total — shows each cell as a share of its own row’s total. Use this when you want to know, within each category, how the regions split up.
- % of Parent Row Total or % of Parent Column Total — useful when you have nested fields and want a subcategory’s share of its immediate parent group, rather than the grand total.
The setting you want almost always depends on which direction the question is pointed. “What share of total revenue came from the Northeast” is a Grand Total question. “Within the Northeast, what share came from each product line” is a Row Total or Column Total question, depending on your layout.
Symptom: Adding a new field to the pivot table changed every percentage on the sheet
Cause: This is expected behavior, not a bug, and it trips people up constantly. “% of Grand Total” recalculates the grand total every time the pivot table’s structure changes. Add a Region field to columns, and the grand total is still the sum of everything — but now each cell’s share is being computed against a total that includes categories broken out by region, which can shift rounding and interact differently with filters applied elsewhere on the sheet.
Fix: Before troubleshooting the math, check whether a filter or a slicer is hiding part of the data. A percentage calculation only considers the values currently visible in the pivot table, so if a slicer is filtering out three regions, “100%” now refers to the remaining regions only, not your full dataset. If you need percentages that stay anchored to the complete dataset regardless of what’s filtered on screen, that’s a sign you need a DAX measure in the Data Model rather than the standard Show Values As option — a measure like DIVIDE([Sales], CALCULATE([Sales], ALL(Table))) ignores the current filter context and always divides by the true, unfiltered total.
Symptom: The percentage column shows 0% or blank for rows that clearly have data
Cause: Usually one of two things. Either the value field has a text entry mixed into an otherwise numeric column somewhere upstream, or “Show Values As” is pointing at the wrong base field — for example, “% of Parent Row Total” is selected, but the row in question has no parent group above it, so there’s nothing to divide against.
Fix: Check the source column’s data type first — a single stray text cell in a numeric field can silently exclude that row from percentage calculations without throwing an obvious error. If the data type checks out, revisit the Base Field dropdown under Show Values As and confirm it’s set to a level that actually exists for every row in your layout. If some rows sit at a different depth in the hierarchy than others — a common issue when row fields aren’t uniformly nested — “% of Parent” calculations can behave inconsistently row to row.
Symptom: You want percentage of total, but running down a list over time — not compared to everything at once
Cause: You’re asking for a cumulative percentage — often called “running percent of total” — and the default percentage options don’t do this on their own. Show Values As has an option for this, but it’s easy to miss because it’s listed separately from the plain percentage calculations.
Fix: Right-click the value field, choose Show Values As, and select “% Running Total In.” Pick your date field as the Base Field, the same way you would for a numeric running total. Each row now shows the cumulative percentage of total through that point in time, rather than an isolated period’s share. This is the right tool for questions like “what percentage of our annual revenue had we hit by the end of Q2,” as distinct from “what percentage of annual revenue did Q2 alone represent.”
Symptom: The percentages look right in the pivot table, but wrong once exported or copied elsewhere
Cause: “Show Values As” percentages are a display property of the pivot table itself, not an underlying stored value. Copy the cells as values into a plain range, and you’re copying the formatted percentage number — but if anyone later refreshes assumptions based on that copied range, they’re working from a frozen snapshot that won’t update if the source data changes.
Fix: If the percentages need to live outside the pivot table and stay dynamic, don’t copy the cells. Instead, reference them with a GETPIVOTDATA formula, or better, build the percentage independently with a formula that divides the specific value by the specific total you need, using cell references back to the pivot table so it updates on refresh. This takes an extra step up front, but it prevents the common failure where a report gets rebuilt every month by copying stale percentage values into a summary sheet no one remembers to refresh.
Symptom: Two categories that should sum to the same total show percentages that don’t add up to 100% between them
Cause: This typically happens when a filter is applied to only part of the pivot table’s fields, or when “Show Values As” is set differently across value fields that appear to be showing the same measure. It can also happen when subtotals are turned off, which changes what Excel considers the total being divided against for grouped rows.
Fix: Check Design, then Subtotals, to confirm subtotals are switched on and consistent across the row fields involved. Then confirm every value field showing a percentage is using the same Show Values As setting — it’s easy to set “% of Column Total” on one field and accidentally leave a second, similar-looking field on “% of Grand Total,” which produces numbers that look almost right but never quite reconcile.
A Reference Table for Percentage of Total Settings
| What you want to know | Setting to use | Base Field |
|---|---|---|
| Share of the entire dataset | % of Grand Total | None needed |
| Share within its own column | % of Column Total | None needed |
| Share within its own row | % of Row Total | None needed |
| Share of its immediate parent group | % of Parent Row/Column Total | Parent field |
| Cumulative share up to this point in time | % Running Total In | Date field |
| Share that ignores current filters entirely | DAX measure with ALL() | N/A — Data Model required |
Most percentage disputes come down to a mismatch between the base someone assumed and the base Excel actually used. Before adjusting anything, ask yourself one question: percentage of what, exactly? Once you can answer that in a single sentence, the correct setting from the table above usually becomes obvious.
What does your percentage need to be measured against — the whole table, its row, its column, or its running position over time? Pin that down first, and the rest of the setup takes about ten seconds.