ExcelInsider Team
In a pivot table, calculated fields are beneficial for performing various calculations without touching the source data range. However, the fields cannot be directly renamed without deleting and creating a new one. The accountant might want some other calculation instead of something that was added when the table was created. In this article, we will learn how to delete calculated field in pivot table so that you can delete the calculated fields of your pivot table at ease.
➤ Select any cell of the pivot table.
➤ Go to the PivotTable Analyze tab.
➤ From the Calculations group, open the dropdown menu of Fields, Items, & Sets and select Calculated Field.
➤ Select your desired field from the dropdown, and press Delete. Click OK afterwards.
In this article, we will show the details of this method and another advanced method to accomplish the job as well. Therefore, stick around with us to learn how to delete a calculated field in a pivot table properly.
Manually Deleting Calculated Field in a Pivot Table
We have a pivot table from the HR department here. The table contains the number of days an employee was present, the number of days they were absent, and a calculated field containing a bonus for the employees for being present at the office. Now the department has decided that they will include the bonus in the source table in the future, which means the calculated field is no longer needed. We need to delete the field to proceed.
➤ Select any cell from the pivot table. This will make the PivotTable Analyze and Design tabs appear on the ribbon.
➤ Open the PivotTable Analyze tab and find the Calculations group.
➤ Go to Fields, Items, & Sets > Calculated Field
➤ On the right of the Name field, there is a small arrow to open a drop-down menu. Click on that to find the field called Bonus. Select the field.
➤ Press Delete and OK.
➤ Check the spreadsheet now to see the result.
Deleting Calculated Field Using VBA
If you are an advanced user, this method will be helpful for you. However, this method has a disadvantage. Instead of permanently deleting the field, this method hides the field from the pivot table. The field still exists in the Calculated Field section of the tabs and can be modified later.
➤ Press Alt + F11 to open the coding interface.
➤ Go to Insert > Module to get the code editor.
➤ Write this code:
Sub DeleteCalculatedField()
Dim pvt As pivotTable
Dim pf As PivotField
Dim pi As pivotItem
Set pvt = ActiveSheet.PivotTables("PivotTable1")
For Each pf In pvt.DataFields
If pf.SourceName = "Bonus" Then Exit For
Next
pf.DataRange.Cells(1, 1).pivotItem.Visible = False
End Sub➤ Replace PivotTable1 with the name of your pivot table, and Bonus with the name of the calculated field.
➤ Select Run > Run Sub/UserForm to run the code.
➤ Go back to the spreadsheet to see the result.
Frequently Asked Questions
How do I delete a field from a PivotTable?
You cannot actually delete a field from a pivot table unless you do it from the source data range. However, you can uncheck the box of the field from the PivotTable Fields panel, and the field will not show up in the pivot table anymore.
How do I remove certain data from a PivotTable?
The data in a pivot table is not editable, so you cannot remove the data from the pivot table. However, you can use a filter to hide the data from a pivot table using slicers or some other method.
Why is calculated field greyed out?
Your calculated field is greyed out because you have added your pivot table to the data model. Data models don’t allow the calculated field to show up, as the user is expected to do the calculations through the data model instead of the pivot table. If you want the Calculated Field option to show up, recreate the pivot table, and do not check the Add this data to the Data Model box.
How do I delete totals in a PivotTable?
Select any cell of the pivot table and go to the Design tab. Find the Layout section, and you will see the Subtotals and Grand Totals options. You can hide the totals from those options. However, the totals cannot be deleted without deselecting the fields from the pivot table.
How do I delete all fields in a PivotTable?
You can manually deselect all the fields from the PivotTable Fields section, or go to the PivotTable Analyze tab. From there, find the Actions group, and select Clear > Clear All. The pivot table will be as good as new, and you can add and modify the fields as you like.
Wrapping Up
In this article, we have learned how to delete calculated field in pivot table. Kindly download the workbook used in this tutorial to practice the methods yourself. Consider bookmarking the site so that you can visit us again for more Excel tutorials. Leave a comment below with your suggestions or criticisms so that we can improve.
