Excel Pivot Table Percentage of Total: How to Show It

Charts & pivot tables · Updated November 18, 2026

To show Excel PivotTable values as a percentage, right-click a value in the PivotTable and choose Show Values As, then select % of Grand Total, % of Row Total, or % of Column Total. Excel recalculates the displayed values against the total you choose, so the first decision is which total answers your question.

What a PivotTable percentage means

A PivotTable percentage is a share of a summarized value, not necessarily a percentage calculated from each original source row. Excel first aggregates the records in each PivotTable cell, then divides that cell’s result by a chosen total. For example, if a cell summarizes 250 in sales and the grand total is 1,000, its percentage of the grand total is 25%.

The denominator changes with the Show Values As option. If the denominator is the whole PivotTable, the result answers how much each cell contributes to the whole. If it is a row or column total, the result answers how the value is distributed within that row or column. Percentages can therefore look very different even though the underlying sales values have not changed.

This setting changes how a value field is displayed; it does not replace the source data or rewrite the underlying sales figures. You can usually still see the summarized amount by selecting a different display option, or by adding the same source field to the Values area a second time and giving the two copies different settings.

Choose the right percentage denominator

Consider a PivotTable with product categories in rows, regions in columns, and Sum of Sales in Values. Imagine the grand total is $10,000. The North region contributes $4,000, and the Furniture category contributes $2,000. The Furniture–North cell contains $800.

  • % of Grand Total: $800 divided by $10,000 is 8%. This tells you the cell’s share of all sales in the PivotTable.
  • % of Row Total: $800 divided by the Furniture row total of $2,000 is 40%. This tells you how much of Furniture sales came from North.
  • % of Column Total: $800 divided by the North column total of $4,000 is 20%. This tells you how much of North sales came from Furniture.

Use grand-total percentages to show overall contribution, row percentages to compare the mix within each row category, and column percentages to compare the mix within each column category. State the denominator in a report or chart title when readers might otherwise mistake one type for another.

For nested row or column fields, Excel also offers parent-total options. These compare a value with the total for its parent item in a hierarchy, such as a city’s sales as a share of its state’s sales. The exact parent option depends on the fields and layout in the PivotTable. If Excel asks for a base field, choose the field whose parent-child relationship you want to use.

Show a percentage of the grand total

These steps apply to Excel for Microsoft 365 on Windows. First build a PivotTable with a numeric field in the Values area, then follow this sequence:

  1. Click any number in the value field you want to convert.
  2. Right-click and choose Show Values As.
  3. Select % of Grand Total.
  4. Check the result against the displayed grand total. The values should add to 100% when the PivotTable includes all the relevant items and the grand total is shown.

You can also open Value Field Settings from the value field’s drop-down menu in the PivotTable Fields pane. In the dialog, select the Show Values As tab and choose the calculation. This route is useful when you want to inspect the value field’s summary calculation and display calculation together. The menu labels can vary slightly by Excel version and platform; on Mac, click or Control-click a value and look for the equivalent Show Values As command or open Value Field Settings.

To make the result look like a percentage, open Value Field Settings, choose Number Format, select Percentage, and set the decimal places you want. Applying the format through the value field settings is generally more reliable than formatting individual cells, because a PivotTable refresh or layout change can otherwise remove direct cell formatting. Use one or two decimal places when small differences matter; use fewer when a clean summary is more important.

Display row or column percentages

To calculate each value as a share of its row, right-click a value and choose Show Values As > % of Row Total. To calculate each value as a share of its column, choose Show Values As > % of Column Total. If the command is not visible in the context menu, use Value Field Settings and select the same option on the Show Values As tab.

Check the layout before interpreting the result. In a PivotTable with categories down the rows and months across the columns, % of Row Total shows the monthly distribution for each category. Each category row should total 100%, subject to rounding and any items excluded from the PivotTable. % of Column Total instead shows the category mix within each month, so each month column should total 100%.

A common reporting mistake is to choose % of Row Total simply because categories appear in rows. The option describes the denominator, not a universal instruction to divide by the visible row. Ask what comparison the reader needs: contribution to the full dataset, composition within each category, or composition within each period. Then choose the denominator that matches.

Keep the amount and percentage side by side

A percentage is easier to interpret when readers can also see the underlying amount. In the PivotTable Fields pane, drag the same numeric source field into the Values area a second time. Keep the first instance as Sum of Sales, then set the second instance to % of Grand Total or another percentage calculation. Rename the value fields to clear labels such as Sales and % of Total.

