Excel Pivot Table Calculated Field (How to Insert & Edit)

When you are using Pivot Tables, you can use calculated fields as a way of making your own custom calculations. In this article, we shall learn different techniques of Excel pivot table calculated field.

Pivot Table Reports already provide a set of summary calculations and function options, but sometimes this is not enough for the unique needs that you may have. So, you can add your own needed custom-made formulas using the original fields in the Pivot Table, and have the results of your formulas or calculated fields appear in the Pivot Table.

What do I mean by this? Well, let’s start with a simple example to master the basics of creating calculated fields and then we can build up from there in follow-up posts.

How to Insert Calculated Field in Pivot table?

A hypothetical biorefinery has a number of different bioreactors on site, producing both biofuel and value-added chemicals. The biorefinery is using microorganisms and the fermentation route in order to produce the desired outputs.

Read More: How to Create Pivot Table Data Model in Excel 2013

The technicians record the number of times the equipment failed, and the number of times the fermentation runs succeeded. We will show the failure rate of these bioreactors using a calculated field. The reason we would want to show something like the failure rate is, our procurement manager wants to know which equipment suppliers to order bioreactors from, in future, and he wants to see which of the bioreactors on the site were the most reliable.

Picture of the source data is shown below:

Calculated Field, Pivot Table, Data

Firstly, you should have your Pivot Table already setup. If you are unsure of how to do this, please visit this post on creating pivot tables in a few easy steps.

Pivot Table, Excel

Make a simple calculate field

Now let’s get to the fun part.

1) With a cell in the Pivot Table selected, in order to activate the context-sensitive Pivot Table Tools, Analyze tab.

Calculated Field, Pivot Table

2) Click on the drop-down arrow next to Fields, Items, & Sets in the Calculations Group and select the option Calculated Field.

How to Insert Calculated Field in Pivot table

3) Firstly, we choose a name in the Insert Calculated Field dialog box that pops up, and this name can be something like ‘Failure Rate of Bioreactors’ / ‘Most Reliable Bioreactors on Site’, try to make the name as descriptive as possible, in this case, we’ll choose ‘Reliable Bioreactors on Site’.

Calculated Field, Pivot Table

4) In the Formula field, instead of zero (make sure the equals is still there), open a parenthesis and then double-click the ‘Times Failed’ field from the Fields field to add it, and then divide this by the ‘Number of Times Succeeded with the fermentation run’, close parenthesis and then multiply by 100.

Then our formula will be:

= (‘Times Failed’/ ‘Number of Times Succeeded with the fermentation run’)*100

This formula will return our rate of failure.

Calculated Field, Pivot Table

5) And then click Add to see the new calculated field and then click OK.

Calculated Field, Pivot Table

6) You should end up with something like the following:

Calculated Field, Pivot Table

7) Go to Field Settings and from the Value Field Settings dialog box, choose Product and click OK.

Calculated Field, Pivot Table

8) Go back to Field Settings and the Value Field Settings dialog box should be shown.

Calculated Field, Pivot Table

9) Click on the Number Format, select Number (already selected) and then set the decimal places to 1, and then you have it.

Calculated Field, Pivot Table

10) So, we can see from our data that Bioreactor A, H, and E had the highest failure rates and the company should not be ordering equipment from those suppliers in future.

Read More: Solving equations in Excel (polynomial, cubic, quadratic, & linear)

Pivot Table Calculated Field with IF Statement

Now, let’s see how to make a more complex calculated field.

Say our original data set has been updated to include a ‘Microorganism Used’ column in the source data, this is the microorganism in the bioreactor that is responsible for producing the biofuel and other products.

Calculated Field, Pivot Table

We can select a cell in the Pivot Table and right-click to Refresh, in order to see the updated fields from the source data.

Calculated Field, Pivot Table

We want a more complex calculated field that looks at the number of times the fermentation run succeeded, irrespective of the failure. If the fermentation run succeeded more than 50 times, it means the growth of the organism is fine and it’s reached the optimum growth. If the number of times succeeded is less than 50, it means that the growth of the actual organism was problematic and the organism did not survive in the bioreactor.

Read More: How to Create an Average Calculated Field in Excel Pivot Table

We then follow the same steps to create a calculated field as above, but this time we do the following:

1) We give the Calculated Field the name ‘Survival of the organism’ as shown, and for the formula, we use an IF function based on the Number of Times Succeeded with the fermentation run Column
= IF( ‘Number of Times Succeeded with the fermentation Run’>=50,100%,0)

We click Add and then OK.

