Pivot Table Calculated Field

Pivot Table is so powerful tool in excel that it can calculate a huge excel data with few seconds with 100% accuracy and it has many option to calculate the data the way we want to create the reports. “Calculated Field” and “Calculated Item” are the tool in Pivot Table that we can create our own demanded calculation in a separate column.

Make sure that your cursor is in any cell of Pivot Table

Go to à Optionà FormulaàDropdown arrowà

Click on “Calculated Field” and following box will appear, in this box you can see that in the field of “Field” area there are several topics “Region”, ”Product”, ”Customer”, etc. All topics are from our Pivot Table.

With the help of these Field headings we can create more formulas and can get more results with the help of "Calculted Fields" and "Calculted Items" option, and example is given below:-

All the topics in this box are from the below given pivot table 

In this pivot table you can see if we want add a “Profit” column then simply apply the formula of “Calculated Field” à

àOption tabàFormula Tabà Click dropdown arrow àClick "Calculated Field" below box appear

Put the cursor in “Formula” field and “double click on Revenue” – (subtraction sign) “double click on COGS”, the formula should be like =Revenue - COGS, just like the below mention..

And simply click on “Add” button and simply click "OK”

When you click on “OK” our Pivot Table will look like  below given image, a new field of “Profit” is added in the Pivot table and with its calculation “Sum of Profits”

So this is the simple way by adding another field in the pivot table.

And simply click OK then following image will appear

One more complicated example

In this below given example we can use  “If” (Logical) conditioned based formula

After click on “OK” button, we will get the below given result

How to remove calculated field temporarily 

Now we if we want to remove “Calculated Field” temporarily then we will simply click on pivot table and press “right click”, simply remove it by below highlighted box.

How to remove calculated field temporarily

If we want to remove “Calculated Field” permanently then we will follow the below given instruction:-

1.      Click on Pivot Table

2.      Go to Option Tab

3.      Click on Formula Button drop down arrow

4.      Click on Calculated Field

5.      Calculated Field box appear

6.       Click on the drop down arrow and look for field you want to delete

7.      And simply delete it or you can modify it also by click on modify button.

List of All the Calculated Fields

1.         Click anywhere in the Pivot Table

2.         Go to Formula tab drop down

3.         Select for "List of Formulas" and Click 

       

A new sheet will be generated in the workbook,  with a list of "Calculated Fields" and "Calculated Items"

Pivot Table has more feature with "Calculated Fields" and "Calculted Item" like "Problem with Calculted Fields" and "Calculated Items", we will discuss more about this feature in future blog.

Till then good bye

Narendra