Percent of Number in an Excel PivotTable: Show Values

How-to basics · Updated November 20, 2026

To show a PivotTable value as a percentage, right-click a number in the Values area and choose Show Values As > % of Grand Total. Choose % of Row Total or % of Column Total instead when you want each row or column to add up to 100%.

What a PivotTable percentage represents

Excel does not change the underlying source numbers when you display them as percentages. It calculates each displayed value in relation to a chosen total. For example, a sales amount of $120 can appear as 30% of a $400 grand total, while the source amount remains $120. This makes percentage views useful for comparing contribution or distribution without replacing the original measure.

The key decision is the denominator: the total against which Excel compares each value. A share of the grand total answers a different question from a share of the current row or column. Selecting the wrong option can produce perfectly valid percentages that tell the wrong story, so decide what you want each 100% to represent before changing the setting.

Choose the right percentage calculation

  • % of Grand Total: Each value is divided by the total of all values in the PivotTable. Use this to show each category’s contribution to the overall result.
  • % of Row Total: Values across each row are divided by that row’s total. Each complete row should add up to 100%. Use it to compare the mix of items within each row category.
  • % of Column Total: Values down each column are divided by that column’s total. Each complete column should add up to 100%. Use it to compare the composition of a column category.

For instance, if rows are regions and columns are product types, % of Row Total shows each product’s share of its region’s sales. % of Column Total shows each region’s share of sales for each product. % of Grand Total shows every region-and-product combination as a share of all sales. These are different views of the same data, not different source totals.

Set up the calculation in Excel

In Microsoft Excel for Microsoft 365 on Windows, first create or select a PivotTable that has a numeric field in its Values area. Then follow these steps:

  1. Click a number in the PivotTable’s Values area, not a label in the row or column headings.
  2. Right-click and choose Show Values As.
  3. Choose % of Grand Total, % of Row Total, or % of Column Total.

You can also open the field settings for more control. In the PivotTable Fields pane, open the menu beside the value field under Values and choose Value Field Settings. Select the Show Values As tab, choose the calculation, and select OK. The Mac version has similar field settings and context-menu options; use a right-click or Control-click depending on your mouse or trackpad setup. Excel for the web may present PivotTable commands differently, so use the desktop app if the calculation option is not available in your web interface.

To keep both the original amount and its percentage visible, add the same source field to the Values area a second time. Leave the first copy as a sum and apply Show Values As to the second. If Excel gives the copies confusing names, change the displayed custom name in Value Field Settings. This is often clearer than replacing the amount with a percentage.

Worked example: percentage of grand total

Suppose a source table contains four sales records: North has $120 and $80, while South has $150 and $50. The grand total is $400. In a PivotTable with region in Rows and sales in Values, the regional sums are North $200 and South $200. With % of Grand Total selected, each region displays 50%. If the PivotTable also includes product categories, each region-and-product cell displays its own share of the $400 total.

For a cell containing $120, the calculation is $120 divided by $400, or 30%. The displayed percent depends on the PivotTable’s current layout and its aggregation. If the value field is counting records rather than summing sales, the percentage is a share of record counts instead of a share of dollars. Check the value field’s summary method when the result seems unexpected.

If you are not using a PivotTable and have amounts in B2:B5, the formula in C2 to calculate B2’s share of that range is =B2/SUM($B$2:$B$5). With B2 equal to 120 and the four amounts totaling 400, the formula returns 0.3, which displays as 30% when C2 is formatted as a percentage. A worksheet formula can be useful for a fixed calculation, but it does not automatically follow PivotTable grouping and filtering in the same way as Show Values As.

Format percentages and keep the source values

Excel may display a percentage with several decimal places or as a whole number, depending on the cell format. To adjust it, select a percentage in the PivotTable and use Home > Number to apply Percentage format or increase and decrease decimal places. You can also right-click and choose Number Format from the value field settings so the format applies consistently to that PivotTable field after refresh. If you only format a few individual cells, later layout changes may not preserve the appearance as expected.

