Use Calculated Items in a Pivot Table
Adding a calculated item to your pivot table will add an additional item under a field.
So, if you have a column in your pivot cache called ‘timeframe’, the possible rows in that column could show ‘budget’ and ‘actual’. Then you could add a calculated item called ‘variance’ to show ‘actual – budget’.
Items exist under fields—if you want to add a whole new calculated field instead see this article.
Watch this video to see how it is done (skip to 2:30 to cut straight to the calculated item method!) Alternatively jump to the summary steps below. Get the file from the video here.
- Make sure your dataset is set up the way you need to get the calculated item you’re looking for
- After your table is set up, click on a pivot table column or row header (NOT the values section of the table) then from the Analyze menu click ‘Fields, Items, & Sets’–>Add Calculated Item
- In the dialogue box that appears choose the field whose items you want to base your new item on
- Then in the right box, choose fields and enter your expression (e.g. =Budget – Actual) NOTE: if you are using an item with a space or special character in it, be sure to surround the item name with single quotation marks like so (e.g. =’Budget Value’ – Actual)
- Click OK and Excel will place the new calculated item in your table for you
Benefits of Calculated Items in a Pivot Table
Calculated items can save a lot of messing around with your dataset in order to calculate variances (as shown in the video). They are also a shortcut to displaying combination ratios when you don’t mind showing multiple categories from your data that do not add up to 100%. I consider them as a ‘core’ skill to have if using pivot tables for data analysis.