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
Enter your email address below to download the sample 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 🙁
Excel Slicers for Fiscal Years
Norine Jansen
Hi Mynda,
I am using the calculated field formula to work out an easy percentage. B/(A-T). The formula is working perfectly for a subset of my data, but as soon I as I apply the formula to the full data set, I get full negative numbers (no longer percentages) and zero’s. How could I troubleshoot this issue.
Thank you
Norine
Mynda Treacy
Hi Norine,
Not sure if this is just a formatting issue or you’re expecting a different result. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Martin Bønnelykke Jensen
Hi ! 🙂
I wanna use what i learned from your video “12 Pro PivotTable Formatting Tricks = No more UGLY PivotTables!”
But I’m stuck at making the Gross Profit and Net profit sums :/
I have made my Pivot from the data model and i assume that’s why “Calculated item” is greyed out for me.
Is there any guide to calculate a new row when using power pivot and the data model?
Thanks 🙂
Mynda Treacy
Yes, Calculated Items are not available when you load the data to the Data Model. It’s much more complicated to create the P&L with the data model. I would re-create it with a regular PivotTable if possible.
Rajesh Rishi
Hi,
I am keeping track of my daily weight, caloric deficit and number of cigarettes I smoked (amongst various other parameters) as I move towards a healthier me:)
I have a pivot table that uses the MIN() function to show the minimum weight and caloric deficit I achieved each week which works fine. But when it comes to cigarettes I want to apply the SUM() function.
Since all values are entered in the same column (though with different variables) how do I use the IF function and the calculate formula like IF(X1=”Smokes”,SUM(Y1),Min(Y1)) to treat them differently?
Philip Treacy
Hi Rajesh,
The pivot table can automatically aggregate values with functions like MIN and SUM if it’s set up the right way. But I can’t see your data so can you please start a topic on the forum and attach your workbook.
Regards
Phil
Kedir
Excel online training course is vital for advanced skills on data management and analysis in all sectors. so I am highly Interested to learn more on this Excel inline course if freely support available .
Anuj Kumar
I get the error while creating this field,
“Multiple data of the same field are not working when pivot table report has calculated item”
Mynda Treacy
Hi Anuj,
It sounds like your PivotTable has multiple value fields that are the same. If so, then you can’t also add a calculated item. It’s just one of those obscure limitations. Instead you could do the calculation outside of the PivotTable, while referencing the PivotTable cells, or try Power Pivot, which doesn’t have these limitations, but will require you to have DAX knowledge.
Mynda
Mike K
Hi! I am very confused.
I have a data table from a survey with only 2 fields – Question Number and Rating. That is, I have a table that lists survey results – Column 1 is the Question Number and Column 2 is the Rating. I can create a simple pivot table with Question Number in the Row Labels section and Rating in the Column labels section and Count of Rating in the Values Section.
I want to be able to add a Calculated Item. Specifically, for each question, the sum of the ratings = 4 and 5, or some other combination
For example: Survey 1 Question 1 Rating = 4, Survey 2 Question 1 Rating = 5. In the Pivot Table, I get Question 1 and under Rating 4 I get 1 and under Rating 5 I get 1. I want to add a calculated field called “Rating 45” that would be the sum of Ratings 4 and 5 for Question 1 which would be 2.
However, when I attempt to add this calculated item I get a dialogue box with the following message: “If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item.”
What am I not understanding?
Catalin Bombea
Hi Mike,
As you mentioned, you have Rating in the Column labels section and Count of Rating in the Values Section. I think the message is very clear: “no fields can be used in the data area two or more times”, you are using the Rating field twice.
Instead of Count of Rating use Count of Question Number, will return the same thing and it will allow you to use Rating for the calculated column.
Tina
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
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: https://www.myonlinetraininghub.com/6-ways-to-fix-dates-formatted-as-text-in-excel
Mynda
Tina
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
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
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
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
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
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
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
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
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
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
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
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
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
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:
https://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
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
Cheers, Tristan. Glad you liked it.
Jon Peltier
Your formula, Initial/(Initial+Renewal), should be Renewal/(Initial+Renewal)
Mynda Treacy
Doh, it is I just wrote it out wrong. Thanks. All fixed.