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.
- Click anywhere inside your pivot table to activate the PivotTable Analyze tab (on Mac: PivotTable Analyze).
- Go to Fields, Items & Sets > Calculated Field.
- In the Insert Calculated Field dialog, type a name in the Name box (e.g., “Profit”).
- 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. - 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*Costinstead. - 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
- Excel Wrap Text in Quotes? How to Add Quotation Marks
- Excel Weekly Schedule Template: How to Make One
- Excel Weather Forecast: How to Pull Live Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Average Line: Add and Format the MeanTo add an average line to an Excel chart, calculate the mean in a worksheet cell, repeat that…
- Probability Histogram in Excel: A Step-by-Step GuideTo make a probability histogram in Excel, divide your numeric data into bins, count the observations in each…
- Excel Chart Maker: Create, Format, and Choose ChartsExcel’s built-in chart maker turns selected worksheet data into a chart: select a well-organized range, then choose Insert…
- Excel Pie Chart Tutorial: Create and Format OneTo make a pie chart in Excel for Microsoft 365 on Windows, select your category names and numeric…
- Excel Half-Moon Chart: Add Red, Yellow, and Green ZonesTo create a red, yellow, and green half-moon chart in Excel, make a doughnut chart from three equal…
- Excel Pivot Group Rows: Dates, Numbers, and CategoriesTo group rows in an Excel PivotTable, right-click a row label or value and choose Group; for dates…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.