To show a value as a percent of its subtotal in an Excel pivot table, add the field to the Values area, then right-click it and choose Show Values As > % of Parent Row Total (or % of Parent Column Total). This calculates each item as a percentage of its own subtotal group, which is exactly what “percent of subtotal” means.
What “Percent of Subtotal” Actually Means
In a pivot table, a subtotal is the total for a group—for example, the total sales for a region, a product category, or a quarter. “Percent of subtotal” shows how much each individual row contributes to that group’s subtotal, not to the grand total. This is different from the more common “% of Grand Total,” which divides each value by the overall total of the entire table.
For instance, if you have sales data with a Region field and a Product field, the subtotal for “East” region is the sum of all East sales. Showing each product’s sales as % of that East subtotal tells you the product mix within the East region. This is useful for comparing the composition of different groups without being distorted by the size of the groups.
How to Set It Up in Excel
Here are the exact steps for Excel for Microsoft 365 on Windows (the steps are the same on Mac, but the right-click menu is slightly different):
- Create your pivot table: select your data range, then go to Insert > PivotTable.
- Drag the field you want to group by (e.g., Region) into the Rows area.
- Drag a second field (e.g., Product) into the Rows area, below the first field.
- Drag the numeric field (e.g., Sales) into the Values area.
- Right-click any value in the Values area, then choose Show Values As > % of Parent Row Total.
- If your subtotals are shown as columns instead of rows, choose % of Parent Column Total instead.
After applying, each value becomes a percentage. For example, if East subtotal is $100 and Product A is $25, it shows 25.00%.
Show Values As Options Explained
The “Show Values As” menu has several percentage options. Here’s how they differ:
- % of Grand Total – divides each value by the grand total of the entire pivot table.
- % of Column Total – divides each value by the column total (useful when your layout has columns).
- % of Row Total – divides each value by the row total.
- % of Parent Row Total – divides each value by the subtotal of the parent row (the group above it). This is the one that gives you “percent of subtotal.”
- % of Parent Column Total – same, but for column-based groupings.
- % of Parent Total – divides by the parent’s total, but only works when you have multiple levels in the same row/column.
For most cases, “% of Parent Row Total” is what you want when you have two or more row fields and you want each item as a share of its immediate group.
Common Problems and Fixes
If the percentage doesn’t look right, check these:
- Wrong parent level: If you have three row fields, “% of Parent Row Total” uses the immediate parent. If you want a higher-level parent, use “% of Parent Total” and specify the base field in the dialog that appears.
- Subtotals are hidden: The calculation still works, but you won’t see the subtotal numbers. To show them, right-click the field name in the Rows area, choose Field Settings, then on the Subtotals & Filters tab, select “Automatic” or “Custom.”
- Percentages are decimals like 0.25 instead of 25%: This is normal—Excel displays the raw decimal. Right-click the value cells, choose Number Format, and select Percentage with the desired decimal places.
- Blank or zero subtotals: If a group’s subtotal is zero, the percentage will show a #DIV/0! error. You can hide that by using a custom number format, but the underlying error remains.
Percent of Subtotal vs. Percent of Grand Total
Many people confuse these two. The key difference is the denominator. “% of Grand Total” always divides by the overall total of the entire pivot table. “% of Parent Row Total” divides by the subtotal of the group that the row belongs to. For example, if you have two regions, East and West, and you want to know what share of East’s sales each product represents, you need % of Parent Row Total. If you want to know what share of all sales each product represents, use % of Grand Total.
Here’s a concrete example: Suppose East sales = $100, West sales = $200, grand total = $300. Product A in East = $25.
- % of Grand Total for Product A = 25/300 = 8.33%
- % of Parent Row Total for Product A = 25/100 = 25%
Both are valid, but they answer different questions.
How to Choose the Right Percentage Option
Ask yourself: what is the denominator you want? If you want the share of the immediate group, use % of Parent Row Total. If you want the share of the overall total, use % of Grand Total. If you have multiple levels and want a specific level (e.g., product as a percentage of region, not of product category), use % of Parent Total and select the base field in the dialog box that appears.
For example, if you have Region > Category > Product, and you want each product as a percentage of its region (not its category), choose % of Parent Total and set the Base Field to Region.
Frequently Asked Questions
Why does my pivot table show “% of Parent Row Total” as a decimal like 0.25?
Excel stores the calculation as a decimal, but the display format may be General. To show it as a percentage, right-click the value cells, choose Number Format, and select Percentage. You can also set the number format in the Field Settings dialog under “Number Format.”
Can I use % of Subtotal in a pivot table with only one row field?
Yes, but it will behave like % of Grand Total because the parent row is the grand total. To have a meaningful subtotal, you need at least two row fields (or one row and one column field).
How do I get the subtotal values to show as well as the percentages?
You can add the same field to the Values area twice. Once, leave it as Sum. The second time, right-click and choose Show Values As > % of Parent Row Total. Then you’ll see both the raw numbers and the percentages side by side.
Does this work in Google Sheets?
Google Sheets has a similar feature. In the pivot table editor, under “Values,” click the dropdown next to the value field, choose “Show as,” and select “% of parent row” or “% of parent column.” The options are slightly different, but the concept is the same.
What does “% of Parent Total” do that “% of Parent Row Total” doesn’t?
“% of Parent Total” lets you choose which parent level to use. For example, with three row fields, you can specify that the percentage is based on the top-level field, not the immediate parent. “% of Parent Row Total” always uses the immediate parent, which may not be what you want if you have more than two levels.
Why is my percentage column showing #DIV/0!?
This error appears when the denominator (the subtotal) is zero. Check if any group has a zero total. If so, you can hide the error by using a custom number format like 0.00%;;; or by wrapping the calculation in an IFERROR, but pivot tables don’t allow formulas directly in the Values area unless you use a calculated field.
Related
- Excel Transpose: How to Quickly Flip Data
- How to Rename a Cell in Excel: A Complete Guide
- Excel Replace Only in Selected Cells: 2 Ways
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Make a Chart in Excel Run From Right to LeftTo make an Excel chart read from right to left, select the axis you want to reverse, right-click…
- Excel Gantt Chart: Build, Format, and Manage a ScheduleYes. An Excel-based Gantt chart is a project schedule displayed as task rows and date columns, with colored…
- Excel Histogram by Date: Group Time into Useful BinsTo make an Excel histogram by date, select a column of real Excel dates, then choose Insert >…
- Google Sheets Chart Themes: How to Set and CustomizeIn Google Sheets, a chart’s appearance can come from the spreadsheet’s overall theme or from settings on the…
- Frequency Histogram in Excel: Create and Interpret OneTo make a frequency histogram in Excel for Microsoft 365 on Windows, select your numeric data, then choose…
- Pivot Chart Formatting Options in Excel 365 ExplainedIn Excel for Microsoft 365 on Windows, format a PivotChart with the contextual Chart Design and Format tabs:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.