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[1] and GL could become Source[2].

## 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 🙁

## Please Share

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.

Tina says

Hi Mynda

Thanks a lot for sharing this 🙂 Great stuff.

But have a problem with grouping ex date when using calculated item – it does not work properly.

Using Excel 2013 – is that the problem ?

Just keep on 🙂

Tina

Mynda Treacy says

Hi Tina,

I’m not sure what you mean by ‘ex date’. If you mean you get an error when you try to right-click the date field in the PivotTable and choose ‘Group’, then that will be because your dates aren’t in a date format, they’re probably text. If so, then you need to fix the format so it’s in a date format that Excel recognises. See here: http://www.myonlinetraininghub.com/6-ways-to-fix-dates-formatted-as-text-in-excel

Mynda

Tina says

Hi Mynda

Sorry but thats not the problem – my dates are correct dates 😉

I cannot choose calculated item when my dates are grouped by month/year – so I have to ungroup them before I make the cal.item.

Then I want to group them again afterwords – but cannot without marking all january dates, and rename the group1 to January, marking all february date, and rename the group2 to February – and so on.

What do you think I do wrong ?

Thanks and Regards

Tina

Mynda Treacy says

Hi Tina,

Thanks for explaining more clearly. You cannot use grouped dates and calculated items in the same PivotTable.

The workaround is to put your date grouping into your source data. i.e. add a column to your source data for the month and use that field in your PivotTable instead of the Date field.

I recommend formatting your months in numeric format e.g. 201601 so that the PivotTable sorts them correctly. If you type in the month name then it will sort them alphabetically.

Mynda

Claudia F says

Hi Mynda,

Thanks for sharing this..

When I insert a calculated item, it is automatically added into all the PivotTables In the report, do yo know how can I change this? I mean, to have the calculated item in only one PivotTable, so I can summarise initial values in the other ones. Thanks

Mynda Treacy says

Hi Claudia,

I can’t replicate this. When I add a calculated field it’s added to the selected PivotTable, not all of them. It appears in the field list for all PivotTables, which is what you’d expect since you ‘added a field’, but it doesn’t automatically get put in the values area of all PivotTables.

Can you please share the file on our Excel Forum and specify the steps you’re taking to get the calculated field automatically added to all PivotTable values areas?

Mynda

Mark Maher says

Hi Mynda,

I have a Pivot Table in Excel 2010 that shows a row for each pupil and the columns show the percentage of SSA grades (1s to 5s with 1s being outstanding and 5s not so good) they have got in a week of lessons, e.g.

1s 2s 3s 4s 5s Calc Item (Total 1-5) Calc Item (1s & 2s) Calc Item (4s & 5s)

Joe Bloggs 50% 50% 100% 100%

John Smith 50% 50% 100% 100%

To get the % to work I got rid of the Pivot Table default Grand Total and created my own Calculated Item manual total.

Then after right clicking a value in the Pivot Table, I chose to Show Values as %Of and my Calc Item (Total 1-5).

I’d like to be able to sort by the either Cal Item (1s & 2s) or the Calc Item (4s & 5s) column but I often find that the sorting is mostly correct but has some incorrectly ordered rows, e.g. below. To sort the pivot table by one of my Calc Items, I right click the first value in the column and click sort largest to smallest. It seems to sort correctly if I sort by the last Calc Item though but not the Calc Item (1s & 2s).

100.0%

53.3%

26.7%

20.0%

20.0%

13.3%

13.3%

13.3%

13.3%

13.3%

13.3%

13.3%

13.3%

13.3%

13.3%

13.3%

13.3%

7.1%

6.7%

6.7%

6.7%

6.7%

6.7%

6.7%

7.1%

11.1%

6.7%

Any help gladly received.

All the best

Mark

Mynda Treacy says

Hi Mark,

Are you able to post this question on our Excel Forum and upload your file with the data anonymised so we can take a look at what you’re trying to do in context?

Mynda

Estella Foo says

hi Mynda,

Thank you so much as this feature is really helpful to save a lot of time.

One of the questions if I want to change the formula, I have to delete the column or clid on to “solve order” to delete the formula and recreate the new formula again, right.

Estella

Mynda Treacy says

Hi Estella,

To change the formula just repeat the steps for inserting a calculated item and in the ‘Insert calculated item’ dialog box select it from the ‘Name’ list and then edit as required.

Mynda

Jane says

Hi Mynda,

I have problems losing my calculated items every time i update my source data and refresh the pivot table. How should i do to avoid this happen again.

Really wish to get your advice.

Mynda Treacy says

Hi Jane,

Are the column header names changing when you refresh the data? Have you renamed the fields in the PivotTable? Usually it’s a renaming issue.

Mynda

Ted says

I am known in my office for being able to break anything involved with programming.

So, in good humor, knowing that the boss never looks at the details, I changed the formula to read

=If(Renewal=0,1,Renewal/(Initial+Renewal)). I then changed Renewals = 0 in several places.

So, in Regions 2-10, it showed %Renewal=100% below Renewal 0, as it should. But even after I refreshed the pivot table, the formula in Region 1 would not change. It remained honest. It did not matter where in the pivot table where I placed my cursor to open the “Fields, Items, & Sets” menu.

In all seriousness, this could be a real problem where the analyst thinks the formula is changed everywhere.

I did insert the pivot table at cell F2 instead of F1 as you did, but that shouldn’t make a difference.

Mynda Treacy says

Hi Ted,

Correct, the formulas in calculated items are not very robust so it’s best to hide the sheets containing those PivotTables to avoid errors.

Mynda

Lee Arndt says

I’m trying to add multiple columns on a pivot table and haven’t been able to figure it out yet. My pivot table has column labels “yr” and then “mmyy” so I see the individual months. I want to total all of the amounts for year 2014 and then 2015 so I can do a variance. Any help would be appreciated!

Lee

Mynda Treacy says

Hi Lee,

Without seeing your file it’s difficult to know the problem. e.g. the dates in your YR and MMMYY columns may not be in an ideal format.

See if this tutorial helps:

http://www.myonlinetraininghub.com/excel-pivottables-year-on-year-change

If not please send us your file via the Help Desk so we can see what you’re working with.

Kind regards,

Mynda

Tristan T says

Excellent blog and article Mynda!! You’ve given me a few tips and tricks I didn’t know existed and will be very useful for me 🙂

Mynda Treacy says

Cheers, Tristan. Glad you liked it.

Jon Peltier says

Your formula, Initial/(Initial+Renewal), should be Renewal/(Initial+Renewal)

Mynda Treacy says

Doh, it is I just wrote it out wrong. Thanks. All fixed.