Calculated Field in a PivotTable: How It Works

A calculated field in an Excel PivotTable is a custom calculation made from fields in the source data, such as profit calculated as revenue minus cost. In Excel for Microsoft 365 on Windows, select the PivotTable, then choose PivotTable Analyze > Fields, Items & Sets > Calculated Field; enter a name and formula, and select Add or OK.

What a calculated field does

A PivotTable summarizes records, usually by adding or counting values and grouping them into categories. A calculated field adds a new value to that summary by applying a formula to existing source fields. It is useful when a number can be derived from columns already in the source data and you want to examine the result by the PivotTable’s row and column groups.

For example, a sales dataset might have Revenue and Cost fields but no Profit field. A calculated field can define profit as revenue minus cost, so the PivotTable can show profit by product, salesperson, or month. The calculation is part of the PivotTable; it does not insert a new column into the original source table.

Calculated fields are different from ordinary worksheet formulas placed next to a PivotTable. A worksheet formula refers to cells and can be disrupted when the PivotTable changes size. A calculated field belongs to the PivotTable and is included in its layout and summaries when you rearrange fields.

How Excel evaluates the calculation

A classic PivotTable calculated field is based on the underlying values of its source fields, then summarized in the PivotTable. It is not generally a formula that runs separately against each visible PivotTable cell. This distinction matters for ratios: dividing summed profit by summed revenue gives an overall margin, whereas averaging the margins of individual records can give a different answer.

Suppose the source table has fields named Revenue, Cost, and Units. For a calculated field named Profit, the field layout assumed here is one source record per transaction, with numeric Revenue and Cost columns. Enter =Revenue-Cost. If a region has $300 of revenue and $170 of cost in total, the PivotTable’s Profit value for that region is $130.

For a calculated field named Margin, using the same source fields and layout, enter =Profit/Revenue. If Profit totals $130 and Revenue totals $300, the regional margin is 43.33% (130 divided by 300). Format the resulting PivotTable values as percentages. This is a ratio of summarized amounts, not necessarily the average of transaction-level margin percentages.

For a calculated field named Revenue per Unit, with Revenue and Units as numeric source fields, enter =Revenue/Units. If the grouped records total $300 in revenue and 9 units, the result is $33.33 per unit. Check whether that definition matches the business question: it is a weighted average price when the fields represent sales totals and units, not an average of each transaction’s unit price.

How to add one in Excel for Windows

Make sure the source data has clear column headers and that the fields used in the calculation contain appropriate values. Click inside the PivotTable.
On the ribbon, open PivotTable Analyze. Choose Fields, Items & Sets, then Calculated Field. In some window sizes, the ribbon groups or commands may be compressed, but the command name is the same.
In the Insert Calculated Field dialog, type a name in the Name box. Use a distinct, readable name such as Profit or Margin.
In the Formula box, enter an equals sign followed by a formula using source field names. Use the Insert Field list to add a field name rather than guessing its spelling.
Select Add if you want to create another field without closing the dialog, or select OK to add the field and return to the PivotTable.
In the PivotTable Fields pane, find the new calculated field and place it in Values. Adjust its number format through Value Field Settings or the number-format controls so the result is displayed appropriately.

Recent desktop versions of Excel for Mac may expose the command on the PivotTable Analyze tab under Fields, Items & Sets. Ribbon layout and command availability can vary by release; if the command is missing, use Excel desktop for Windows or check whether the PivotTable is based on the Data Model or an OLAP source. Excel for the web has more limited PivotTable authoring features, so create or edit a classic calculated field in the desktop application.

Writing formulas and choosing field names

Calculated field formulas use source field names rather than worksheet cell references. A formula such as =B2-C2 is not the right approach: B2 and C2 refer to positions on a worksheet, not fields in the PivotTable’s source. Field names must match the source headers, and the formula can use arithmetic operators and supported functions. If a header contains spaces or punctuation, use the Insert Field list and follow the syntax Excel places in the formula box.

Calculated fields are most straightforward when each input field is numeric and the calculation makes sense on summarized values. Basic arithmetic such as addition, subtraction, multiplication, and division is common. Functions can also be used, but not every worksheet formula pattern translates neatly to a PivotTable calculated field. Test the result against a small, independently checked example before relying on it in a report.

One practical check is to calculate the expected result outside the PivotTable for a small group. If the PivotTable shows total revenue of $300 and total cost of $170, a Profit field should show $130. If the result instead seems multiplied by the number of records or does not match the intended definition, review the field formula, the aggregation, and the source data.

Calculated field or source-data column?

A calculated field is convenient for a PivotTable-specific calculation that should respond to the PivotTable’s groupings. A source-data column is often better when the derived value must be available to charts, formulas, exports, or multiple reports, or when the calculation must be defined record by record. For instance, a row-level commission amount can be added as a source column if other parts of the workbook need to use each transaction’s commission.

Before choosing, consider these factors:

