A calculated field in an Excel pivot table is a custom column you add that performs a formula on the pivot’s underlying data, such as =Sales/Units to show average price. To add one, click anywhere in the pivot table, go to PivotTable Analyze > Fields, Items & Sets > Calculated Field, enter a name and formula, then click OK.
What Is a Calculated Field in a Pivot Table?
A calculated field is a new field that you define within a pivot table, based on the existing fields in your source data. Unlike a calculated item (which works on individual items within a field), a calculated field applies its formula to the sum of the underlying data for each row and column combination. For example, if your source data has Revenue and Cost columns, you can create a calculated field named Profit with the formula =Revenue - Cost. The pivot table then shows the profit for each product, region, or time period, using the aggregated totals of Revenue and Cost.
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 use them just like any other value field—sum, average, count, etc. However, they have some quirks, which we’ll cover below.
How to Add a Calculated Field (Step by Step)
Here’s the exact process in Excel for Microsoft 365 on Windows (Mac steps are similar):
- Click anywhere inside the pivot table to activate the PivotTable Analyze tab (on Mac, it’s the PivotTable Analyze tab as well).
- On the ribbon, go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field.
- In the Insert Calculated Field dialog box, type a name for the field (e.g., “Profit”).
- In the Formula box, type your formula using the field names from the source data. You can double-click a field name from the list below to insert it. For example, type
= Revenue - Cost. - Click Add, then OK.
The new field appears in the PivotTable Field List (usually under “Values”). Drag it into the Values area if it isn’t already there. On a Mac, the steps are identical—the dialog box looks the same. In Excel for the web, calculated fields are not supported; you’ll need to add the formula to your source data or use a regular formula outside the pivot table.
How Calculated Fields Work Behind the Scenes
When you create a calculated field, Excel stores the formula as a member of the pivot table’s internal data model. The formula is evaluated after the pivot table aggregates the source data. This is a crucial point: the calculation is not performed on individual rows, but on the sums of each field within a given pivot context.
For example, suppose you have sales data with columns: Region, Product, Units, and Price. You create a calculated field named Revenue with formula =Units * Price. If you place Region in Rows and the calculated field in Values, Excel sums all Units for each region, sums all Price for each region, and then multiplies those two sums. This is not the same as multiplying each row’s Units by its Price and then summing those products. The result can be wildly different if prices vary per row.
This “sum-of-fields” behavior is the most common source of confusion. To avoid it, you should add a Revenue column to your source data (e.g., =Units*Price in a new column) and then pivot on that, rather than using a calculated field. Calculated fields are best for simple arithmetic on aggregated totals, like profit margin (=Profit/Sales) or average order value (=Sales/Orders).
When to Use a Calculated Field vs. a Calculated Item
Calculated fields and calculated items are often mixed up, but they serve different purposes:
- Calculated Field: Adds a new field that uses other fields from the source data. It works on the aggregated sums. Example:
=Sales - Cost. - Calculated Item: Adds a new row or column within an existing field, based on other items in that same field. Example: In a “Product” field, create a “Bundled” item that equals
ProductA + ProductB. Calculated items only work in the row or column area, not in the Values area.
Use a calculated field when you need a new metric derived from multiple source columns. Use a calculated item when you want to combine or compare specific items within a single field, like a subtotal for two products.
Common Problems and How to Fix Them
Here are the frequent issues users hit with calculated fields:
- Wrong results due to sum-of-fields behavior: As explained, if your formula involves multiplication or division of two fields, the result may be incorrect. Fix: add the calculation to your source data as a new column, then refresh the pivot.
- Calculated field not showing in the field list: After adding, it appears under “Values” but not in the field list as a normal field. It will show in the “Values” area; drag it there if needed.
- Cannot use calculated field in another calculated field: You can reference a calculated field in a new calculated field, but the order matters. Excel evaluates them sequentially, so the referenced field must exist. For example, create Profit first, then create Margin as
=Profit/Sales. - Calculated field disappears after refreshing: This happens if you delete the pivot table or if you recreate it. Calculated fields are stored with the pivot table, not the source data. If you create a new pivot from the same source, you must re-add the calculated field.
- Cannot use fields from multiple tables: If your pivot is based on multiple tables (Data Model), calculated fields are limited. You may need to create a DAX measure instead, which is more powerful but requires Power Pivot.
Examples of Useful Calculated Field Formulas
Here are a few common formulas you can type in the Calculated Field dialog (assuming your source data has these columns):
- Profit:
=Revenue - Cost - Profit Margin:
=Profit / Revenue(but beware of the sum-of-fields issue; better to create Profit as a calculated field, then Margin as another calculated field referencing Profit) - Average Order Value:
=Sales / Orders(if you have a column for number of orders) - Commission:
=Sales * 0.05(5% commission)
Remember: these formulas operate on the aggregated sums. For example, if you have a “Sales” field and a “Units” field, =Sales/Units will give you the average price per unit for each pivot grouping, which is usually what you want.
Alternatives to Calculated Fields
If calculated fields aren’t giving you the results you need, consider these alternatives:
- Add a column to your source data: The simplest and most reliable method. Add a formula like
=Revenue - Costin a new column, then refresh your pivot. This gives you row-level accuracy and full control. - Use Power Pivot and DAX measures: If you’re using Excel 2013 or later (or Microsoft 365), you can create measures that are more flexible and accurate, especially for complex calculations. Go to Power Pivot > Measures > New Measure.
- Use GETPIVOTDATA or regular formulas outside the pivot: If you need a one-off calculation, you can reference pivot cells with formulas like
=GETPIVOTDATA("Sales",$A$3)*0.05. - Use a regular table and SUMIFS: For simple summaries, you might skip the pivot table entirely and use
SUMIFSto calculate totals, then do your arithmetic in normal cells.
Each approach has trade-offs. Calculated fields are quick and convenient but limited. Source data columns are accurate but require you to modify your data. DAX measures are powerful but have a steeper learning curve.
Frequently Asked Questions
Why is my calculated field showing the wrong total?
Most likely because the formula is being applied to the sum of the underlying fields, not to each row. For example, if you have =Units * Price, Excel sums all Units, sums all Price, then multiplies those two sums. To get the correct total revenue, add a Revenue column to your source data.
Can I use a calculated field in a pivot chart?
Yes, calculated fields appear as value fields and can be plotted in a pivot chart just like any other value. Simply add the calculated field to the Values area, then insert a pivot chart from the PivotTable Analyze tab.
How do I delete a calculated field?
Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field. In the dialog box, select the field name from the drop-down list, then click Delete. The field will be removed from the pivot table.
Can I use a calculated field in a slicer or filter?
No, calculated fields are value fields, so they cannot be used as row/column labels or in slicers. They only appear in the Values area. If you need to filter based on a calculated metric, you’ll need to add it to your source data and then use it as a regular field.
Does the calculated field update when I refresh the pivot?
Yes, the calculated field recalculates when you refresh the pivot table, because it uses the current aggregated data. However, if you change the formula itself, you must edit it in the Calculated Field dialog.
Can I use a calculated field in a pivot table based on an external data source?
Yes, calculated fields work with external data sources as long as the pivot is based on a single table. If you’re using multiple tables in the Data Model, you’ll need to use DAX measures instead.
Related
- Excel How to Get to Data Validation: Full Guide
- Excel: How to Hide Rows in a Table (3 Ways)
- Excel Split Cells Fast: 4 Ways That Work
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Build a Bubble Chart in Excel: A Step-by-Step GuideTo build a bubble chart in Excel, arrange each observation’s X value, Y value, and bubble-size value in…
- Excel X-Y Chart: How to Create and Read OneIn Excel, an x-y chart is usually an XY (scatter) chart: select paired numeric x and y values,…
- Properties of Log Functions: Rules, Graphs, and ExamplesA logarithmic function is the inverse of an exponential function: logb(x) tells you the exponent to which the…
- Fill Area in an Excel Chart: A Practical Step-by-StepTo fill an area in an Excel chart, select the data series, right-click it, choose Format Data Series,…
- Excel Radar Chart Scale: Set the Minimum and MaximumIn Excel for Microsoft 365 on Windows, select the radar chart’s numeric value axis, right-click it, and choose…
- Conditional Progress Circle Chart in Excel: Build OneYes. In Excel for Microsoft 365 on Windows, you can make a conditional progress circle with a doughnut…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.