Pivot Table Calculated Field IF Statement

2) We now should have the following as shown below:

Calculated Field, Pivot Table

3) We then go to Field Settings in the Active Field Group.

Calculated Field, Pivot Table

4) Choose Number Format from the Value Field Settings dialog box.

Calculated Field, Pivot Table

5) Click on Percentage and choose 0 decimal places, click OK and then OK again to close both dialog boxes, and then the values are in percentages instead of 1s and 0s.

Calculated Field, Pivot Table

6) There you have it, we can conclude that even though the bioreactor was failing in Bioreactor A’s case, the growth of the actual organism was fine.

Calculated Field, Pivot Table

Read More: How to rename a default group name in Pivot Table

Problems with Excel Pivot Table Calculated Field Tools

Totals are usually always a problem with Calculated Fields since Excel often does not Total them correctly. The best way to work around this is to hide the Totals column since it is just confusing and can make others looking at your Pivot Table, think you did something wrong. So, let’s hide the Totals Columns and Rows.

1) Right-click a cell in the Pivot Table and Choose PivotTable Options as shown.

Calculated Field, Pivot Table

2) In the PivotTable Options dialog box, in the Totals and Filters tab, Make sure Show grand totals for rows and Show grand totals for columns is unchecked.

Calculated Field, Pivot Table

3) The Incorrect Totals are then not shown.

Calculated Field, Pivot Table

Another common problem is the name of the field at times since Excel often adds the “sum” or “product” text as a part of the title.

Calculated Field, Pivot Table

1) In the ∑ Values of the PivotTable Fields dialog box, click on the drop-down arrow next to Product of Reliable Bioreactors on Site.

Calculated Field, Pivot Table

2) Go to Value Field Settings…………

Calculated Field, Pivot Table

3) Change the Custom Name in the Value Field Settings and remove “Product” and type Reliable Bioreactors instead as shown.

Calculated Field, Pivot Table

4) Click OK.

Calculated Field, Pivot Table

How to Edit Calculated Field?

So, what happens when we want to modify or edit a calculated field that we already created for the Pivot Table, well we can easily do this.

Read More: Excel Pivot Table Terminology

1) Make sure you have a cell in the Pivot Table selected to activate the context-sensitive PivotTable Tools Analyze tab. In the Calculations Group, choose Fields, Items & Sets and select Calculated Field as shown.

Calculated Field, Pivot Table

2) Click on the drop-down arrow next to Name and select one of the calculated fields you created previously.

 

Calculated Field, Pivot Table

3) In this case, we will select Survival of the organism calculated field we created recently.

How to Edit Calculated Field

4) Then adjust the formula, in this case, we are going to enter this modified formula:

= IF(‘Number of Times Succeeded with the fermentation Run’ >=105,100%,0)

So, we are changing 50 to 105

5) Then click Modify and OK.

Calculated Field, Pivot Table

6) This is what the resultant Pivot Table looks like as a result of modifying the formula – we can see Bioreactor A is no longer included in the good growth rate, as a result of the change in the formula.

Calculated Field, Pivot Table

How to Delete a Calculated Field in a Pivot Table?

Deleting a calculated field in a Pivot Table is really easy.

1) In the Analyze tab, of the PivotTable Tools, select Fields, Items & Sets drop down and Calculated Field as shown.

Calculated Field, Pivot Table

2) Click the drop-down arrow next to Name and choose the name of the calculated field you created that you now want to delete. In this case Survival of the organism.

Calculated Field, Pivot Table

Calculated Field, Pivot Table

3) Click Delete as shown and OK.

How to Delete a Calculated Field in a Pivot Table

4) Then Survival of the organism is deleted completely in both the Calculated Field and Pivot Table Fields List.

Calculated Field, Pivot Table

Useful Pivot Table related Links from ExcelDemy

How to Use Pivot Table Data in Excel Formulas

How to Create Pivot Tables that Provide Meaningful Data Analysis & Insights [Dream Analysis]

8 Excel Pivot Table Examples – How to Make a PivotTable!

Conclusion

Calculated fields are extremely useful features when you need extra customization of the standard Pivot Table options. Calculated fields can be used for many unique calculations, which are not natively in Excel, but that may be required by your particular industry. Proceed with caution with TOTALS in the calculated fields options, since more often than not they need to be turned off, in order to not create confusion. Please feel free to comment and tell us what you have used calculated fields for and in which industry.

Useful links

Learn the basics of Pivot Tables

Download Working File

Calculated-Fields-Sample-WorkSheet.xlsx


Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

      Leave a reply