To add a formula directly to a PivotTable in Excel for Microsoft 365 on Windows, select the PivotTable, then choose PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Give the field a name, build its formula from the PivotTable’s source field names, and add it to the Values area. This creates a calculated field that updates with the PivotTable; for calculations that need to run on each source-data row, add a formula column to the source data instead.
What You’ll Need
- An Excel workbook with a conventional PivotTable built from a range or an Excel table. Calculated fields are not available for every PivotTable type, including many PivotTables built from the Data Model or an OLAP source.
- Source data with clear column headings and the numeric fields you want to calculate. For the example below, the source has fields named Revenue and Cost.
- Access to desktop Excel. Excel for the web may let you work with a PivotTable, but use the desktop application to create or edit a calculated field.
- A decision about whether the calculation belongs in the PivotTable or in the source data. A calculated field works with summarized field values; a source formula column calculates separately for each record.
Step-by-Step
- Check the source data and its headings. Make sure every column has a distinct, nonblank heading and that the fields you need contain usable numbers. If the source is a range, converting it to an Excel table with Insert > Table can make it easier to include new rows later. Avoid merged heading cells and blank rows inside the data. If you already have a PivotTable, click inside it and use PivotTable Analyze > Change Data Source to check which range or table it uses.
- Refresh the PivotTable if the source has changed. Click inside it and choose PivotTable Analyze > Refresh, or right-click the PivotTable and select Refresh. If you added a new source column, confirm that the PivotTable’s data source includes that column. Refreshing a PivotTable does not automatically expand every fixed source range to cover newly added rows.
- Decide whether a calculated field is the right kind of formula. Use one when you want a calculation based on summarized fields, such as total revenue less total cost. Use a source-data formula column when the calculation must be worked out for each individual order, employee, or transaction before the PivotTable groups the records. This distinction affects the result, so do not choose a calculated field just because you want a formula to appear beside the PivotTable.
- Click a cell inside the PivotTable. This makes the PivotTable-specific ribbon tab available. In Excel for Microsoft 365 on Windows, the tab is called PivotTable Analyze. If the tab does not appear, click within the PivotTable again; selecting a nearby cell outside it will not show the PivotTable tools.
- Open the Calculated Field dialog. Choose PivotTable Analyze > Fields, Items, & Sets > Calculated Field. In some Excel window sizes, the command may be tucked into a dropdown in the Calculations group. The dialog has a Name box and a Formula box, along with a list of source fields and an Insert Field button.
- Give the new field a clear name. Enter a label such as Profit in the Name box. Use a name that will make sense in the Values area and in the PivotTable headings. Check that it does not duplicate a source field name. Excel may reject a name that conflicts with an existing field or calculated field.
- Build the formula using the listed field names. The example assumes each source record has numeric fields named Revenue and Cost. In the Formula box, enter
=Revenue-Cost. The formula refers to the source field names, not worksheet addresses such as B2 or C2. To reduce typing mistakes, select a field in the list and click Insert Field; then enter the operator and the other field. Use the exact field names shown in the dialog. - Click Add, then OK. Add saves the calculated field definition in the dialog, while OK closes the dialog and applies it to the PivotTable. Depending on the dialog and Excel version, the field may be added to the Values area automatically. If it is not visible in the report, open the PivotTable Fields pane and drag the new field into Values. It should appear as a summarized value, commonly labeled Sum of Profit.
- Check the result and format it. Confirm that the new value appears in the expected row and column groups, and check the grand total. With two source records—Revenue 100 and Cost 60 in the first, Revenue 80 and Cost 30 in the second—the PivotTable’s grand-total calculation is 180 minus 90, or 90. Format the output by right-clicking a result, choosing Number Format, and selecting an appropriate currency, number, or percentage format. This applies number formatting to the value field without changing its formula.
- Edit the formula if you need to revise it. Click in the PivotTable and return to PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Choose the calculated field by name, update its formula, and select Modify or the equivalent update command shown in the dialog. To remove it, select the field in that dialog and choose Delete. Check the PivotTable after editing to make sure the intended field remains in Values.
Mac and web note: In current desktop Excel for Mac, select the PivotTable and look for the equivalent PivotTable Analyze command for calculated fields; ribbon labels or group placement can vary by release. If you cannot find the command, open the workbook in desktop Excel for Windows or Mac. Excel for the web does not provide the same full calculated-field authoring workflow.
Common Mistakes
- Using cell references instead of field names. A calculated field is not an ordinary worksheet formula. References such as B2 and C2 do not tell Excel which PivotTable source fields to summarize. Insert source fields from the dialog’s list.
- Expecting a row-by-row calculation. Calculated fields work on summarized values for each PivotTable group. For example, the group calculation based on total revenue and total cost can differ from averaging the profit on individual records. If the business rule must be applied per record, calculate it in a source-data column and refresh the PivotTable.
- Trying to add a calculated field to a Data Model PivotTable. A PivotTable based on the workbook Data Model or an OLAP connection may not offer the standard Calculated Field command. Use a suitable source-data column or create a measure with Power Pivot and DAX when that model supports it.
- Using a field with text or inconsistent data. A calculation involving a column that contains text, blanks, or numbers stored as text may give unexpected summaries or errors. Clean the source column and confirm Excel recognizes its values as numbers.
- Forgetting to refresh or include new source columns. A newly added field will not be available if the PivotTable still points to an older source range. Update the data source, then refresh before trying to create the formula.
- Adding a calculated item when you mean a calculated field. A calculated item operates on individual items within a row or column field, such as categories. It is not the normal choice for calculating a new value from source columns, and it can make totals harder to interpret.
Tips
- Use a short, distinct calculated-field name, then give its displayed value a more descriptive custom heading if needed. Clear names are especially helpful when the PivotTable contains many measures.
- Use Insert Field rather than typing long or spaced field names by hand. This helps avoid misspellings and makes it easier to tell which source columns the calculation uses.
- Keep the original source data unchanged when you are experimenting. A calculated field can be edited or deleted without adding extra formula columns to the source.
- For a calculation that is simply a ratio or percentage, confirm what the PivotTable is summarizing. Dividing one summarized amount by another can be useful, but it is not necessarily the same as averaging the row-level percentages. For row-level rates, calculate each record’s rate in the source data, then choose an appropriate summary.
- After changing the formula or source records, refresh the PivotTable and check both a detail group and the grand total. A total can look plausible while the calculation is being applied at a different level than you intended.
- If you need to reuse the formula in several reports, consider adding it as a clearly labeled column in an Excel table or defining a model measure. That makes the calculation easier to share and maintain than separate, differently named calculated fields in multiple PivotTables.
Troubleshooting
- The Calculated Field command is disabled or missing: Verify that you clicked inside the PivotTable. Then check whether its source is a normal range or table rather than the Data Model, an OLAP cube, or another external model. For model-based reports, use a measure if available, or add the calculation to the source before building the PivotTable.
- The new field does not appear in the report: Open the PivotTable Fields pane and look for the calculated field. Drag it to Values if it is not already there. If the field is absent, reopen the Calculated Field dialog to confirm it was added, check for a naming conflict, and refresh after correcting the source.
- The result is wrong even though the formula is accepted: Check whether Excel is calculating from group totals rather than calculating each source row first. Compare one group against the relevant source values. If you need a row-by-row result, add a formula column to the source, fill it down, ensure the PivotTable source includes it, and refresh.
- The formula is rejected: Check the spelling of every field name against the dialog’s list, use the Insert Field button, and confirm that the formula uses supported arithmetic and valid operators. Do not include worksheet cell addresses or a field name that no longer exists in the source.
- New records or fields are missing: Use PivotTable Analyze > Change Data Source to inspect the source range. Expand it to cover the new data or use an Excel table as the source, then refresh the PivotTable. If new rows are outside the range, a refresh alone may not bring them in.
Frequently Asked Questions
Can I use a normal Excel formula inside a PivotTable cell?
You can enter a worksheet formula in a cell outside the PivotTable, but putting one in the PivotTable’s body is unreliable: refreshes and layout changes can overwrite or move it. Use a calculated field for a PivotTable-level calculation, or add a formula column to the source for row-level logic.
What is the difference between a calculated field and a calculated item?
A calculated field creates a new value using source fields, such as revenue less cost. A calculated item calculates using items within one existing PivotTable field, such as values in a category field. Most formulas that combine source columns call for a calculated field, not a calculated item.
Will a calculated field update when the source data changes?
The calculated-field definition remains part of the PivotTable, but the displayed results update after the PivotTable includes the changed source data and is refreshed. If you add rows outside a fixed source range, update the data source first.
Can a calculated field use a field that is not shown in the PivotTable?
Yes. A source field can be used in the calculated-field formula even if you have not placed that source field in Rows, Columns, or Values. The field must still be part of the PivotTable’s source, and its name must match the field list.
Can I create a calculated field in Excel for the web?
For the full calculated-field creation and editing workflow, use desktop Excel for Microsoft 365 on Windows or Mac. If the workbook is open in a browser and the command is unavailable, open it in the desktop app.
Why does the grand total not match the sum of visible row results?
A calculated field is evaluated for each PivotTable group, including the grand-total context. Its grand total may therefore be calculated from grand-total field values rather than by adding the displayed group results. If you need the total of row-level calculations, create the calculation in the source data and summarize that result.
Related
- Import CSV Into an Existing Excel Table: Steps & Tips
- Import Excel to Jira CSV: Sample File and Import Steps
- Excel Import Multiple CSV Files: Methods and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Variance Between Columns: How to CalculateTo show a variance between column items in an Excel PivotTable, right-click a value, choose Show Values As…
- Excel Chart Not Plotting Zero Values? How to Fix ItExcel normally plots numeric zero values, but a zero can be hard to see: a zero-height column has…
- Excel Combo Chart Secondary Axis: Setup and TipsTo put a series on a secondary axis in an Excel combo chart, select the chart, choose Chart…
- Excel Pivot Table: How to Turn Off SubtotalsTo turn off every subtotal in an Excel PivotTable, click inside it and choose PivotTable Design > Subtotals…
- Excel Gantt Chart With a Today LineYes. For a Gantt chart made from worksheet cells, apply conditional formatting to the date grid so the…
- Power Pivot From Multiple Excel Files: How It WorksYes. In Excel for Microsoft 365 on Windows, use Power Query to import or combine data from multiple…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.