Friends,
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 ...
