To remove a calculated field from an Excel pivot table, click anywhere inside the pivot table, go to the PivotTable Analyze tab (or Options in older versions), click Fields, Items & Sets, choose Calculated Field, select the field name in the list, and click Delete. This permanently removes the calculated field from that pivot table, but it does not affect your source data or other pivot tables built on the same range.
What a Calculated Field Actually Is
A calculated field is a custom field you add to a pivot table that uses a formula based on other fields in the same pivot table. For example, if your source data has Sales and Cost columns, you might create a calculated field named Profit with the formula =Sales - Cost. Unlike a calculated item (which operates within a single field), a calculated field appears as a new column in the pivot table’s field list and can be used in the Values area, Rows, or Columns.
Calculated fields are stored with the pivot table itself, not in the source data. That means deleting one only affects the pivot table you are working on. If you have multiple pivot tables from the same data, each one has its own set of calculated fields, so you must remove the field separately from each pivot table if needed.
Step-by-Step: Removing a Calculated Field
These steps work in Excel for Microsoft 365, Excel 2021, 2019, 2016, and earlier versions on Windows. On Mac, the ribbon path is the same, but the tab is called PivotTable Analyze as well (in older Mac versions it may be Options).
- Click any cell inside the pivot table to activate the PivotTable Tools on the ribbon.
- Go to the PivotTable Analyze tab (or Options tab in Excel 2010 and earlier).
- In the Calculations group, click Fields, Items & Sets.
- From the dropdown, choose Calculated Field.
- In the Insert Calculated Field dialog box, you will see a list of all calculated fields under the Name dropdown. Select the one you want to delete.
- Click the Delete button. Excel will ask you to confirm – click OK.
- Click Close to exit the dialog.
The calculated field disappears from the pivot table immediately. Any formulas that referenced that field (for example, in a calculated item or another calculated field) will return a #REF! error, so check for those after deletion.
How to Choose Which Field to Remove
If you have several calculated fields, you might not remember which one is causing issues. To see what each field does, open the Calculated Field dialog and click on each name in the list. The formula appears in the Formula box. That way you can identify the exact field before deleting.
Also note that calculated fields are not the same as regular fields from your source data. If you simply want to hide a calculated field from the pivot table without deleting it, you can uncheck it in the PivotTable Field List – that just hides it temporarily. Deleting is permanent, so only use Delete when you are sure you no longer need that calculation.
Common Problems and Misconceptions
- “I deleted it but it still shows in the field list.” – This happens if you only unchecked the field, not deleted it. Use the Delete button in the Calculated Field dialog, not the checkbox.
- “The Delete button is grayed out.” – This occurs when you have not selected a calculated field in the Name dropdown. Click on the field name first.
- “I removed it, but other pivot tables still have it.” – Correct, because each pivot table stores its own calculated fields. You must repeat the deletion for each pivot table that uses it.
- “Can I undo the deletion?” – Yes, immediately press Ctrl+Z (Windows) or Cmd+Z (Mac) right after deleting. If you close and reopen the file, the undo history is gone.
- “Will deleting affect my source data?” – No. Calculated fields are only in the pivot table cache, not in the original data range.
Alternative: Remove All Calculated Fields at Once
There is no built-in button to delete all calculated fields at once. You must delete them one by one using the dialog. However, if you have many and want a clean slate, you can create a new pivot table from the same source data – the new pivot table will have no calculated fields. Simply select your source range, insert a new pivot table, and start fresh. This is often faster than deleting each field individually.
What About Calculated Items?
Calculated items are different from calculated fields. A calculated item is a custom entry within an existing field (for example, adding a “Total” item to a “Region” field). To remove a calculated item, go to the same Fields, Items & Sets menu, choose Calculated Item, select the item, and click Delete. The process is similar, but the dialog is different. If you mistakenly try to delete a calculated item using the Calculated Field dialog, you won’t see it.
Frequently Asked Questions
Why can’t I delete a calculated field that I added?
Make sure you are in the correct dialog. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. In the Name dropdown, select the field you want to delete. If the Delete button is still gray, you might be looking at a regular field from your source data – those cannot be deleted from the pivot table; they are only hidden.
Does removing a calculated field change my pivot table totals?
Yes, because the calculated field is no longer part of the pivot table. Any totals or subtotals that included that field will revert to excluding it. Your source data remains unchanged, so you can always re-add the field later.
Can I delete a calculated field if it’s used in a PivotChart?
Yes, but the chart will lose that series or data point. Excel will not prevent deletion, but the chart may show an empty space or an error. After deleting, update the chart to remove the reference.
Is there a way to delete a calculated field without opening the dialog?
No, the dialog is the only built-in method. However, you can use VBA to delete it programmatically. For example, the following macro removes a calculated field named “Profit” from the active pivot table:
Sub DeleteCalcField()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.CalculatedFields
If pf.Name = "Profit" Then
pf.Delete
Exit For
End If
Next pf
End Sub
Before running any macro, save a copy of your workbook because macros cannot be undone with Ctrl+Z.
What if I see “#REF!” after deleting a calculated field?
This means another formula (like a calculated item or a cell formula outside the pivot table) referenced the deleted field. Check any cells that show the error and update or remove those references. The pivot table itself will not show #REF! unless you have a calculated item that used the deleted field.
Can I delete a calculated field in Excel for the web?
Yes, Excel for the web supports calculated fields, and you can delete them using the same menu path: PivotTable Analyze > Fields, Items & Sets > Calculated Field. The interface is slightly different but the steps are the same.
Related
- How to Rotate a Table in Excel: 3 Easy Ways
- Excel How to Select Certain Cells: 6 Quick Ways
- Excel Transpose: How to Quickly Flip Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- 3D Pie Chart in Excel: Create, Format, and Improve ItTo make a 3-D pie chart in Excel for Microsoft 365 on Windows, select a two-column range containing…
- Google Sheets Distribution Charts: Types and SetupA Google Sheets distribution chart is usually a histogram: it groups numerical values into ranges, or bins, and…
- Excel Chart Elements: What They Are and How to Use ThemExcel chart elements are the visual parts that explain, organize, or add detail to a chart, such as…
- Excel to Pie Chart Converter: How to Make a Pie ChartExcel has a built-in pie chart converter: select a category column and its values, then choose Insert >…
- Excel Stacked Column Chart: Reverse Series or Categories?To reverse the order of the colored sections in an Excel stacked column chart, select the chart and…
- Pivot Table Value Filter Not Working? Fix It in ExcelIf a PivotTable value filter is not working, first confirm that you applied it to a field in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.