Excel PivotTable Percentages: How to Choose the Right One

Updated 6 November 2026 · Standard formula, shown below

Excel PivotTable percentage calculations can show values as a share of a row, column, grand total, or parent item, and choosing the wrong one gives you a misleading number. This article explains the main percentage options, how to read each one, and how to pick the correct calculation for your data. You’ll also learn how to add percentages in a PivotTable and avoid common mistakes.

Why PivotTable Percentages Are Confusing

When you right-click a value field in a PivotTable and choose Show Values As, Excel offers more than a dozen percentage options. The labels are short — “% of Grand Total,� “% of Column Total,� “% of Parent Row Total� — but each one changes the meaning of the number. The same raw data can produce very different percentages depending on the option you select, so it’s essential to understand what each calculation actually does.

PivotTable percentages are always calculated from the underlying data that is already summarized in the PivotTable. They are not recalculated from the original source data; they are based on the values currently displayed in the pivot. This means that if you filter the PivotTable, the percentages change because the underlying total changes.

How PivotTable Percentage Calculations Work

Every percentage option in a PivotTable takes the value in a cell and divides it by some other value, then multiplies by 100. The “other value� is what differs between the options:

% of Grand Total: divides each cell by the grand total of all values in the PivotTable.
% of Column Total: divides each cell by the total of its column.
% of Row Total: divides each cell by the total of its row.
% of Parent Row Total: divides each cell by the total of its parent row (useful in hierarchical data).
% of Parent Column Total: divides each cell by the total of its parent column.
% of Parent Total: divides each cell by the total of its immediate parent (row or column, depending on layout).
% of Row Total / Column Total: these are the same as the above but named differently in some Excel versions.

The key is to know what “total� is being used as the denominator. For example, if you have sales data with regions in rows and products in columns, “% of Grand Total� shows each cell as a share of all sales, while “% of Column Total� shows each cell as a share of that product’s total sales across all regions.

How to Choose the Right Percentage Calculation

Choosing the correct percentage depends on the question you want to answer. Here are common scenarios and the right option:

“What share of overall sales did each region contribute?� → Use % of Grand Total.
“Within each product, how does regional distribution compare?� → Use % of Column Total (if products are columns).
“Within each region, what is the product mix?� → Use % of Row Total (if regions are rows).
“How does each subcategory contribute to its parent category?� → Use % of Parent Row Total or % of Parent Column Total.
“What is the percentage change from one period to the next?� → This is not a percentage share but a difference; use % Difference From with a base field.

When you have multiple row or column fields (e.g., Year and Quarter), the parent options become important. “% of Parent Row Total� gives the share of the immediate parent row, while “% of Grand Total� gives the share of the entire table. For hierarchical data, parent percentages are usually more meaningful.

Common Mistakes and Misconceptions

Using “% of Grand Total� when you want “% of Column Total� — This is the most common error. If you want to compare regions within a product, column total is correct; grand total would compare regions across all products, which is a different question.
Percentages don’t add up to 100% — If you use “% of Parent Row Total� in a multi-level pivot, the percentages across the whole table won’t sum to 100% because each sublevel is relative to its own parent, not the grand total.
Filtering changes percentages — If you filter out some rows, the totals used for percentages change, so the percentages recalculate. This is correct behavior but can surprise users who expect static percentages.
Using “% Difference From� when you need a simple share — “% Difference From� shows the percentage change between two values, not a proportion. They are different calculations.
Rounding errors — Percentages are displayed with the number of decimal places you set. If you round to 0 decimals, the displayed percentages may not sum to 100% due to rounding.

How to Add Percentages in a PivotTable (Step by Step)

Click anywhere inside your PivotTable.
In the PivotTable Fields pane, drag the field you want to calculate a percentage for into the Values area (if it’s not already there).
Click the drop-down arrow next to the value field name in the Values area (or right-click the value in the pivot table).
Select Value Field Settings.
Go to the Show Values As tab.
Choose the percentage option from the drop-down list (e.g., % of Grand Total).
Optionally, set the number format to show decimal places (e.g., 1 or 2).
Click OK.

The value field will now display percentages. If you need to keep the original numbers, add the field to the Values area twice — one as a normal sum, one as a percentage.

Comparing Percentage Options: A Concrete Example

Suppose you have a PivotTable with regions in rows (North, South) and products in columns (A, B). The raw sums are:

North: A=100, B=200, Total=300
South: A=300, B=100, Total=400
Grand Total: A=400, B=300, Total=700

If you apply % of Grand Total, the North-A cell shows 100/700 = 14.3%. If you apply % of Column Total, the same cell shows 100/400 = 25% (because column A total is 400). If you apply % of Row Total, it shows 100/300 = 33.3% (because North’s total is 300). Each percentage answers a different question, and none is “wrong� — but only one matches what you want to convey.

When to Use “% Difference From� Instead

If you want to show how much a value has changed compared to a previous period, a base item, or another field, use % Difference From in the Show Values As menu. You specify a base field (e.g., Month) and a base item (e.g., previous month). The result is the percentage change, not a share of a total. This is useful for month-over-month growth, year-over-year changes, or comparing actual vs. budget.

Note that “% Difference From� requires a base field and item. If you choose “(previous)� as the base item, Excel uses the previous item in the sort order of that field. This is a quick way to get a running percentage change.

Frequently Asked Questions

Why do my PivotTable percentages not add up to 100%?

If you are using “% of Parent Row Total� or “% of Parent Column Total,� the percentages are relative to different parents, so they won’t sum to 100% across the whole table. Also, if you have rounding to 0 decimal places, the displayed numbers may not sum exactly to 100% due to rounding. Check the decimal places and the percentage option you selected.

How do I show both the count and the percentage in a PivotTable?

Add the same field to the Values area twice. For one copy, keep the default calculation (Sum, Count, etc.). For the other copy, right-click it, choose Show Values As, and select the percentage option you want. You can also rename the fields to make it clear which is which.

What is the difference between “% of Column Total� and “% of Row Total�?

“% of Column Total� divides each value by the total of its column, so each column sums to 100%. “% of Row Total� divides each value by the total of its row, so each row sums to 100%. Use column total when you want to compare values within a column; use row total when you want to compare within a row.

Can I use a percentage calculation with a filtered PivotTable?

Yes, but remember that the percentages are based on the visible (filtered) data. If you filter out some rows, the totals used as denominators change, so the percentages recalculate. If you want percentages based on the unfiltered data, you need to either remove the filter or use a different approach, such as adding a calculated field with a fixed denominator.

Why does “% of Parent Row Total� give different results than “% of Grand Total�?

Because the denominator is different. “% of Grand Total� uses the overall total of all values in the PivotTable. “% of Parent Row Total� uses the total of the immediate parent row (e.g., if you have Year > Quarter, the parent row for Quarter 1 is the Year total). This is useful for hierarchical data where you want to see the contribution to a specific subgroup, not the entire table.

How do I change the number of decimal places for the percentage?

Right-click the value field, choose Value Field Settings, click Number Format, select “Percentage� and set the desired decimal places. This only affects the display, not the underlying value.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.