Elevate Your Pivot Tables: The Secret to Adding a Calculated Item in Excel
I had a pivot table in Excel, and needed an additional column (not available in the original dataset) displayed that would auto-calculate dynamically. For this example, I wanted to show a “delta” between a Budget and Spend amount.
Here’s the raw data:
I created a simple pivot table:
The trick is to add a “calculated item” in the Excel pivot table
How to create a Calculated Item in an Excel pivot table
Click on the pivot table. When you do, the ribbon will change to show a section for “PivotTable Tools”.
In this new section is an option for Fields, Items, & Sets and under that is the option for Calculated Item:
⚠️Important: where to click first
Click on an item in the column headers. When you create the calculated item, the title of the window will indicate the field.
We do not want a calculated field for the row header (character name)
Instead, we do want a calculated field to appear in the column (category)
Next, put in the formula. Here, we’ll do Budget — Spend. Give it a name, and hit “Add”. Then “ok”.
Excel will add the newly created field in as a column:
You may want to hide the Grand Total column, as that’s irrelevant for this data.
You can even add a “Percentage” column:
But wait, the Grand Total is wrong for the Percent!
Unfortunately this is a known issue, as stated by Microsoft, even in the latest version of MS Office (365)
