Excel PivotTable Calculated Fields: How to Create One

How-to basics · Updated November 22, 2026

To create a calculated field in an Excel PivotTable, select the PivotTable, then choose PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Give the field a name, enter a formula using source-field names, and select Add. This works for regular PivotTables, but not PivotTables based on the Data Model or an OLAP source.

What a calculated field does

A calculated field adds a new value to a PivotTable by applying a formula to fields in its source data. For example, if a source table contains Revenue and Cost, a calculated field named Profit can calculate the difference. You do not need to add a Profit column to the source worksheet first.

The field is part of the PivotTable, not a normal worksheet formula copied into its cells. Excel uses the source fields to calculate values as the PivotTable summarizes its records. The calculated field can then be arranged in the Values area like other numeric fields, and its results respond to changes in the PivotTable’s rows, columns, filters, and slicers.

This is useful when you want a calculation to travel with a PivotTable or do not want to alter the original source data. It is not a general-purpose formula editor: calculated-field formulas refer to source fields, not worksheet cell addresses such as A2 or B2.

Create a calculated field step by step

  1. Make sure the source data has clear column headings and contains the fields needed for the calculation. A regular Excel Table is a convenient source because it can expand as you add records.
  2. Click a cell inside the PivotTable. Excel displays the contextual PivotTable Analyze tab on the ribbon.
  3. Choose PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Depending on the ribbon width, the command may appear within a grouped menu.
  4. In the Insert Calculated Field dialog, enter a name in the Name box, such as Profit.
  5. In the Formula box, enter the expression. Use the field list to insert field names; double-clicking a field can help avoid typing a name incorrectly. For example, the formula is =Revenue-Cost.
  6. Select Add, then select OK. Excel adds the calculated field to the PivotTable field list and normally places it in the Values area. If it is not there, drag it into Values.

Use the exact source-field names shown in the dialog. Do not substitute cell references or type the displayed PivotTable label in place of a source field. If you want to change the calculation later, reopen the same dialog, select the calculated field by name, edit its formula, and choose Modify.

Worked example: calculate profit

Suppose the source contains two records: one has Revenue of 120 and Cost of 70, and the other has Revenue of 80 and Cost of 50. The calculated field formula =Revenue-Cost produces 50 for the first record and 30 for the second. With the default Sum summary, the PivotTable shows total Profit of 80. This agrees with total Revenue of 200 minus total Cost of 120.

Place fields such as Region or Product in Rows, and place Revenue, Cost, and Profit in Values to compare the totals. You can use the calculated field in a filtered or grouped PivotTable without writing a separate formula for each displayed row. If the source data changes, refresh the PivotTable so the new records are included in the summary.

A calculated field can also use arithmetic with constants. For instance, if a field named Sales stores a value and the goal is to calculate a 5% charge per record, a formula such as =Sales*5% calculates that amount from each source record. Check that the chosen summary operation makes sense for the result: summing per-record charges is often useful, while summing rates or percentages usually is not.

How the calculation is aggregated

The key detail is that a standard calculated field’s formula is evaluated against the source records, and its results are then summarized in the PivotTable. The Values area typically summarizes a numeric field by Sum, though you can change the summary through Value Field Settings. This behavior explains why a formula that appears reasonable can produce a surprising grand total.

For example, a calculated field for a per-record margin, =(Revenue-Cost)/Revenue, returns 50/120 for the first example record and 30/80 for the second. If the PivotTable sums those results, the total is about 1.2083, or 120.83%—not the overall margin. The overall margin from the combined totals is 80/200, or 40%. An average of record-level margins would be a different statistic again. Choose the calculation based on the question you actually want the PivotTable to answer.

In general, additive values such as revenue, cost, quantity, and profit are good candidates for a calculated field summarized by Sum. Ratios, percentages, and other measures that depend on totals need more care. A subtotal may represent a different set of records from the grand total, so it is important to verify both against a small hand-calculated example.

Edit, format, and refresh the field