Percentage formatting changes how a decimal is displayed: 0.3 becomes 30%. It does not multiply the source value or change the percentage calculation. Likewise, typing a percent sign into a custom heading or changing the field name does not turn the underlying values into percentages. Use Show Values As for the calculation and number formatting for how the result looks.

For a report that needs both dollars and shares, keep two copies of the value field in Values, use a clear name such as Sales and % of Total, and format each copy appropriately. This helps readers distinguish the actual amount from its contribution and reduces the risk of someone treating a percentage as a currency figure.

How filters, subtotals, and refreshes affect results

A PivotTable percentage is calculated within the PivotTable’s current context. Filtering the report, selecting a slicer item, or changing row and column fields can change what appears and how the displayed distribution is organized. Check the denominator after applying a filter: a percentage of the grand total should be interpreted as a share of the total used by that PivotTable view, not automatically as a share of every record that exists in the source system.

Subtotals and grand totals can also make a report look as though the percentages add to more than 100% if you add detail rows and subtotal rows together. The detail items make up the relevant total; a subtotal repeats their combined contribution. Do not sum both levels as if they were separate categories. If an expected 100% is missing, confirm that you are comparing a complete set of rows or columns under the same denominator.

Refreshing the PivotTable updates its source data, but a percentage can change because the underlying amounts or records changed. If new source rows are not included, check that the PivotTable’s source range includes them, or use an Excel Table as the source so added rows are easier to include. After changing the source, refresh the PivotTable and verify that its totals match the data you intend to summarize.

Common problems and how to fix them

  • Show Values As is unavailable: Make sure you clicked a value cell and that a field is in the Values area. A text field may be summarized as a count, which can still be expressed as a percentage, but a label in Rows or Columns is not a value field.
  • The result is 100% for every item: You may have selected a calculation that compares each item with its own parent or a very narrow group. Check whether you need % of Grand Total, % of Row Total, or % of Column Total.
  • Percentages do not add to 100%: Confirm the denominator option and whether filters hide items. Also check that you are not including subtotals along with the detail values.
  • Numbers look like decimals: Apply Percentage number formatting. A value of 0.25 is 25%, not 0.25%, when displayed as a percentage.
  • The percentage is based on counts, not amounts: Open Value Field Settings and check whether the field summarizes by Sum, Count, or another method. Choose a suitable numeric field and summary.

PivotTable percentages versus formula calculations

Show Values As is usually the better choice when the report should respond to PivotTable filters, grouping, and layout changes. It keeps the percentage calculation attached to the value field and allows you to switch among common denominators without building separate formulas. A cell formula is more appropriate when you need a bespoke calculation, a fixed report layout, or a percentage based on a denominator outside the PivotTable.

Be cautious about writing formulas directly beside a PivotTable and filling them down: the PivotTable can expand or contract after refresh, and the formula references may no longer line up with the intended categories. If you use formulas, inspect them after changing the PivotTable layout. For standard shares of row, column, or grand totals, the built-in calculation is generally easier to maintain.

Frequently Asked Questions

How do I show a number as a percentage of the total in an Excel PivotTable? Right-click a value in the PivotTable, choose Show Values As > % of Grand Total, and apply Percentage number formatting if needed.

How do I make each PivotTable row add up to 100%? Right-click a value and choose Show Values As > % of Row Total. Each row is calculated against its own total, so a complete row of detail values adds to 100%.

Can I show the amount and its percentage at the same time? Yes. Add the same source field to Values twice. Keep one copy as the amount and set the other copy to the desired Show Values As calculation.

Why does my PivotTable percentage change when I filter it? The visible PivotTable context has changed. A filter can remove categories from view or change the group being compared, so review the chosen denominator and the filtered total before interpreting the result.

Can I use a formula instead of Show Values As? Yes. A formula such as =B2/SUM($B$2:$B$5) calculates a share for a worksheet range, but it may not adjust cleanly when a PivotTable changes shape. Use Show Values As for the standard PivotTable percentage options.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.