如何在 Excel 資料透視表中新增計算欄位

Tested in: The 10% commission figures below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Fields, Items, & Sets in Excel this session.

If you do not have a pivot yet, start with How to Create a Pivot Table in Excel. This page is only about a field you compute from other pivot fields.

Add the field in Excel

1. Click any cell inside the pivot table.
2. Open the PivotTable Analyze tab (it may be labeled Options or Analyze in older Excel).
3. Choose Fields, Items, & Sets > Calculated Field.
4. In Name, type something like Commission.
5. In Formula, enter =Revenue*10% (or =Revenue*0.1). You can double-click a field in the list instead of typing the name.
6. Click Add, then OK.

The field shows up in the Values well. If it arrives as Count of Commission, switch it to Sum, as in pivot table shows count instead of sum.

On the eight-sale sample used elsewhere on this site, 10% of Revenue should follow the same cuts as Revenue itself. In Google Sheets on 12 November 2026:

=24450*0.1
=9450*0.1
=19800*0.1

Those returned 2445 (grand total), 945 (East), and 1980 (Widget). After you add the calculated field, the pivot’s Commission totals should match those figures. If they do not, the formula is using the wrong field, Values is still on Count, or a filter is hiding rows.

How the formula is written

Use field names, not cell addresses. =Revenue*0.1 is right; =F2*0.1 is not, because F2 is a worksheet cell, not a pivot field. Spaces in a field name need single quotes: ='Unit Price'*Quantity.

Excel calculates a calculated field on the aggregated totals, not on each source row and then summed. For =Revenue*0.1 that does not matter, because 10% of the sum is the sum of the 10%s. For a ratio such as =Profit/Revenue or =Revenue/Quantity, the field can be wrong: it will divide the summed profit by the summed revenue (which may be what you want) or, depending on the layout, not match a row-level margin you had in mind. When the math has to happen on every source row, add a helper column in the source (=G2/F2) and pivot that column.

You can also add a calculated field that subtracts two fields, for example =Revenue-Cost. Keep the result in Values as Sum, and check it against a sheet formula such as =SUM(F2:F9)-SUM(G2:G9).

Google Sheets, and when not to use a calculated field

Sheets pivot tables do not offer Calculated Field. Insert a column in the source:

=F2*0.1

fill it down, then add that column to Values as SUM. The same 2445 / 945 / 1980 checks apply.

A calculated field is the wrong tool for “show Revenue as a percent of the grand total.” That is a display option, covered in pivot table percentage of total. It is also the wrong tool when the source is in the data model: add a DAX measure there, not a calculated field.

If you change the source after adding the field, refresh the pivot. To edit or delete the field, go back to Calculated Field, pick it from the name list, and change the formula or click Delete.