Inserting Excel PivotTable Calculated Items is a great way to analyse your data and automatically incorporate that analysis in your PivotTables.
Another way to think of them is the ability to add a new item to your report based on a formula which uses other items in the column. You can then include this new item in any PivotTable report, as though it were part of your source data.
Download the Workbook
Watch the Video
Visualising a Calculated Item
I like to differentiate a Calculated Field from a Calculated Item by picturing where they would appear if they were part of your source data:
- PivotTable Calculated Fields are the same as columns in your source data
- PivotTable Calculated Items are the same as the different items inside those columns, or another way to think of them is to imagine they are the same as the items you see in the list when you click on the filter drop down buttons.
Inserting Calculated Items
The PivotTable below contains sales by region split by Type: Initial Sales and Renewal Sales. We’ll add a Calculated Item for the percentage Renewal Sales are of the total sales.
The first thing you must do is select a cell in the PivotTable rows or columns area (i.e. any of the cells not containing numbers), and if you choose a cell in the row/column where you want your item added it’ll save you a step. We’re adding a new Type so I’ll select any cell in column G of the PivotTable.
Then on the PivotTable Options tab (Excel 2010), or PivotTable Analyze tab (Excel 2013) > Fields, Items & Sets > Calculated Item. This opens the dialog box below:
- Give my new Item a name. I’ll call it % Renewals
- My formula for % Renewals will be:
= IF(Renewal=0, 0, Renewal / (Initial + Renewal))
Translated to English reads: IF the Renewal value = 0, then return zero, otherwise calculate Renewal divided by the sum of Initial + Renewal.
The reason I added the IF is to avoid any #DIV! errors should the Renewal value ever be zero.
In the Insert Calculated Item dialog box my formula it looks like this:
When you add the new Calculated Item it’s automatically included in your PivotTable:
Formatting Calculated Items
Now we have a dilemma; we need the % renewals formatted with the percentage sign but applying a number format to the field (via the Value Field Settings menu) would mean all values are displayed as a %, so instead we need to apply the formatting just to the cells for the % renewals. Don't forget to make sure the ‘Preserve cell formatting on refresh’ is checked.
The quickest way to do this is to switch the order for Region and Type so all the Types are grouped together. Simply drag Type above Region in the field list:
Then we can select all the cells we want to format and apply the formatting using the Format Cells menu on the Home tab, or CTRL+SHIFT+%:
Then you can switch your Region and Type columns back and the formatting sticks:
Tip: notice in the formula bar that the formula for the calculated item is visible. You can also edit this formula just as you would any other, but beware, it will only edit it for the active cell and all other formulas will remain as they were.
Calculated Item Formula Rules
Above is just one example of inserting a calculated item, however applying the rules below will open up many more uses for them:
Operators: you can use operators and expressions as you do in other worksheet formulas (+ - * / ^ < >).
Constants: you can use constants and refer to data from the report, but you cannot use cell references or defined names.
Functions: you can’t use worksheet functions that require cell references or defined names as arguments, and you can’t use array functions.
Not compatible with OLAP PivotTables - You can only insert calculated items 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.
Calculated Items Example 2 – Reconciling
Last week I was asked if there was an easy way to reconcile a General Ledger to a P&L. The answer is yes, a PivotTable is a great tool to use. Let’s take a look; here is our data which contains the amounts by GL Account for two sources: the General Ledger (GL) and the P&L:
I’ll insert a PivotTable that summarises the Amounts by GL Account and then we can compare the GL balances to the P&L balances:
Now I can insert a calculated item (called Difference) to subtract P&L from GL:
Modifying and Deleting Calculated Items
If you want to edit or delete a calculated item simply go back to the PivotTable Options/Analyze menu > Insert Calculated Item > click on the drop down list in the Name field and select the item you want to delete or edit:
From here you can make your changes to the formula or click the ‘Delete’ button to get rid of it altogether.
Referencing Items by Position
Another way you can reference items in the formula is by their position in the PivotTable. For example, GL is the first item and P&L is the second, therefore we could write the formula like this:
Items referred to in this way can change whenever the positions of items change or different items are displayed or hidden. Note: hidden items are not counted in this index.
For example, if you moved the order of the items through sorting, P&L could become Source and GL could become Source.
Referencing by field name
And in the event that there might be a name conflict (caused by items in different fields having the same name) resulting in #NAME? errors, you can reference the items by their field and item name like so:
Calculated Items Solve Order
If you have multiple calculated items you can alter the order in which they are calculated by rearranging the Solve Order (PivotTable tools > Options/Analyze > Solve Order):
Calculated Item Gotchas
Unfortunately once you insert a calculated item into your PivotTable you can no longer summarise your values by Average, Standard Deviation or Variances 🙁
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.