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:
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:
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.
After clicking OK, your calculated field will now show up on the pivot table:
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:
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:
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.