Excel PivotTable Calculated Fields are easy to add but there are a couple of ‘gotchas’ you should be aware of.
Calculated Field Definition
Microsoft Definition: A Calculated Field is a user-defined field in a PivotTable that can perform calculations using the contents of other fields in the PivotTable. Calculated field formulas can refer to one or more fields. Blah, blah, blah…
Is your head hurting already? Mine is. Let’s look at an example of when you might use a Calculated Field because it’s really not that complicated; below is my Actual and Budget sales data which is begging to be Pivoted. I’ll create an Actual vs Budget variance report. But wait, I don’t have a ‘Variance’ column:
I could add another column in the source data above and calculate my variance for each row of data and then sum it up in my PivotTable, or … drum roll….I could just add a Calculated Field to my PivotTable.
Tip: Here’s how I visualise Calculated Fields; Your PivotTable Field List is simply a list of all of the column labels in your source data (as you can see below), so I like to think of adding a Calculated Field as an alternative to adding another column in your source data.
Whatever you can do in a column in your source data you can do in a Calculated Field…well almost. Don’t hold me to that as I haven’t tested every scenario!
Calculated Fields vs Formulas in the Source Data
Quite simply, formulas in your source data are Full Fat Formulas and formulas in Calculated Fields are Fat Free Formulas!
Full Fat Formulas
Sure it’s easier to just add a simple =E2-D2 formula in cell F2 of the source data then copy it down column F and let the PivotTable add it up, however it’s not the most efficient use of your Excel resources.
You see, if you add that simple formula to column F it gets calculated for every row, and in large files that can result in your file having a MB blowout which means you could end up with a FAT FILE.
Fat Free Formulas
Whereas if you add that calculation to your PivotTable as a Calculated Field Excel only needs to make that calculation when the PivotTable calls for it, and only to the level of detail seen in the PivotTable. That’s why I call them ‘Fat Free Formulas’.
So if your file needs to go on a crash diet simply feed it some Fat Free Calculated Field Formulas and get rid of all the Full Fat Formulas in your source data.
How to add a Calculated Field
Before you can add a Calculated Field you must actually insert a PivotTable, and then with any cell in the PivotTable selected;
Excel 2007 & 2010: Go to the PivotTable Tools: Options tab > Fields, Items & Sets > Calculated Field
Excel 2013: Go to the PivotTable Tools: Analyse tab > Fields, Items & Sets > Calculated Field
This opens the Insert Calculated Field dialog box:
- Give your new field a name. I’ll call mine ‘Variance’.
- Insert your formula – you can use functions, operators and expressions just like you do in worksheet formulas, with a few exceptions – see the ‘Gotchas’ below.
- Click OK (you can click ‘Add’ first if you want but it isn’t necessary), your new field will be in the Field list and in your PivotTable:
You can now use that field in your PivotTable just as you would any other field in the field list.
Modifying and Deleting Calculated Fields
To modify a Calculated Field simply open the Calculated Field dialog box again (remember, select any cell in the PivotTable then on the Options/Analyze tab > Fields, Items & Sets > Calculated Field)
Choose the field from the Name list:
The dialog box ‘Add’ button will become ‘Modify’ and you can make your changes to the formula and then press OK:
Alternatively you can Delete the Calculated Field altogether with the Delete button. Note: this removes it from your PivotTable and the field list so it will no longer be available.
Calculated Field Gotchas
Not compatible with OLAP PivotTables - You can only insert calculated fields in PivotTables created with non-OLAP data sources. For most of us that’s ok since data in an Excel worksheet is a non-OLAP data source.
Can’t use cell references or defined names - Calculated Fields can use operators and expressions (+ - * / > < ^) as you do in other worksheet formulas. You can use constants and refer to data from the report, but you cannot use cell references or defined names.
You can use Functions but you cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions. That means you can use IF, IFERROR etc. but not SUMIFS, COUNTIFS etc. because they require a cell reference for their arguments.
Sometimes the totals will be wonky - Totals aren’t actually totals; every cell you see in the values area for a Calculated Field is the result of the formula. It’s like having the same formula in every cell in the column irrespective of whether the row is a totals row or not.
Let’s look at an example. In column N I have a new Calculated Field for my forecast:
The Calculated Field formula is:
=IF(Actual Sales >1,000,000, Actual Sales * 1.05, Actual Sales * 1.10)
Which says, if my Actual Sales for a category is greater than $1m then forecast growth at 5%, otherwise forecast growth at 10%. Probably wouldn’t happen in the real world but let’s go with it.
In the image above we can see column N contains the results of my new calculated field (Forecast) and in column P I have the same formula entered in the cells.
The results are the same until we get to the Grand Total row; cell P12 contains the sum of the values above it, but my calculated field cell N12 still uses the formula even though it’s supposed to be a total.
What to do? You have a couple of options:
- Hide the grand total row.
- Do the calculation outside of the PivotTable.
- Use a Full Fat Formula in your source data and let the PivotTable add them up.
If you can’t live with the above limitations then check out Power Pivot as it can do everything a PivotTable can but isn’t as restricted by most of the above.
And if you've always wanted to master PivotTables check out our Excel Pivot Table course.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.