Have you ever tried to insert a calculated field in a pivot table, only to find the calculation is not performing as expected? I imagine a lot of people work around this by trial-and-error, but in the hopes of expediting others’ spreadsheet troubleshooting, here is a list of 3 golden rules for working with calculated fields in pivot tables.
But first! Instead of rehashing the basics of inserting a calculated field, here’s a quick (1.5 minute) video tutorial by tinawn68 that covers just the basics. How to create a calculated field in a pivot table.
Knowing the basics is a great start, but due to problems in the implementation of Excel’s calculated fields, I’ve developed these 3 golden rules for working with pivot tables and calculated fields. Commit them to memory, and you will save yourself a lot of wasted time trying to build a calculated field that just can’t be done.
Rule #1 – You cannot use named ranges, cell references, or arrays in the calculation. So, if you have a cell named growth_pct, you cannot use it in the calculated field by =sales * growth_pct. You could write =sales * 5%, but maybe you want to have the option to vary the growth rate in a single cell in the workbook, and not the formula. The best work-around for a single cell reference is to include it with the pivot table data. This can be accomplished by adding a column to your data and populating the entire column with “=growth_pct” (in this example).
Rule #2 – You cannot use COUNT, AVERAGE, and other functions within the calculated field. Well, you CAN, but you won’t get what you want out of it. The reason for this is the fact that the pivot table ALWAYS aggregates first, and then applies the calculated function. See the following example of some calculated fields dealing with zip code data.
Each last name has an associated 4 records. For simplicity, I have used a single zip code of 27514. To the far right, you can see the actual sum and count of the zip code data. To illustrate the problems, I am using Min, as this will show the smallest of the calculated fields, Average(Zip) and Count(Zip).
Even just by looking at the Average(Zip), you can see that Excel first aggregated the 4 records of data, summing zip code to 110,056 (=4 x 27,514), then taking the AVERAGE of the SINGLE NUMBER! Also, when the calculated field is Count(Zip), it aggregates the records to a SINGLE data point and then counts that, so the output is 1!
So, the moral is, don’t use functions inside calculated fields. As it turns out, calculated fields are really only useful for easy calculations like SALES * 10%.
Rule #3 – Subtotals and Totals don’t work either! Take the following example.
Bringing our attention to the spring sales, we see that the subtotal of $330 is much greater than the actual total of $100 + $20. The reason for this is ‘Sum of cost’ is a calculated field with the formula price * quantity. What Excel does is aggregate price and quantity first and then apply the calculated formula. So, you get 30 x 11 = $330.
Hopefully these 3 golden rules of calculated fields will help with what not to do. For more on pivot tables, check out this related checklist.
Obviously working with pivot tables, one can encounter a lot of problems. Being an Excel expert for over 7 years, I’ve probably seen it all. Please contact me at firstname.lastname@example.org, or use our contact form here with any further questions. Thanks!