Insert Calculated Field in Pivot Table in Microsoft Excel 2010

In this article we will lear how to we can add a data field that calculates the difference between two data fields in Mcirosoft Excel 2010.

“PIVOT TABLE”is used for Summarize alarge amount number of data without using any formulas, it makes the data easy to read with flexibility.

Let’s take an example to add data fields that calculate the difference between two data fields.

I have a “PIVOT TABLE” in which Column A containsthe product column B sum of total revenue in column C net revenue. We need to show the expenses amount inthe “PIVOT TABLE”. We need to follow the below mentioned steps to add the data field in the “PIVOT TABLE”.:-

 

img1

 

    • Click on any cell in the pivot table report, the contextual menu on the ribbon will get activated.
    • Go to the “Analyze” tab, in the “Calculations” group select “Calculated Field” from the “Field, Items & Sets” drop down list.
    • The dialog box will appear, type “Expenses Amount”as the name ofthe new field in the “Name” box.

 

img2

 

  • In the “Fields” box select “Total Revenue” and click on the “Insert Field” button or double click on “Total Revenue” directly to insert it in the Formula box. Type the minus (-) sign in the “Formula” box.
  • Select “Net Revenue” from the “Field” box and click on “Insert Field” or double click as mentioned above.
  • Click on ok.

The field will appear as another column in the pivot table.

 

img3

 

Now let’saddanother data field in the “PIVOT TABLE” –

    • Click on any cell in the pivot table report, the contextual menu on the ribbon will get activated.
    • Go to the “Analyze” tab, in the “Calculations” group select “Calculated Field” from the “Field, Items & Sets” drop down list.
    • The dialog box will appear, type “%age of Expenses” as the name of the new field in the “Name” box.
    • In the “Field” box select “Expenses Amount” and click on “Insert Field” or double click on Expenses Amount,type division (/) sign in the “Formula” box.
    • Select “Total Revenue” from “Field” box and click on “Insert Field” or double click on Total Revenue.

 

img4

 

  • Click on ok.
  • To change the field to %age format, select the range E5:E12, and press the key “CTRL+SHIFT+ %( 5)”.
  • The Range will be formatted as a %age.

 

img5

Users are saying about us...

  1. Hi, I did what you’ve done and my results showing as millions but my numbers is as simple as 209 – 361 and then division is 0

    can you advise what I’ve done wrong.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube