How to Delete Calculated Field in Pivot Table in Excel

This guide will explain how to delete the calculated field in the pivot table in Excel.

Since it contains several built-in functions and tools, Excel is a popular tool to use for different situations and needs. Specifically, Excel is an excellent choice to store, organize, and manipulate data. 

Additionally, Excel has the pivot table, a powerful tool that allows us to easily manipulate, sort, organize, edit, format, and summarize data from a data set. And this makes it really simple to compare data and point out the trends and patterns found in the data set.

Essentially, there are many things we can do using the pivot table. For instance, we can group the data by date and time, sort the data, and even add calculated fields. So we can manipulate the values in a pivot table based on any condition or criteria we set.

However, what happens when we want to delete or remove an action we performed? Not to worry, it is really easy to undo or delete the fields or things we have done in a pivot table. In this case, we will focus on learning how to delete a calculated field in the pivot table after we have added it.

Let’s take a sample scenario wherein we need to delete a calculated field in the pivot table in Excel.

Suppose you have a sales report based on three different stores. So it contains the store, the number of sales made by each store, and the number of returns done in each store. And let’s say we added a calculated field in our pivot table.

After a while, we realized we do not need the calculated field in our pivot table, so we want to delete it. To do this, we simply go to the calculated field window and delete the inserted column from our pivot table.

Great! Now we can move on and dive into a real example of deleting a calculated field in a pivot table in Excel.

 

A Real Example of Deleting Calculate Field in Pivot Table in Excel

Let’s say we have a data set containing three columns. Firstly, we have the columns which have the stores. Then, we have a second column having the number of sales for each store. Lastly, the third column has the number of returns made in each store. So our initial data set would look like this:

Initial data set

 

And we want to learn how to delete a calculated field in a pivot table. But, we need to learn how to add a calculated field in a pivot table in the first place. Luckily, this is a really simple process. In this case, a calculated field would display the total sum of the sum of sales and the sum of returns columns.

Firstly, we will convert our data set to a pivot table. Then, we will add all the columns as fields in our pivot table. So this will give us a pivot table containing the stores, the sum of sales, the sum of returns, and the grand total of each sum. 

Next, we can now add a calculated field to show the total sum of the sum of sales and sum of returns columns. To do this. We will select any value in our pivot table. Then, we will head over to the PivotTable Analyze tab containing the fields, items, and sets tool. 

In this tool, we can click the calculated field to open the calculated field window. In the calculated field window, we will input the necessary details. 

Firstly, we will type in a name for the calculated field. In this case, we will name the column, Total Transactions. Then, we will input a formula that will calculate the total sum of the sum of sales and the sum of returns. In this case, the formula would be = sales + returns

Once we have inputted all the details, we will add and apply the changes to our pivot table. So our pivot table will now have a calculated field added. 

Next, let’s remove or delete the calculated field we added to our pivot table. To do this, we simply have to retrace our steps. So we will go back to the PivotTable Analyze tab and select once again the Calculated Field tool.

In the calculated field window, we will simply select the calculated field we added and click Delete. And this will easily remove the calculated field from our pivot table.

So our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can discuss the steps of how to delete a calculated field in a pivot table in Excel.

 

How to Delete Calculate Field in Pivot Table in Excel

In this section, we will explain the step-by-step process of how to delete a calculated field in a pivot table in Excel. Furthermore, each step has detailed instructions and pictures for you to easily follow.

To apply this method, simply follow the steps below.

1. Firstly, we will convert our data set to a pivot table. To do this, we will select the entire data set and go to the Insert tab. Then, we will click PivotTable.

Convert to pivot table

 

2. Secondly, we will choose the Existing Worksheet option in the Create PivotTable window and input an empty cell location to display our pivot table. Lastly, we will click OK to apply the changes.

Display pivot table

 

3. Thirdly, we will select all the columns to add as fields to our pivot table. So we will drag Store to the Rows box. Then, we will drag Sales and Returns to the Values box.

Delete Calculated Field in Pivot Table in Excel

 

4. Next, we can now add a calculated field to our pivot table. To do this, we will select any value or cell in our pivot table. Then, we will go to the PivotTable Analyze tab and click Fields, Items, & Sets. Lastly, we will click Calculated Field in the dropdown menu.

Delete Calculated Field in Pivot Table in Excel

 

5. Once the Insert Calculated Field opens, we will type in the necessary details needed for the calculated field. Firstly, we will type in “Total Transactions” as the Name for our calculated field.

Then, we will input the formula “= Sales + Returns” in the Formula tab. Lastly, we will select Add and click OK to apply all the changes.

Delete Calculated Field in Pivot Table in Excel

 

6. Now we have successfully added a calculated field in our pivot table.

Delete Calculated Field in Pivot Table in Excel

 

7. Afterward, let’s delete or remove the added calculated field in our pivot table. To do this, we will select any value or cell in our pivot table and go back to the PivotTable Analyze tab.

Then, we will select once again the Fields, Items, & Sets tool. Lastly, we will click the Calculated Field option in the dropdown menu.

Delete Calculated Field in Pivot Table in Excel

 

8. In the Insert Calculated Field window, we will select Total Transactions in the dropdown menu. Then, we will click the Delete button. Lastly, we will click OK to apply the changes.

Delete Calculated Field in Pivot Table in Excel

 

9. And tada! We have successfully deleted a calculated field in a pivot table in Excel.

Delete Calculated Field in Pivot Table in Excel

 

And that’s pretty much it! We have successfully explained how to delete a calculated field in a pivot table in Excel. Now you can apply this simple and easy method whenever you need to delete or remove a calculated field in your pivot table.

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like