Pivot Table | Chapter 7| Calculated Field Calculated Item

Calculated Field And Calculated Item

Creating Calculated Field and Calculated Item is bit confusing sometimes. 

We deal with Calculated Field and Calculated Item once the Pivot Table is created. 

Download the sheet used with calculation.....Here

Calculated Field - A new field created from other fields in the Pivot table. Simply speaking a column is calculated from other columns in Pivot table. After creation it resides in the value area of the pivot table. One cannot shift this field to other areas. Alternatively and much easier is to create a column and add the same to the Pivot Table. But when we deal with any external database, Calculated Field is handy and only option left.

Calculated Item - Similarly a new row is created instead of column with the help of Calculated Item option.This new row resides in the Column area, Filter area and Rows area in the pivot table. And it cannot resides in the value area of the Pivot Table.

The formula we use to create the Calculated Field and Calculated Item is not the standard excel formula. Of course the math and logic is same. 

We will understand both with the help of a video.

Explanations:

• After ungrouping the OrderDate it is advisable to select the region's cell (say central) before writing the formula in case of Calculated Item. (Though it is not full proof but I find it convenient)

Some important tips on Pivot Table
Pivot Table has some limitations, it is flexible enough but you cannot add new rows or columns or enter formulas within the Pivot Table. It is true you can enter column or row with the help of Calculated Field & Calculated Item.
                     Again if one wants to manipulate a Pivot Table data one can copy the same and paste else where to manipulate. When Pivot table is pasted else where it detaches itself from the Excel Table. Here Clipboard copying is the best option. 
And copied information is not pivot table and it is no longer linked to the source data.