Calculate Field Pivot Table: Step-by-Step Guide

Charts & pivot tables · Updated November 22, 2026

To calculate a field in a pivot table, add a calculated field via PivotTable Analyze > Fields, Items & Sets > Calculated Field, enter a name and formula, then click Add and OK. This creates a new field that performs custom calculations on your pivot data, such as summing two existing fields or applying a percentage.

What Is a Calculated Field in a Pivot Table?

A calculated field is a custom column you add to a pivot table that uses a formula based on other fields in the same pivot table. Unlike a calculated item (which operates within a single field), a calculated field works across fields. For example, if you have Sales and Cost fields, you can create a calculated field named Profit with the formula =Sales - Cost. The pivot table then displays Profit for each row and column combination, and it automatically updates when you refresh the data.

Calculated fields are stored with the pivot table, not in the source data. They appear in the PivotTable Field List under the “Values” area, and you can format them like any other value field.

How to Add a Calculated Field (Step-by-Step)

These steps apply to Excel for Microsoft 365 on Windows. On Mac, the menu is under PivotTable Analyze > Fields, Items & Sets as well, but the dialog looks slightly different.

  1. Click anywhere inside your pivot table to activate the PivotTable Analyze tab (on Mac: PivotTable Analyze).
  2. Go to Fields, Items & Sets > Calculated Field.
  3. In the Insert Calculated Field dialog, type a name in the Name box (e.g., “Profit”).
  4. In the Formula box, type your formula. You can double-click fields from the list to insert them, or type them directly. Use standard Excel operators and functions. For example: =Sales - Cost.
  5. Click Add, then OK.

The new field appears in the PivotTable Field List under “Values” and is added to the Values area automatically. You can drag it to other areas if needed.

Example with Real Numbers

Suppose your source data has columns: Region (A), Sales (B), Cost (C). You create a calculated field named Profit with formula =Sales - Cost. If a row shows Sales=1000 and Cost=600, Profit shows 400. If you also want a margin percentage, create another calculated field: = (Sales - Cost) / Sales and format it as a percentage. The pivot table will compute this for every row and column, including totals.

Another common use: a calculated field to show average order value: =Sales / Orders (assuming Orders is a numeric field).

Choosing Between Calculated Field and Other Methods

Calculated fields are not always the best choice. Consider these alternatives:

  • Add a column to your source data: If the calculation is simple and you don’t need to change it frequently, add a column in the source table (e.g., Profit = Sales – Cost) and refresh the pivot. This is faster and more flexible for complex formulas.
  • Use a measure in Power Pivot: If you’re using Power Pivot (Data Model), DAX measures are more powerful and handle context better than calculated fields. Calculated fields in a regular pivot table cannot reference other calculated fields or use functions like SUMIFS.
  • Use a slicer or grouping: If you just want to filter or group, don’t use a calculated field.

Calculated fields are best for quick, simple calculations that don’t require referencing individual cells or other calculated fields.

Common Problems and Fixes

Here are typical issues users encounter:

  • Formula uses cell references: Calculated fields cannot use cell references like =B2*C2; they must use field names. Use =Sales*Cost instead.
  • Field name has spaces: If a field name contains spaces, enclose it in single quotes, e.g., = 'Unit Price' * Quantity.
  • Calculated field shows wrong totals: Pivot tables calculate totals based on the sum of the underlying data, not the sum of the calculated field’s results. For example, a Profit field will total correctly because Profit sums, but a ratio field like Margin will show a total that is the sum of margins, not the overall margin. To display a correct overall ratio, use a different approach, such as adding a column to source data or using a measure.
  • Cannot reference other calculated fields: You cannot use a calculated field in another calculated field’s formula. Workaround: create the calculation in the source data or use a measure.

How to Edit or Delete a Calculated Field

To modify a calculated field, go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. Select the field name from the list, edit the formula, and click Modify. To delete, select the field and click Delete. Note that deleting a calculated field removes it from the pivot table entirely; you cannot undo with Ctrl+Z after closing the dialog, so make sure you want to remove it.

Frequently Asked Questions

Can I use a calculated field in a pivot chart? Yes, any calculated field added to the pivot table appears in pivot charts as a series, just like any other value field.

Why does my calculated field show #DIV/0!? This happens when the denominator is zero or blank. Use an IFERROR function in the formula, e.g., =IFERROR(Sales/Cost,0).

Can I use SUM or other functions inside a calculated field? Yes, but be careful: calculated fields implicitly aggregate. For example, =SUM(Sales)/SUM(Cost) works, but =Sales/Cost is evaluated for each row and then summed. Use functions when you need a specific aggregation.

Does a calculated field work in Google Sheets pivot tables? No, Google Sheets does not have a built-in calculated field feature. You can add a calculated column to your source data or use a query formula.

How do I format a calculated field as currency? Right-click the field in the Values area, choose Value Field Settings, then Number Format, and select Currency.

Alternatives and Advanced Options

If calculated fields are too limiting, consider these:

  • Power Pivot measures: Use DAX to create complex calculations that respect filter context. For example, =SUM(Sales)-SUM(Cost) as a measure.
  • Get & Transform (Power Query): Add a custom column during data import. This is useful for complex transformations.
  • Regular formulas outside the pivot table: Use GETPIVOTDATA or reference pivot cells for custom reporting.

Remember to save a copy of your workbook before making significant changes, especially if you use VBA to automate calculated fields, because macros cannot be undone with Ctrl+Z.

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.