For instance, the two fields might show $800 and 8% for the Furniture–North cell in the example above. The amount answers how much was sold; the percentage answers how large that amount is relative to the selected denominator. When making the second copy, confirm that its summary remains Sum if the source field contains sales amounts. Excel may choose Count instead if it encounters text or blanks in the source column.

To change a value field’s caption, open Value Field Settings and edit the Custom Name. Avoid using a name identical to a source field if Excel says the name already exists; a descriptive variation such as % of Sales Total is clearer anyway. Adding a second copy does not duplicate source records. It gives the PivotTable two views of the same summarized field.

How filters and refreshes affect the result

Percentage-of-total calculations use the items currently included in the PivotTable. If you filter out a region, product, or date range, Excel recalculates the displayed percentages using the remaining data. A percentage that was 8% before a filter might become 10% afterward even though the underlying cell amount is unchanged. Slicers and report filters can have the same effect.

This behavior is often useful: the percentage describes the selected subset. But it can surprise readers who expect a share of the original, unfiltered dataset. Label the report clearly, and check active filters before comparing percentages across PivotTables. Two tables with different filters can have percentages that cannot be compared directly.

If source records have changed, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. The percentage calculation updates from the refreshed summaries. Refreshing does not necessarily expand the source range to include newly added rows if the PivotTable was built from a fixed range; an Excel Table is usually a convenient source because it expands as new records are added. After changing the source, verify the PivotTable’s data source and refresh it.

Common problems and what to check

  • The values show decimals instead of percent signs: Set the value field’s Number Format to Percentage in Value Field Settings. A decimal such as 0.25 represents 25%, not 0.25%.
  • The percentages do not add to 100%: Check whether the PivotTable contains hidden or filtered items, whether you chose the intended denominator, and whether displayed rounding accounts for a small difference. For a grand-total calculation, compare the included cells with the grand total.
  • Excel counts records instead of adding amounts: Open Value Field Settings and choose Sum. If Sum is unavailable or the result is unexpected, inspect the source column for text-formatted numbers, labels, or other nonnumeric entries.
  • The percentage changes after using a slicer: This is expected when the filter changes the included data and therefore the denominator. Make clear whether the percentage refers to the current selection or a fixed overall total.
  • Some categories are missing: Check the field filters and source data. A PivotTable cannot calculate a share for records it does not include, and a refresh may be needed after source changes.

Rounding deserves particular attention. If five displayed percentages each round to one decimal place, their visible sum may be 99.9% or 100.1% even when the unrounded values total exactly 100%. More decimal places can clarify the arithmetic, but displaying excessive precision can make a report harder to read.

Percentages versus calculated fields and formulas

Show Values As is usually the simplest choice when the goal is to express a PivotTable summary as a share of a row, column, grand total, or parent total. It is designed for this kind of comparison and adjusts as the PivotTable layout and included items change.

A calculated field is different: it defines a calculation based on source fields, rather than dividing a summarized PivotTable cell by a chosen PivotTable total. It may be appropriate for a measure such as profit calculated from sales and cost, but it is not the same as % of Grand Total. For a custom percentage whose denominator does not match Excel’s built-in options, consider a measure in the Data Model or a separate worksheet calculation, and document how its denominator is defined.

For a small, fixed report, a worksheet formula outside the PivotTable can be practical. However, a formula that points to a particular cell may break or refer to a different item after someone filters, sorts, or rearranges the PivotTable. If you use formulas beside a PivotTable, test them after refreshes and layout changes. For a reusable PivotTable report, built-in Show Values As settings are often easier to maintain.

Frequently Asked Questions

How do I show a PivotTable as a percentage of the grand total?
Right-click a value in the PivotTable, choose Show Values As > % of Grand Total, then set the value field’s Number Format to Percentage if needed.

How do I make each row add up to 100%?
Right-click a value and select Show Values As > % of Row Total. Each row is divided by its own total, so the percentages across that row should add to 100%, subject to rounding and filters.

How do I show a percentage and the original amount together?
Add the same source field to the Values area twice. Leave one instance as the amount and set the other instance to the percentage calculation you need.

Why does a PivotTable percentage change when I filter it?
The selected Show Values As calculation usually uses the items currently included in the PivotTable. A filter changes the group or grand total used as the denominator, so the percentage can change even when a particular amount does not.

Can a PivotTable show percentages of a parent category?
Yes. In Value Field Settings, choose an available parent-total option under Show Values As and select the appropriate base field if Excel requests one. This is useful for nested fields, such as a city’s share of its state’s total.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.