How to add a calculated field to a pivot table

Lets take a look.

This pivot table shows sales data by Product.

If we check the source data, we see that we have columns for Quantity and Total sales.

However, the data does not contain a unit price.

To add a calculated field to a pivot table, first, select any cell in the pivot table.

Then, on the Options tab of the PivotTable Tools ribbon, click Fields, Items & Sets.

From the menu, choose Calculated Field.

The Insert Calculated Field dialog box will be displayed.

First, bang out the name of the Field youd like to create.

In this case, well use the name Unit price.

Then tab into the formula box.

Unlike regular formulas, formulas for Calculated fields are always entered in this dialog box.

In this case, the formula we want is Total Sales divided by Quantity.

Note that Excel will automatically add single quotes around any field that contains spaces.

Like regular fields, you might manually update the name of the field displayed in the pivot table.

you’re free to also change the number format to suit the data.

In this case, well use the Accounting format with 2 decimal places.