Add, Modify or delete a calculated field in a pivot table in Excel

In this article, we will learn how to Add, Modify or delete a calculated field in a pivot table in Excel.

Scenario:

When working with pivot tables, it doesn't allow you to edit the data, the way we generally edit data in Excel worksheets. Why does it happen? It happens as pivot tables are default customized with formulas. If allows to

Edit a pivot table normally, its whole structure torn apart. So In this article we will learn how to add a field in a pivot table. And how to modify the added field in the pivot table. And then how to delete the added field in the pivot table. 

How to solve the problem?

For this problem, we will be using the pivot table and its one of the feature calculated field option in Excel. Calculated field option allows you to add, modify or delete a field in Excel. But you can only edit the fields which added using the calculated field. You can remove a field under the PivotTable fields panel. Below is the explanation on how to add a field in a pivot table and modify it with a formula. Then how to remove a field in the pivot table using an Example.

 

Example :

All of these might be confusing to understand. Let's understand how to add a field in a pivot table in Excel via following the steps explained below. Here we have data having product categories, with corresponding sales and profit for each product. 

We have data upto approximate 10000 rows and we will create a pivot table from this table in Excel. Follow the steps.

Select the whole data and click Insert -> Pivot table as marked yellow in the below snapshot.

When you click the pivot table option. A dialog box appears in front. The fields are filled by default and shown in the dialog as shown below. 

The pivot table will be added into a new worksheet which will have the data from sheet1! A1:C9995. Click OK

A new worksheet adjacent to the data sheet will be shown like this. Tick the boxes as marked to get the required fields in the pivot table. As you tick the box, one by one fields will be like as shown below.

This a pivot table showing the sum of sales and profit for each unique subcategory. Now i want to add one field having the difference of the sales amount - profit amount. For this we will use the calculated field option. Select any pivot table cell which will enable pivot table tools. Go to the Analyze -> Fields, Items & Sets -> Calculated field... option as shown below.

Insert Calculated Field dialog box appears in front. Name the new field name with Sum of (Sales - profit) and use the formula = Sales - profit using the Insert field option.

You will add the field using Insert field option shown in the list. As you can see a new field is added named Sum of (Sales - Profit) with corresponding result. 

Now If we want to edit the formula in the formula box of Calculated field option. 

 

How to remove a field in a pivot table?

Pivot table doesn't allow you to delete a column table. As you try to delete a column of a field, Excel throws an error saying "We can't make this change for the selected cells because it will affect a pivot table. Use the field list to change the report. If you are trying to insert or delete cells, move the PivotTable and try again.

You can remove or delete the new added field using the Calculated field option as shown below. Go to the Calculated field option. 

Select the list with the drop down option. 

Click delete to remove the field from the pivot table. 

Or you can delete the field from the PivotTableField panel. Untick the box from the right panel as shown in the below image. 

Your table will be free from the non required fields temporarily.

You can call back by just ticking the box again.

Hope this article about how to Add, Modify or delete a calculated field in a pivot table in Excel is explanatory. Find more articles on Pivot table and editing table here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

Related Articles:

subtotal grouped by dates in pivot table in Excel : find the subtotal of any field grouped by date values in pivot table using the GETPIVOTDATA function in Excel.

Pivot Table : Crunch your data numbers in a go using the PIVOT table tool in Excel.

Dynamic Pivot Table : Crunch your newly added data with old data numbers using the dynamic PIVOT table tool in Excel.

Show hide field header in the pivot table : Edit ( show / hide ) field header of the PIVOT table tool in Excel.

How to Refresh Pivot Charts : Refresh your PIVOT charts  in Excel to get the updated result without any problem.

Popular Articles:

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube