Calculated Fields

Every field in the PivotTable Fields panel comes directly from a column in the source data. A calculated field is different — it is a new metric you define inside the pivot table itself, built by combining existing fields with a formula. It has no column in the source data and never modifies it. It exists only in the analysis layer, behaves like any other value field, and recalculates automatically as the pivot table layout changes.

Creating the Commission calculated field:

1. Set up the pivot table with Salesperson in Rows and Revenue in Values. This gives you total revenue per salesperson as the starting point.
2. Click anywhere inside the pivot table. Go to PivotTable Analyze tab → Fields, Items & Sets → Calculated Field.
3. In the dialog, type a name in the Name field — e.g. Commission. Clear the Formula field, then click Revenue in the fields list and click Insert Field. It appears in the formula as =Revenue.
4. Complete the formula by typing *0.05 after the field name. The formula should read =Revenue*0.05. Click OK.
5. A new Sum of Commission column appears in the pivot table. If values are blank, go to PivotTable Analyze → Refresh. The field now appears in the Fields panel alongside the original fields.

When you rearrange the pivot table — changing Rows from Salesperson to Category, or adding Store to Columns — the calculated field recalculates automatically to match the new view. The formula stays the same; the numbers change to reflect whatever grouping is currently active.

Editing and Removing Calculated Fields

1. Rename or change the formula

   Open the Calculated Field dialog, select the field from the Name dropdown, edit the name or formula, then click Modify (not OK — clicking OK creates a duplicate). The change applies to every pivot table sharing the same cache.
   
   

   PivotTable Analyze → Fields, Items & Sets → Calculated Field → select field → edit → Modify

2. Shared cache warning

   If other pivot tables in the same workbook use the same source data, they share a pivot cache. Modifying a calculated field here changes it everywhere. If you need different versions of the same metric, build them in separate workbooks or rename them distinctly.

3. Delete a calculated field

   Select the field from the Name dropdown and click Delete. The field disappears from the pivot table and the Fields panel. The source data is completely unaffected — only the derived metric is removed from the analysis layer.
   
   PivotTable Analyze → Fields, Items & Sets → Calculated Field → select field → Delete