在 Excel 数据透视表中将值显示为占总计的百分比

Tested in: The share figures below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click Show Values As in Excel this session.

This is a display option on a field you already have. It is not a calculated field. For the same math outside a pivot, use the percentage of total calculator.

Change Show Values As

In Excel 365

1. Click a number in the Values area, or open the field’s menu in the Values well.
2. Choose Value Field Settings.
3. Open the Show Values As tab (in some builds this is a drop-down on the same dialog).
4. Pick % of Grand Total, % of Column Total, or % of Row Total.
5. Click OK. Format as a percentage if the cells do not already show a % sign.

To keep the original sums visible, drag Revenue into Values a second time and set only the copy to % of Grand Total. You will see “Sum of Revenue” and “Sum of Revenue2” (rename the second to “Share”).

In Google Sheets

1. Click the pivot to open the editor.
2. In Values, open the field.
3. Set Show as to % of grand total, % of row, or % of column.

Check the shares against the sheet

On the eight-sale sample, Revenue sums to 24450, East is 9450, Widget is 19800, and Maya is 12600. In Google Sheets on 12 November 2026:

=9450/24450
=19800/24450
=12600/24450

Those returned 0.38650306748466257, 0.8098159509202454, and 0.5153374233128835. A pivot that shows % of Grand Total for those items should match once you display enough decimals. West is 1 − East, or 0.6134969325153374.

% of Column Total is useful when Dates or Products run across the top and you want each column to add to 100%. % of Row Total does the same across a row. % of Parent Total (Excel) and % of parent (Sheets, when available) are for hierarchical layouts — for example, a product’s share of its region, not of the workbook grand total.

If Values is still on Count, you will be looking at a share of row counts, not of money. Switch the summary to Sum first; the percentage option does not fix a count.

Rounding, filters, and differences from a calculated field

A share displayed as 39% has been rounded; the unrounded East figure is 38.650…%. Adding rounded percentages can miss 100% by a point. That is a display issue, not a sign that the pivot is wrong.

A filter changes the grand total the percentage is based on. If you filter out West, East becomes 100% of the remaining 9450, not 38.65% of 24450. Decide whether you want the share of everything or the share of the visible slice before you apply the filter.

Do not build % of total as a calculated field like =Revenue/GrandTotal. There is no GrandTotal field to divide by, and a calculated field will not automatically use the pivot’s grand total the way Show Values As does. Leave percentages to Show Values As, and use a calculated field for a commission or a difference you can write with the field names you already have.