Excel Pivot Table Calculated Field: How to Change It

Charts & pivot tables · Updated November 19, 2026

To change an existing Excel PivotTable calculated field in Microsoft 365 for Windows, click inside the PivotTable, choose PivotTable Analyze > Fields, Items & Sets > Calculated Field, select the field under Name, edit its formula, and click Modify. The PivotTable recalculates using the revised formula; if you meant to change a source-data formula or a Data Model measure, use a different method.

What a calculated field does

A calculated field adds a calculation to a traditional PivotTable without adding a new column to the source range. Its formula uses fields from the source data, and the resulting field can be placed in the PivotTable Values area. This is useful when a calculation belongs in the summary, such as profit from revenue and cost, but you do not want to alter the original table.

Calculated fields are part of the PivotTable’s calculation setup, not ordinary worksheet formulas in its visible cells. That distinction matters: typing over a number displayed in the PivotTable will not change the calculation, and editing the source data will not rewrite the calculated-field formula. You must open the calculated-field dialog to change that formula.

A calculated field is also different from a calculated item. A calculated field works with source-data fields, such as Revenue and Cost. A calculated item performs a calculation involving particular items within a row or column field, such as combining two category entries. The command for calculated items is nearby, but it is not the right choice for changing a field formula.

Change the formula step by step

  1. Click a cell inside the PivotTable you want to edit. This makes the PivotTable-specific ribbon tab available.
  2. On the ribbon, choose PivotTable Analyze > Fields, Items & Sets > Calculated Field. The command is in the Calculations group.
  3. In the Calculated Field dialog, open the Name list and select the existing calculated field. Selecting the name is important: otherwise, you could create a second field instead of changing the first.
  4. Edit the expression in the Formula box. Use the field names available in the dialog; the Insert Field control can help enter a name correctly.
  5. Click Modify, then click OK. Inspect the PivotTable to confirm the revised field and totals are what you intended.

If you want to create a separate calculated field rather than change one, enter a new name, build its formula, and click Add. To remove an existing field, select it in the Name list and use Delete. Deleting a calculated field is not the same as deleting a source-data column; it removes that calculation from the PivotTable setup.

On Excel for Mac, select the PivotTable and look for the calculated-field command on the PivotTable Analyze tab; ribbon organization and wording can vary by release. Excel for the web does not expose all the desktop PivotTable authoring controls. If you cannot find the command there, open the workbook in the desktop app. These instructions apply to a standard PivotTable, not every PivotTable connected to an external or Data Model source.

Example: revising a profit calculation

Suppose the source table has columns named Revenue and Cost, and the PivotTable already contains a calculated field named Profit. To change the calculation, select Profit in the dialog and replace its formula with the revised expression. For example, if profit should now exclude a fee stored in a source field named Fee, include that field in the calculation.

Assuming the source table has Revenue in column B, Cost in column C, and Fee in column D, with the first record in row 2, a suitable calculated-field expression is =Revenue-Cost-Fee. If one record has Revenue of 120, Cost of 75, and Fee of 5, that record contributes 40 in profit. If a second record has Revenue of 80, Cost of 50, and Fee of 2, it contributes 28. With the PivotTable summing Profit across those two records, the displayed total is 68.

In the actual dialog, use the field names from your source rather than worksheet cell addresses such as B2 or C2. If a field name contains spaces or punctuation, select it from the Insert Field list instead of guessing how to type it. Keep the name of the calculated field unchanged when your goal is to revise the existing field; changing the name can create a different field rather than update the one already used in the layout.

How calculated-field results are summarized

A calculated field is evaluated against the source records and then summarized in the PivotTable. For additive calculations such as revenue minus cost, summing each record’s result gives the same total as subtracting the summed costs from the summed revenue. That is why a profit total often looks straightforward.

Ratios can behave differently. Imagine one record has Revenue of 120 and Units of 3, while another has Revenue of 80 and Units of 4. If the calculated field divides Revenue by Units and the PivotTable sums the results, it adds 40 and 20 to display 60. That is not the overall average selling price: total revenue divided by total units is 200 divided by 7, or about 28.57. The result depends on the expression and how it is summarized, so check whether you want a sum of per-record ratios, an average, or a ratio of totals.

This is a common reason a calculated field seems mathematically wrong even though Excel is applying its formula consistently. When the business meaning is a ratio of totals, a Data Model measure may be a better tool because it can calculate using the aggregate context of the PivotTable. Alternatively, calculate the result outside the PivotTable from its totals, taking care that the outside formula stays aligned if the layout changes.

