To show a percent of total in an Excel pivot table, add the value field a second time, then right-click it and choose Show Values As > % of Grand Total. This converts the duplicate field from raw numbers into percentages of the overall total, while the original field keeps its original values.
Why Percent of Total Matters in Pivot Tables
Raw counts and sums are hard to compare when categories have very different sizes. A percent of total normalizes each value so you can see relative share at a glance. For example, if your pivot table sums sales by region and the grand total is $1,000,000, a region with $250,000 shows as 25%. This makes it easy to spot which region drives the most revenue, which product category dominates, or which month has the largest share of annual sales.
Excel offers several percent variations beyond the grand total: % of Column Total, % of Row Total, % of Parent Row Total, and % of Parent Column Total. The one you choose depends on whether your pivot table has multiple row or column levels. For a simple single-level table, % of Grand Total is usually what people mean by “percent of total.”
How to Add a Percent of Total Field
Assume you have a pivot table with Product in Rows and Sales in Values. To show percent of total alongside the raw sum:
- Drag the Sales field into the Values area a second time. You now have two “Sum of Sales” fields.
- Right-click either of the new value cells (not the header) and choose Show Values As > % of Grand Total.
- The field header changes to “Sum of Sales1” by default. Rename it by clicking the header and typing “% of Total” (or similar).
On a Mac, the steps are identical: right-click the value cell, then go to Show Values As &b> % of Grand Total. In Excel for the web, right-click the value cell and select Show Values As > % of Grand Total from the context menu.
If your pivot table has multiple row fields (e.g., Region then Product), the % of Grand Total option still calculates each cell as a share of the entire table’s grand total. To see the share within a parent row (like each product’s share within its region), choose % of Parent Row Total instead.
Choosing the Right Percent Option
The “Show Values As” menu has many options, and picking the wrong one is a common source of confusion. Here’s a quick guide:
- % of Grand Total – each value divided by the table’s overall total. Use this for a single-level table or when you want each cell’s share of the entire dataset.
- % of Column Total – each value divided by its column’s total. Useful when columns represent categories and rows represent time periods.
- % of Row Total – each value divided by its row’s total. Useful when rows are categories and columns are time periods.
- % of Parent Row Total – each value divided by the total of its parent row (the row above it in a hierarchy). Essential for multi-level row fields.
- % of Parent Column Total – same idea but for hierarchical columns.
For most “percent of total” requests, % of Grand Total is the correct choice. If your pivot table has only one row field, the other options may produce identical results to the grand total, so don’t worry if the numbers look the same.
Common Problems and Fixes
Several issues can trip up users when working with percent of total:
- The percent column shows 100% for every row. This happens when you accidentally choose % of Row Total on a table with only one row per row. Switch to % of Grand Total.
- The percent field is blank. This often occurs when the pivot table has a filter that excludes all data for some rows, or when the field is set to a calculation that doesn’t apply (like % of Parent Row Total with no parent). Check your filters and try a different “Show Values As” option.
- The percent values don’t add up to 100%. If you have multiple row fields and use % of Grand Total, the sum of all leaf-level cells will be 100% only if you hide subtotals. With subtotals visible, the subtotal rows also display percentages, and the sum of all visible percentages exceeds 100%. Hide subtotals (right-click > Field Settings > Layout & Print > uncheck Subtotal) or only look at the leaf-level rows.
- Percentages are formatted as decimals (0.25 instead of 25%). Right-click a value cell, choose Number Format, and select Percentage with the desired decimal places.
Percent of Total vs. Other Calculations
Percent of total is just one of many “Show Values As” options. Others include Difference From, % Difference From, Running Total, and Rank. These are useful for different analytical questions:
- Running Total – shows cumulative sums over time (e.g., month-to-date sales).
- Difference From – compares each value to a base item (e.g., sales vs. previous year).
- Rank – assigns a rank to each value within a row or column.
Percent of total is best when you need to understand composition or market share. If you need to track growth over time, a difference or running total is more appropriate. You can also combine both by adding the same field multiple times with different “Show Values As” settings.
Using Formulas vs. Pivot Table Percentages
Some users prefer to calculate percent of total outside the pivot table using formulas like =B2/$B$10. This works, but it has drawbacks: if the pivot table changes size or order, the formula references can break or point to the wrong cells. The built-in “Show Values As” feature recalculates automatically and stays aligned with the pivot structure.
If you need a permanent, static percentage (for a report or chart), you can copy the pivot table and paste as values, then format as needed. But for ongoing analysis, using the pivot table’s native percent calculation is more robust and requires no manual formula maintenance.
Frequently Asked Questions
Why does my percent of total show 0%? This usually means the value field is set to a count rather than a sum, or the underlying data is text. Check that the field in Values is summarized as Sum (right-click > Value Field Settings > Summarize by > Sum). If the data is text, Excel counts it, and the percent may be small but not zero. If you see 0%, verify the source data has numeric values.
Can I show percent of total in a pivot chart? Yes. Create the pivot table with the percent field, then insert a pivot chart from it (Insert > PivotChart). The chart uses the percent values automatically. You can also change the chart type to a pie or bar to visualize shares.
How do I show percent of total for only a filtered subset? If you apply a report filter (e.g., only show Q1), the grand total recalculates to the filtered data, so the percent becomes a share of that subset. To always show percent of the entire unfiltered dataset, you would need to use a formula outside the pivot table or use the GETPIVOTDATA function.
What’s the difference between % of Grand Total and % of Column Total? In a table with multiple columns (e.g., years), % of Grand Total divides each cell by the overall table total, while % of Column Total divides each cell by its column’s total. If you have only one column, they are identical.
Can I have both raw numbers and percentages in the same pivot table? Absolutely. Add the value field twice and set one to “Sum” and the other to “% of Grand Total.” This is the most common setup for reports that need both absolute and relative views.
Quick Recap
To show percent of total in a pivot table:
- Add the value field a second time to the Values area.
- Right-click the new field and choose Show Values As > % of Grand Total.
- Rename the field header to something clear like “% of Total”.
- Format the numbers as percentages if needed.
This method works in Excel 2016, 2019, 2021, and Microsoft 365 on Windows, Mac, and the web. The same steps apply to Google Sheets, where the option is called “% of grand total” under the “Show as” menu in the pivot table editor.
Related
- Excel Subtotals: How to Add Them Step by Step
- Excel Hourly Schedule Template: How to Make One
- Excel: How to Add Lines in a Cell (3 Easy Ways)
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Tables in Microsoft Excel: How to Create and UseA PivotTable in Microsoft Excel summarizes and reorganizes a larger data set so you can compare totals, counts,…
- Excel Pivot Tables for BI: Uses, Limits, and AlternativesYes. Excel PivotTables can serve as a practical, entry-level business intelligence (BI) tool: they summarize data, reveal patterns,…
- Microsoft Power Pivot: What It Is and How It WorksMicrosoft Power Pivot is an Excel feature for building data models from multiple tables and analyzing them with…
- Graph the Line y = x – 5: Slope, Intercepts, and StepsAssuming you mean y = x – 5, graph a straight line with slope 1 and y-intercept -5.…
- Excel Pivot Table Course: What to Learn and How to ChooseAn Excel pivot table course teaches you to summarize, compare, and explore spreadsheet data without writing formulas for…
- Excel Sparklines Greyed Out? Causes, Fixes, and LimitsIf Excel’s Sparkline commands are greyed out, first exit cell-edit mode, select a normal worksheet cell, and check…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.