Be Careful with Calculated Items in Pivot Tables

Let me warn you – Calculated Items in Pivot Tables can produce incorrect results! Calculated Items can be useful – in special circumstances. However, don’t use a Calculated Item to create a Quarterly Summary. It is too easy to accidentally “double” your sales! And, this will not earn you any special bonus. In fact, it may harm your reputation as an Excel expert. So be very careful before you create a Calculated Item.

Review my Excel Training Video on Grouping Data to see the preferred way to produce a Quarterly Summary in a Pivot Table.

Here are the steps to follow in this lesson:

You use a Formula to Calculate an Item from the values on one of the Fields in your Pivot Table.
Select one value in the Field where you want to create the Calculated Field.
In this lesson, I selected a Month and then from the Drop-Down Menu on the Toolbar I chose Formula – Calculated Item,
I named the Calculated Item Q1 (1st Quarter) and for the Formula I clicked = January+February+March. Click Add and then click OK.
As you can see, the new Q1 Item actually “doubled” the existing sales for January – March. That is not what you were looking for!
To remove the Calculated Item, select it and then from the drop-down select Formulas – Calculated Item. Highlight Q1 and click Delete – OK.
Review my video to see how to Group Data to produce the Quarterly Summary in the Pivot Table.