To calculate percentages in an Excel PivotTable, right-click a value, choose Show Values As, and select % of Grand Total, % of Column Total, or % of Row Total. Excel calculates each displayed value’s share automatically; choose the option that matches the comparison you want to make.
What a PivotTable percentage means
A PivotTable percentage expresses a value as a share of a larger total. For example, if a sales PivotTable shows $120 for the North region and $250 in total sales, North represents 48% of the total. The underlying sales amounts remain unchanged: the percentage is a different way to display the summarized values.
The key is deciding what should count as the whole. A share of all sales is different from a share of one region’s sales or one product’s sales. Excel provides several Show Values As calculations so you can choose the relevant denominator without writing a worksheet formula.
- % of Grand Total: Each value divided by the total of all values in the PivotTable.
- % of Row Total: Each value divided by the total of its row.
- % of Column Total: Each value divided by the total of its column.
- % Of: Each value divided by a selected base item, when the PivotTable’s layout supports that comparison.
Prepare the PivotTable
These steps apply to Excel for Microsoft 365 on Windows. Start with a tidy source table: each column should have a clear heading, each row should represent one record, and the measure you want to summarize should contain numeric values. Avoid blank header cells and merged cells in the source data because they can interfere with creating or refreshing a PivotTable.
- Select a cell in the source data, or select the complete data range.
- Choose Insert > PivotTable, confirm the table or range, select where to place the PivotTable, and choose OK.
- In the PivotTable Fields pane, drag a category such as Region into Rows, another category such as Product into Columns if useful, and a numeric field such as Sales into Values.
- Check that the value field is summarized as intended. For example, use Sum for sales amounts rather than Count.
For example, suppose the source has sales records for North, South, and East, with summed sales of 120, 80, and 50. The PivotTable’s grand total is 250. Once the Sales field is in Values, you can display those sums as percentages while keeping the region labels and other PivotTable structure intact.
Show values as a percentage of the grand total
Use this calculation when the question is, “What share of all sales came from each category?” In the PivotTable, right-click one of the numbers in the value field and choose Show Values As > % of Grand Total. You can also open Value Field Settings, select the Show Values As tab, and choose % of Grand Total.
With the example totals, North is 120 ÷ 250 = 48%, South is 80 ÷ 250 = 32%, and East is 50 ÷ 250 = 20%. The percentages add up to 100% when all categories are included and there are no excluded values affecting the total. If you filter out a region, Excel’s grand total and percentages may recalculate based on the items that remain visible, depending on the PivotTable’s settings and source.
This view is useful for showing each department’s share of company-wide costs, each product’s share of orders, or each channel’s share of total website visits. It is less useful if your real question concerns a separate total for each row or column; use the corresponding row or column option for that.
Calculate percentages by row or column
Use % of Row Total when items across a row should add to 100%. For example, if rows contain regions and columns contain product categories, this shows each product’s share of the sales in its region. Use % of Column Total when the items down each column should add to 100%. In the same layout, that shows each region’s share of sales for a product category.
To apply either calculation, right-click a number in the appropriate value field and select Show Values As > % of Row Total or Show Values As > % of Column Total. Alternatively, open Value Field Settings > Show Values As and select the option there. Look at the PivotTable’s layout before choosing: rows and columns determine which grouping is treated as the whole.
A quick check is to scan the relevant totals. With % of Row Total, the percentages across a complete row should generally total 100%; with % of Column Total, the percentages down a complete column should generally total 100%. Filters, hidden items, blanks, or other PivotTable settings can affect what is included, so confirm which records are represented before interpreting the result.
Show a share of a parent group
Some PivotTables have nested categories, such as Year, Quarter, and Month, or Category and Subcategory. For a hierarchy like this, a percentage of a parent total can answer questions such as, “What share of this category’s sales came from each subcategory?” In Value Field Settings > Show Values As, look for an option such as % of Parent Row Total or % of Parent Column Total, where available.
These options depend on the field arrangement and data source. If the relevant parent-percentage choice is not available, check that the PivotTable contains the parent and child fields in the appropriate row or column hierarchy. A standard flat layout may not provide a meaningful parent comparison. You can also rearrange the fields or use a regular percentage formula outside the PivotTable when the required denominator is not represented by its structure.
Compare percentages with a previous period
A percentage share is not the same as a percentage change. If you want to compare this month with the previous month, use % Difference From in the Show Values As options. Select the time field as the base field and the previous period as the base item, if those choices are available in the PivotTable.
For instance, sales rising from 100 to 120 represent a 20% increase, because the difference of 20 is divided by the earlier value of 100. A share-of-total calculation instead asks what fraction of a selected total a value represents. Make sure you choose a comparison that answers the question: use % Difference From for change over time, and a percentage-of-total option for contribution to a total.
Format the result and keep the source unchanged
After selecting a percentage calculation, check how the result is displayed. If it appears as 0.48 rather than 48%, select the PivotTable values and choose Home > Number > Percent Style. Use the decimal increase or decrease controls in the Number group if you need more or fewer decimal places. Percentage formatting changes how a number is displayed; it does not decide whether the calculation is a share of a row, column, or grand total.
It is possible to add the same source field to Values more than once. For example, keep one Sales field as the original summed amount and set a second Sales field to % of Grand Total. This lets readers see both the amount and its contribution side by side. Give the value fields clear custom captions, such as “Sales” and “Share of total,” so the two calculations are easy to distinguish.
In Microsoft 365 for Mac, the PivotTable tools and field settings provide comparable percentage calculations, although ribbon placement and labels can vary by version. Excel for the web supports many PivotTable tasks, but available controls can differ; if a particular Show Values As option is missing, open the workbook in the desktop Excel app to use the full set of controls.
Common problems and how to fix them
- Every number shows as 100%: The selected calculation may be using a total that is the same as each displayed value, or the PivotTable may have only one item in that group. Check the row and column fields and try the grand-total option if you want a share of the entire report.
- The values are counts instead of amounts: Open Value Field Settings and select Sum under Summarize Values By. If Sum is unavailable or Excel keeps counting, check the source column for text-formatted numbers or nonnumeric entries.
- The percentages do not add to 100%: Confirm whether you chose grand total, row total, or column total, and whether filters or hidden items exclude data. A partial selection is not necessarily expected to total 100%.
- The result does not change after editing source data: Refresh the PivotTable using PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. Also confirm that newly added source rows are within the PivotTable’s source range; an Excel Table used as the source can make later expansion easier to manage.
- Percentages look unexpectedly tiny or large: Check the base total and the summarized field. A percentage of a column total may differ greatly from a percentage of the grand total, even though both calculations are working correctly.
When a worksheet formula is a better choice
PivotTable percentages are convenient when the denominator is one of the totals represented by the PivotTable. A worksheet formula can be more suitable when you need a custom denominator, a fixed comparison total, or a percentage calculation outside the PivotTable. For example, with the region totals in cells B2:B4—120, 80, and 50—and the percentage result in C2, enter this formula and fill it down through C4:
=B2/SUM($B$2:$B$4)
The formula returns 48% for 120, 32% for 80, and 20% for 50 when the result cells are formatted as percentages. The arithmetic is 120 ÷ 250, 80 ÷ 250, and 50 ÷ 250. The dollar signs keep the total range fixed as the formula is filled down. Unlike a PivotTable calculation, this formula will not automatically summarize new categories; the source range and formula may need to be extended as data changes.
Frequently Asked Questions
How do I show a PivotTable total as a percentage?
Right-click a value and choose Show Values As > % of Grand Total. If you want percentages within each row or column instead, choose the matching row-total or column-total option.
How do I show both the amount and percentage?
Add the same numeric field to the Values area twice. Leave one copy as the amount, then set the second copy to the desired percentage using Value Field Settings > Show Values As.
Why does Excel display a decimal instead of a percent sign?
The underlying result may be correct but formatted as a decimal. Select the values and choose Home > Number > Percent Style; adjust decimal places if needed.
Can I calculate a percentage difference between two years?
Yes, if the years are represented in the PivotTable, choose % Difference From in Show Values As, then select the year field and the comparison year as the base. This calculates change relative to the selected base item, not share of the grand total.
Do PivotTable percentages change when I filter?
They can. Filtering changes which items are shown and can change the total used as the denominator. Check the selected percentage calculation and filter state whenever a result appears different from an earlier view.
Related
- Blank Checklist Template Excel: Create One That Works
- Bloomberg Excel Add-In Repair and Troubleshooting
- Bill Paying Checklist Excel Template: Build One That Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Custom Number Format for Negative Numbers in BracketsYes. In Excel for Microsoft 365 on Windows, use a custom number format with a semicolon separating positive…
- Outlook Browser Signature: How to Create One Step by StepTo create a signature in Outlook in your browser, open Settings, go to the signature settings, enter and…
- Google Sheets: How to Merge Columns Without Losing DataTo combine values from columns, put the result in a new column: with a first name in A2…
- Excel Page Break Dotted Lines: How to Show Them AgainTo show page-break dotted lines in Excel for Microsoft 365 on Windows, choose File > Options > Advanced,…
- Outlook Signature: How to Create and Set It Up for EmailTo create a signature message in Outlook, open the signature settings for your version, add your name and…
- Excel Conditional Formatting: Highlight Entire RowsTo highlight an entire row in Excel, select the data range, choose Home > Conditional Formatting > New…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.