Excel Pivot Table Percentage of Total Subtotal

This article explains how to show a percentage of total for a subtotal row or column inside an Excel pivot table, and what to do when the default % of Total doesn’t behave as expected. You’ll learn the exact steps for both classic and newer Excel versions, how to fix common pitfalls like grand total percentages, and how to make the percentage show for subtotals only.

Formula

The percentage of total for a subtotal is calculated as:

Subtotal percentage = (Subtotal value / Grand total value) × 100

Where:

• Subtotal value is the sum of the field for a specific group (e.g., a product category).
• Grand total value is the sum of the entire field (all groups combined).

In Excel, this is the same as the “% of Grand Total� calculation, but you can apply it specifically to subtotal rows by using the “Show Values As� option on the subtotal cell.

Worked Example

Suppose you have a pivot table with sales by region, and you want to know what percentage each region’s subtotal contributes to the overall total.

1. Your pivot table has rows: North, South, East, West. Each region has a subtotal at the bottom of its group.
2. The grand total (all regions) is $100,000.
3. The North region subtotal is $25,000.
4. To get the percentage for North, you compute: (25,000 / 100,000) × 100 = 25%.

Excel does this automatically when you apply “% of Grand Total� to the subtotal cell.

How to Show Percentage of Total for a Subtotal

Follow these steps to display the percentage of total for a subtotal row or column:

1. Click on the subtotal cell in your pivot table (for example, the cell that shows the total for a specific group).
2. Right-click and choose Show Values As > % of Grand Total.
3. The subtotal cell now shows the percentage of the grand total.
4. If you want to apply this to all subtotals at once, select all subtotal cells (hold Ctrl and click each) before applying the setting.

Alternatively, you can drag the field you want to calculate into the Values area again and set that new field to “% of Grand Total� – then use it in your subtotal row.

Common Problem: Percentage Shows for Every Cell, Not Just Subtotal

When you apply “% of Grand Total� to a value field, it changes every cell in that field, not just the subtotal. This is a frequent frustration. To limit the percentage to subtotals only, you have two options:

• Use a separate value field: Add the same field to the Values area a second time, set that new field to “% of Grand Totalâ€�, and then hide the original field’s subtotals or use the new field only for the subtotal row.
• Use PivotTable Design options: In some Excel versions, you can right-click the subtotal cell and choose “Field Settingsâ€� > “Show Values Asâ€� – this applies only to the selected cell, but it may not persist if the pivot refreshes.

For a reliable result, the separate field method is recommended.

How to Show Percentage of a Subtotal (Not Grand Total)

Sometimes you want each row to be a percentage of its own subtotal (e.g., each product’s share within its category). That is a different calculation: “% of Parent Row Total�.

1. Click on the value field in the Values area.
2. Choose Show Values As > % of Parent Row Total.
3. In the dialog that appears, select the parent field (e.g., the category field).

This shows each row as a percentage of the subtotal for its group, not the grand total.

Using Calculated Fields for Custom Percentage

If you need a percentage that isn’t directly available (e.g., percentage of a subtotal that is itself a calculated value), you can create a calculated field:

1. Click anywhere in the pivot table.
2. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
3. Name it (e.g., “% of Totalâ€�) and enter a formula like =Sales/TotalSales, but note that calculated fields cannot directly reference the grand total – you’ll need to use a GETPIVOTDATA formula outside the pivot for that.

For most cases, the built-in “Show Values As� options are simpler and more accurate.

Frequently Asked Questions

Why does my percentage show as a decimal like 0.25 instead of 25%?

Excel stores percentages as decimal fractions. To display as a percentage, select the cells, right-click, choose Format Cells, and under Number, select Percentage with the desired decimal places.

Can I show percentage of total for a column subtotal?

Yes, the same steps apply to column subtotals. Click the column subtotal cell and set “Show Values As� to “% of Grand Total�.

How do I show percentage of a subtotal for multiple levels (e.g., region then product)?

Use “% of Parent Row Total� and select the parent field that represents the level you want to compare against. For example, to show each product as a percentage of its region’s subtotal, select the region field as the parent.

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

“% of Grand Total� divides each value by the overall total of all rows and columns. “% of Column Total� divides each value by the total of its column. For subtotals, “% of Grand Total� is usually what you want.

Why does my percentage not update when I refresh the pivot table?

If you used a separate value field with “Show Values As�, it should update automatically. If you manually formatted a cell, it may not. Ensure the field is set correctly and not overwritten by formatting.

Can I show percentage of total without a grand total row?

Yes, you can turn off the grand total in the pivot table design, but then the “% of Grand Total� calculation has no denominator. In that case, you’ll need to use a calculated field or a helper column with the total from outside the pivot.

Limitations and Alternatives

The built-in “Show Values As� options are powerful but have limitations. They always calculate based on the visible data in the pivot, so filters affect the percentages. If you need a static percentage that doesn’t change with filters, consider using a formula outside the pivot table with GETPIVOTDATA. Also, for very large datasets, pivot tables can slow down; using Power Pivot might offer more flexibility.

In summary, showing a percentage of total for a subtotal is straightforward once you know which “Show Values As� option to use and how to apply it only to the subtotal cells. The key is to decide whether you want each row as a percentage of the grand total or of its own subtotal, and then choose the appropriate setting.

Related

• NASA Mini Payload Rovers: Design & Build Guide
• Force Reader Mode Every Webpage? How To
• Vtuber Agency vs Independent: Which Is Better?

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2