Definition: Does the calculation mean a formula over summed values, or does it require calculating each record first and then summarizing those results?
Reuse: Will several PivotTables or downstream analyses need the same derived value? A source column or a Data Model measure may be easier to maintain.
Transparency: Will another person understand the result and how it was derived? Clear field names and documented definitions reduce confusion.
Source quality: Are blanks, text values, errors, or inconsistent units likely to affect the result?

For example, if every transaction has a quantity and a unit price, a source column for transaction revenue may be preferable when the transaction-level result is important elsewhere. If the goal is simply to show a PivotTable’s total revenue less total cost, a calculated field can be an efficient option. Neither approach is universally superior; the key is matching the calculation’s level to the question.

Calculated fields, calculated items, and measures

A calculated field is not the same as a calculated item. A calculated field creates a value from fields in the source data. A calculated item creates a new item within a particular PivotTable field, combining or calculating among its items—for example, a custom item in a category field. Calculated items can complicate totals and may be unsuitable for large or complex PivotTables, so do not choose one simply because its name sounds similar.

A measure is a separate kind of calculation used with the Excel Data Model and Power Pivot. Measures use DAX and are designed for aggregations and filter-aware calculations. They are often the more appropriate choice for Data Model PivotTables, distinct counts, or more involved ratio logic. The classic Calculated Field command is not available for PivotTables built from OLAP sources or the Data Model; use a measure or change the source model instead.

When a workbook uses a regular table or range as its PivotTable source, a classic calculated field is usually the feature people mean by “calculated field in a pivot table.” If the command is disabled or unavailable, check the PivotTable’s source type before assuming Excel is malfunctioning.

Common problems and how to fix them

The command is unavailable: The PivotTable may use the Data Model, an OLAP connection, or another external source that does not support classic calculated fields. Create a DAX measure where appropriate, or use a calculated column in the source.
The formula is rejected: Verify the field names using Insert Field. The formula must refer to source fields, not cell addresses or the displayed PivotTable labels. Check operators, parentheses, and any function arguments.
The result is unexpected: Confirm whether the calculation should operate on summarized totals or on each underlying record. Ratios and averages are especially easy to misinterpret.
A field is missing from the list: Refresh the PivotTable after changing source headers or adding source columns, and confirm the PivotTable’s source range includes the new data. A source formatted as an Excel table can expand more reliably when rows are added.
Blanks or errors appear: Inspect the source for blank, text, zero, or error values that can affect arithmetic. For division, consider what the desired result should be when the denominator is zero; a classic calculated field may not provide the record-level control you need.
The number format looks wrong: Set the calculated value’s number format in the PivotTable rather than relying on the source field’s appearance. Use currency for monetary totals, a percentage for margins, or an appropriate decimal format for rates.

After changing source data, refresh the PivotTable so the summary reflects the new records. If the calculation itself changes, edit it through the Calculated Field dialog rather than typing over a value displayed in the PivotTable; PivotTable result cells are generated by the summary and are not the place to maintain the calculation.

When a calculated field is a good choice

Use a classic calculated field when the source is a regular range or table, the calculation is relatively simple, and the result should be summarized alongside the PivotTable’s other values. Typical examples include gross profit from sales and cost, a markup calculated from totals, or a ratio of two summed measures. It can make a report easier to explore because users can rearrange categories without rewriting formulas beside the PivotTable.

Prefer a source column when the business rule is inherently row-level and needs to be reused broadly. Prefer a Data Model measure when the PivotTable is model-based or the calculation needs DAX’s filter-aware behavior. If the result is a complex statistical calculation or a ratio whose definition depends on individual records, verify the method carefully rather than assuming a calculated field will behave like a worksheet formula copied down every row.

Frequently Asked Questions

Can a PivotTable calculated field use cell references?
No. It should refer to source field names, not worksheet addresses such as A2 or B2. Use the Insert Field list in the Calculated Field dialog to insert the correct source fields.

Why is Calculated Field grayed out?
The PivotTable may be connected to the Data Model or an OLAP source, which does not support classic calculated fields. Use a DAX measure for a model-based PivotTable, or add a suitable field to the source data.

Does a calculated field calculate each source row separately?
Do not assume it behaves like a worksheet formula filled down the source table. Classic calculated fields are evaluated in the PivotTable’s summarized context, so a ratio commonly uses the summarized numerator divided by the summarized denominator. Check the result against the calculation definition you need.

Can I calculate profit margin in a PivotTable?
Yes, when the source has suitable Profit and Revenue fields and the intended result is total profit divided by total revenue. Format the result as a percentage and confirm that this aggregate margin—not an average of individual transaction margins—is the measure you want.

How do I change or remove a calculated field?
Open PivotTable Analyze > Fields, Items & Sets > Calculated Field, select the field by name, and edit its formula or choose Delete. Removing it from the Values area only removes it from the current layout; it does not necessarily delete the calculated-field definition.

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.