How to Insert and Edit a Calculated Field in an Excel Pivot Table

Consider the following: a hypothetical biorefinery has a different bioreactors, producing both biofuel and value-added chemicals. The biorefinery uses microorganisms and fermentation routes. The number of times the equipment failed, and the number of times fermentation succeeded are recorded.

A picture of the source data is shown below:

Create your Pivot Table. (Click the following link: creating pivot tables).

Example 1 – Create a calculated field

Select a cell in the Pivot Table to activate Pivot Table Tools, in the Analyze tab.

Click the drop-down arrow next to Fields, Items, & Sets in Calculations.
Select Calculated Field.

Choose a name in the Insert Calculated Field dialog box. Here, ‘Reliable Bioreactors on Site’.

In the Formula field, enter the formula:

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

It will return the failure rate.

Click Add to see the new calculated field.
Click OK.

This is the output.

Go to Field Settings.
In Value Field Settings, choose Product.
Click OK.

Go back to Field Settings.

In the Value Field Settings dialog box, click Number Format.
Select Number and set the decimal places to 1.

Bioreactors A, H, and E had the highest failure rates.

Example 2 – Create a Calculated Field using the IF Statement

The original dataset was updated: The ‘Microorganism Used’ column was inserted in the source data. Microorganisms are used in the bioreactor to produce biofuel and other products.

Select a cell in the Pivot Table and right-click to Refresh, and see the updated fields.

Create a calculated field to find the number of times fermentation succeeded, irrespective of failure. If it succeeded more than 50 times, the growth of the organism reached the optimum level. If it is less than 50, the organism did not survive in the bioreactor.