Pivot Table Calculated Field - Step by Step

Friends,

In the last two articles we have learned about how to create or insert a PivotTable and few Customization in Microsoft Excel. In this article we will discuss about Calculated Fields in Pivot Table.

But before that let us first know ...

Why should we use Calculated Fields and in which circumstances we should use this technique.

Let me ask you a question ?

Have you ever try to sum two columns in a Pivot Table ?

Let me clear it, if you want to sum first two columns using SUM function or using plus sign like sales value of product 1 & product 2 in the last blank column what will happen ?

You will find a long formula starting GETPIVOTDATA and six parameters (!!!), total value will be calculated for one cell. Okay, now if you drag it to sum the entire data range (that we normally do), you will get zeros all the cells except the first cell. This is because when you try to sum two cells from Pivot Table by default Excel uses the GETPIVOTDATA function with static cell ranges. This is the reason not to get the sum in all cells.

Now in this situation we have one option left i.e., sum by reference (i.e., mentioning that 'sum C2 cell with D2 cell' for example), but here is also we might have problems because in future if you want to change the Pivot Table structure there will be either a blank column between the Pivot Table and the newly created column outside the Pivot Table or Pivot Table will ask you to overlap the column which you just created.

But think if we have some technique to sum these two columns within the Pivot Table as individual column then these problems will vanish and additionally you will get an opportunity to add the new column whenever you need in the Pivot Table.

Yes... there is a process to do this in Microsoft Excel PivotTable, which is known as Calculated Field.

Calculated because you put your own formula to calculate from the Pivot Table column(s).

Field because your newly created column will be listed in Pivot Table as a new Column or Field.

How to Create Calculated Field in Pivot Table

       1.   Select any cell within Pivot Table, you will see two additional Tabs Options and Design

       2.   Click on Option Tab

       3.   Under Tools submenu, click Formulas beside PivotChart

       4.   Click Calculated Field

Calculated Field Options Tab Then Formulas

       5.   A new small window will appear, "Insert Calculated Field"

a)  In the first input box labeled as Name type a name for your Field, for example, 'Sum Of Three Products'

b)  Go to next input box either by clicking in the box or you can hit TAB button

c)   Here you will put formula to be calculated, e.g., I need sum of three columns so I typed SUM after equal sign and selected the fields separated by commas, you can use plus sign without Typing SUM.

d)  To select the fields to be added you need to select it, and either you can double click or can click on the Insert Field Button, notice a single opening and closing quote is automatically printed in the formula box for each item.

e)   Click OK and you are done, you can now see a new field in the Pivot Table and you can also find it within the PivotTable Field List Box.

Insert Calculated Field Window

Create Calculated Field Window - Using Formula

New Field Added as a Column within Pivot Table

New Field Added as a Column within Pivot Table

This is how to create Calculated Field in Pivot Table. Now I will show you...

How to Use Logical Function IF in Calculated Field in Pivot Table

To use logical IF function within Pivot Table as Calculated Field the steps are same as mentioned earlier, remember if you use IF function in Calculated Field it only displays the numbers after calculation, no Texts are allowed, if you try to evaluate Text based upon the result of IF it will give you #VALUE error.

Please watch carefully the following picture you will understand how to use IF in Calculated Fields.

Using IF function in Pivot Table Calculated Field

Using IF function in Pivot Table Calculated Field

Remove or Delete a Calculated Field from PivotTable

It is very simple.

From Options tab click Formulas to open the Insert Calculated Field window.

From Name drop down list select the Field you want to Delete, click Delete button below Modify button, click OK and done.

Remove or Delete a Calculated Field from PivotTable

Modify a Calculated Field from PivotTable

If you need to rectify formula or the field name, its' better to do it through Modify button.

From Options tab click Formulas to open the Insert Calculated Field window.

From Name drop down list select the Field you want to Modify. Make necessary changes in the Name or Formula and then click Modify button and click OK and you are done.

Modify a Calculated Field from PivotTable

Calculated Field in Pivot Table

Few Things About Calculated Fields in PivotTable

Limitations

       1.   Calculated field formulas cannot refer to the pivot table totals or subtotals

       2.   Calculated field formulas cannot refer to worksheet cells by address or by name.

       3.   Sum is the only function available for a calculated field.

       4.   Calculated fields are not available in an OLAP-based pivot table. (OLAP - Online Analytical Processing, we'll discuss in another article).

Features

       1.   For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.

       2.   Calculated fields are automatically available in all pivot tables that are based on the same pivot cache

That's all friends for today. Hope the topic discussed today is easily understandable. Keep reading & keep asking & commenting...

Thank you ...