How to add a calculated field to a pivot table

Adding a calculated field to a pivot table is an alternative to adding a new column to the source data. It's especially useful when you don't have access to the source data, for example when you're connecting to a database.

Let’s 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. We could add a new column to the source data that calculates unit price by dividing Total Sales by Quantity, but we can also add a unit price as a calculated field.

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, type the name of the Field you’d like to create. In this case, we’ll 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.

To add fields to the formula, you can type the field names directly, double click a field name, or use the Insert Field button.

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.

When you click OK, the new field will be added to the field list and the values area, and we now see Unit Price in the pivot table.

Like regular fields, you can manually update the name of the field displayed in the pivot table. You can also change the number format to suit the data. In this case, we’ll use the Accounting format with 2 decimal places.

There are a few things you should know about Calculated Fields:

First, unlike a regular field, a calculated field can only appear in the values area. If you try to move a calculated field into a Row or Column label area, you’ll see a message to that effect.

Second, calculated fields can only summarize data using the SUM function. You’ll see that the Summarize Values By Menu is disabled on the ribbon. You are able to change the Summarize Values By Calculation using the Field Settings dialog box, but these settings have no effect - the values that appear in the pivot table won’t change.