use calculated ITEMS in pivot table

Pivot Table Calculated Items

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.

5-step Summary

  1. Make sure your dataset is set up the way you need to get the calculated item you’re looking for
  2. 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
  3. In the dialogue box that appears choose the field whose items you want to base your new item on
  4. 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)
  5. 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.

Share your thoughts below :)