When the calculated-field command is missing

If Calculated Field is unavailable or disabled, first check what kind of PivotTable you are working with. Calculated fields are supported in standard PivotTables built from a worksheet range or table, but not in the same way for PivotTables based on the Excel Data Model or an OLAP source. A connection to an Analysis Services cube, for example, may restrict calculations to tools provided by that source.

  • Check the source type: Data Model and OLAP PivotTables generally need a measure or calculation defined for that model or connection, rather than a classic calculated field.
  • Check that a PivotTable cell is selected: the relevant Analyze commands appear when the active cell belongs to a PivotTable.
  • Check for protection or restricted editing: workbook or sheet protection, or permissions on a connected source, may prevent changes.
  • Check that the field is not just a worksheet formula: a formula in the source table is changed in the source column, then the PivotTable is refreshed.

For a Data Model PivotTable, a measure created with DAX is the usual alternative. A measure can calculate results based on the current filters and grouping, which makes it appropriate for many ratios and percentages. It requires a Data Model and a different workflow; it is not a calculated field that can simply be edited in the dialog described above.

Calculated field, source column, or measure?

Choose the method based on where the calculation belongs and how it should aggregate. A calculated field is convenient for a basic calculation in a traditional PivotTable. A source-data column is often clearer when each individual record needs a reusable result, especially if that result must also be filtered, grouped, or used in other reports. A Data Model measure is suited to calculations that must respond to PivotTable filters or calculate ratios from totals.

  • Use a calculated field for a simple summary calculation in a conventional PivotTable, such as record-level profit from Revenue and Cost.
  • Use a source column when the derived value should be visible in the dataset and usable by other tools or PivotTables. After changing its values or formula, refresh the PivotTable.
  • Use a measure for a Data Model PivotTable or a context-sensitive calculation, such as total revenue divided by total units.
  • Use a normal worksheet formula when the calculation is only needed beside a particular PivotTable result and does not need to become a PivotTable field.

There is no universal best option. A calculated field keeps a simple calculation with the PivotTable, but its limits can be inconvenient when the calculation needs to be reused, grouped, or evaluated as a ratio of aggregates. If you expect the workbook to grow into a reusable reporting model, choosing a source column or measure at the outset can make the logic easier to maintain.

Common editing problems and checks

If the result does not change after editing, verify that you selected the intended calculated-field name and clicked Modify, not just OK or Add. Confirm that the formula refers to the exact source field names and that the new expression uses the intended operation. For example, reversing the order of Cost and Revenue changes the sign of a subtraction result.

Also check the PivotTable’s value summary and number format. A correct result can look unexpected if the field is summarized differently than you expect or displayed as a percentage, currency, or rounded number. Compare a small sample of source records with the PivotTable result. If the source records themselves changed, refresh the PivotTable; changing the calculated-field formula and refreshing source data are separate actions.

When several PivotTables are involved, confirm that you clicked the one whose calculation you intend to change. PivotTables can use different sources or model types, so an identical-looking report may not offer the same commands. If you need a calculation changed across a workbook, inspect each relevant PivotTable rather than assuming one edit updated every report.

Frequently Asked Questions

How do I edit an existing calculated field in Excel?

Click inside the PivotTable, choose PivotTable Analyze > Fields, Items & Sets > Calculated Field, select the existing field under Name, edit Formula, and click Modify. Then confirm the updated result in the PivotTable.

Why is Calculated Field greyed out?

The PivotTable may use the Data Model or an OLAP connection, which does not support classic calculated fields in the same way. It can also happen if a PivotTable cell is not selected or editing is restricted. For a Data Model report, consider creating a measure instead.

Do I need to refresh after changing a calculated field?

Changing the calculated-field definition normally updates its PivotTable results when you confirm the dialog. Refresh is needed to bring changed or newly added source records into the PivotTable; it is a separate step from editing the expression.

Can a calculated field use cell references?

No. Its formula refers to source fields by name, not worksheet addresses such as B2. Use the field list in the dialog to insert the correct source field, and use a source-data formula if you specifically need a row-by-row worksheet calculation.

What is the difference between a calculated field and a calculated item?

A calculated field calculates using source fields, such as Revenue and Cost. A calculated item calculates using particular entries within a PivotTable field. For a new or revised profit calculation, choose Calculated Field, not Calculated Item.

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.