How to Add Calculated Fields to a Pivot Table

Pivot tables do a good job of summarizing your data and showing you the totals based on various splits and categories. In some cases, however, you may want to add calculated fields to your pivot table if it doesn’t go far enough in analyzing your data. Below, I’ll show you how you can do that.

Adding a calculated field

In the pivot table below, there is a break down by sales and cost by order status:

Pivot table summarizing sales and cost by order status.

Suppose you wanted to calculate the margin to show how much you are making per order. Since in this data set a margin field doesn’t exist, it needs to be added as a calculated field. To do that, click anywhere on your pivot table and then select the PivotTable Analyze tab on the ribbon. There, you’ll see an option for Fields, Items & Sets. Click on that, and you’ll see an option to add a Calculated Field:

Adding a calculated field in Excel.

Creating the formula

In the next step, you can name your field as well as set up the formula to determine what it should be calculating. You can use the fields in your pivot table and insert them into the formula. To do this, just double-click on any one of the fields. This is a better option than simply typing in the field because if you miss a space or enter it differently, the formula will not compute.

In the case of a margin calculation where we want to know how much of revenue is remaining after costs, the formula is just sales minus cost.

Creating a calculated field for the margin.

After clicking OK, your calculated field will now show up on the pivot table:

A pivot table after adding a calculated field.

Ideally, we would also have a field that shows margin as a percentage to help add context. To do this, I can add another calculated field. For this formula, all I need to do is take the recently created margin field and divide it by sales:

Creating a calculated field in a pivot table to determine the margin percentage.

You could potentially do this all within a single calculated field. But the point here is to illustrate that you can use a calculated field within another calculated field. In some cases, it can make it easier to break them out separately. And this also gives you more flexibility in how you want to present the data.

After converting the format to a percentage, now I see a margin % in the pivot table:

Pivot table showing multiple calculated fields.

If you liked this post on How to Add Calculated Fields to a Pivot Table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Comments are closed.