To revise a formula, click the PivotTable and return to PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Select its name from the list, change the formula, and choose Modify. To remove it, select the field in that dialog and use Delete. Removing a calculated field does not delete the original source columns.

To format its displayed values, right-click a value in that field and choose Number Format, or open Value Field Settings and choose Number Format. Setting a number format through the field settings is generally more reliable than formatting individual PivotTable cells, because PivotTable refreshes can rearrange or regenerate cells.

Refresh the PivotTable after editing source records: right-click inside it and choose Refresh, or use PivotTable Analyze > Refresh. If the source range does not include newly added rows, refresh alone will not bring them in. An Excel Table used as the source usually expands to include adjacent new records; otherwise, update the PivotTable’s data source as well.

Important limits and when to use a measure

The Calculated Field command is intended for regular, non-OLAP PivotTables. It is unavailable for PivotTables built from the Excel Data Model or certain external OLAP sources. Those PivotTables use measures instead. A measure is written in DAX and can calculate a ratio from aggregated totals, which is often the right approach for margins and other non-additive metrics. In Microsoft 365 for Windows, measure tools are available when the Power Pivot features are available and enabled; the exact ribbon options can depend on the workbook and Excel configuration.

  • Use a calculated field for straightforward arithmetic between source fields when the result should be calculated per record and then summarized.
  • Use a measure for Data Model or OLAP PivotTables, or when the desired result must be computed from the filtered totals rather than by adding record-level results.
  • Add a helper column to the source when the calculation needs ordinary row-by-row worksheet logic, cell references, or a result that must also be available outside the PivotTable.

A calculated field is also different from a calculated item. A calculated field creates a value from source columns and is used in the Values area. A calculated item creates a formula involving particular items within a row or column field—for example, combining named product categories. Calculated items have separate behavior and can complicate PivotTable totals, so do not choose one simply because its name sounds similar.

Common problems and fixes

  • The command is unavailable: Confirm that the selected PivotTable is a regular PivotTable rather than one built from the Data Model or an OLAP source. For those sources, use a measure if appropriate.
  • The formula rejects a field name: Use the field list to insert the name, and check that the source column heading is present and spelled as displayed. Avoid cell references.
  • The field appears to show an unexpected total: Check how its results are being summarized. Sum may be appropriate for profit but misleading for rates or percentages. Compare the PivotTable result with a small manual calculation.
  • New source rows are missing: Refresh the PivotTable and check that its source range includes the added records. Consider converting the source range to an Excel Table.
  • A source column was renamed: Recheck the calculated-field formula, since it depends on source field names. Edit the field formula after confirming the PivotTable recognizes the updated source heading.

On Excel for Mac, the contextual PivotTable ribbon provides PivotTable analysis commands, though their placement can differ with the ribbon layout and version. Excel for the web has fewer PivotTable authoring controls; if the calculated-field command is not available in the workbook, open it in the desktop Excel app to create or edit the field.

Frequently Asked Questions

Can I use cell references in a PivotTable calculated field?
No. A calculated field formula uses source-field names, not worksheet references such as A2 or B2. Use a helper column in the source data if the calculation depends on a particular cell or needs normal worksheet formula behavior.

Why is Calculated Field greyed out in Excel?
The PivotTable may be based on the Data Model or an OLAP source, where calculated fields are not supported. Use a DAX measure for that PivotTable, or create a regular PivotTable from a suitable worksheet range or Excel Table.

Can a calculated field divide one field by another?
Yes. A formula can divide source-field values, but remember that Excel evaluates it at the record level and then summarizes the results. A sum of individual ratios is not necessarily the ratio of the totals.

Does a calculated field update when source data changes?
The formula remains part of the PivotTable, but the displayed results update when the PivotTable is refreshed. Also confirm that the PivotTable source includes any new rows you want to summarize.

What is the difference between a calculated field and a calculated item?
A calculated field uses source fields to create a value, usually for the Values area. A calculated item operates on specific entries within a row or column field. For calculations such as profit from revenue and cost, use a calculated field.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.