Create Calculated Field in Pivot Table [Excel 2010]

Pivot Table, being the most celebrated feature of Excel, includes multitude of options to manipulate the data in desired way. Once pivot table is created from the source data, you can add fields & items without modifying source data, which comes in handy for doing quick calculations. In this post we will demonstrate adding a Calculated field in Pivot table.

To begin with, launch Excel 2010 spreadsheet that contains pivot table. For illustration purpose, the data source of Pivot table contains fields; Course Names, Studied By (number of students), Total Marks Obtained, and Total Marks.

table 1

The Pivot table created out of above mentioned data source seems much like same, except of pre-evaluated Grand Total.

pivot table

Now in Pivot table, we need to insert a new field without inserting field in data source which will evaluate percentage of total marks obtained by students. For this, head over to PivotTable Tools Options tab, from Calculation group, under Fields, Items, & Sets options, click Calculated Fields


options

It will bring up Insert Calculated Field dialog, enter an appropriate name of Calculated field. From Formula, enter formula for evaluating calculating field.

As we are calculating percentage, we will enter a simple percentage formula that’s include Total Marks obtained field, and Total Marks field. To quickly add field name in Formula input pane, double-click field name under Fields box. Once formula is entered, Click Add and then Close the dialog.

percentage

This will add a Percentage field in Pivot table, containing percentages of corresponding total marks obtained.

percentage 1

You can also check our previously reviewed guides on How to calculate working days in Excel 2010 and How to create custom Conditional Formatting rule in Excel 2010.

Advertisement
  • Steve

    Thanks for that – Just what I needed!

    • http://www.facebook.com/terriel2 Terrie Larson

      I’ve got two calculated fields, once calculates a % and another caclulates that percentange of another field. However, my subtotals and totals of the calculated return the results of the formula applied to the subtotals of the other fields, rather than suming the results of the calculated field.

  • shelley

    a calculated field that used to work in Excel 2007 is now throwing a #DIV/0! i’m dividing one value by another and in 07 the value came back fine. any suggestions?

    • SS

      use an =if formula: eg =if(A1=0,0,A2/A1), should return 0 instead of #DIV/0 and the correct result when there is one.

  • Audrey Onderdonk

    But what if your pivot total was one of the values you wanted to use in your calculation? For example, I want to show the percent of Y by dividing Y/Total.  The total is coming out of the pivot and is not a column in my original data source. 

    • Dale

      Create the calculated field. Say you want Total Marks percentage of each line to Grand Total. Enter the formula  = Total Marks to duplicate the data in the Total Marks Column but label the field say GT % Tot Marks.
      Next select the calculated field in the pivot table. Select Value Field Settings by right click. Select the Show Values Tab. In the Show Values AS box select % of Total instead of Normal.

    • Dale

      Create the calculated field. Say you want Total Marks percentage of each line to Grand Total. Enter the formula  = Total Marks to duplicate the data in the Total Marks Column but label the field say GT % Tot Marks.
      Next select the calculated field in the pivot table. Select Value Field Settings by right click. Select the Show Values Tab. In the Show Values AS box select % of Total instead of Normal.

    • Jittender Kumar

      hi

    • Jittender Kumar

      hi

    • Jittender Kumar

      r u der?

  • Idanrm

    thanks for that.
    Why when i change data source (expanding colums) some of the calculated fields get #NAME, although, i didn’t change the name of the original name or location of fields they are based on.

  • Kthy_n

    How do you delete a calculated field in 2010?  I want to remove it from the field list permanently.

    • Idanrm

      in the Insert Calculated Field screen choose the desired field and press delete (below the add button)

  • Abc

    wow..thanks a lot

  • BCPlanner

    Different question: when using a pivot table w/slices (by month) fields that are set to sum work perfectly if I select 1 month at a time. However when selecting for example (an 8 month slice) it multiplies the sum (total of all months) by 8. example: month 1 – month 8 = 10 each (total of all months = 80); when I select each month the amount shows as 10 per month; when I select or show all items I get 640. what am I missing?

  • Don_M

    just brilliant! searched all over google but this is the most practical solution!

  • Faysal

    very nice solution, thanks buddy

  • Zian Chen

    I used calculated field to calculate Days Payment Made. I subtracted Date Invoice Sent from Date Payment Received. Look like the pivot table does not recognize the date format so all the days were wrong. How to

  • jamshed

    The post in respect to calculated field was helpfull to arrive at a derived percentage.

    Now I need to ascertain within the pivot total sales based on the COUNT value of each sale. Am unable to arrive at this formula. Say for e.g Sum Value is 100K which I need to arrive at average value and this in the pivot is based on a count field, it is giving me a zero value?

    Please advise

  • Gerben Steijaert

    What if you want to have a calculated field only for a total column? I ‘ve created a pivot with two levels of column labels. When I insert the calculated field it gives a calculated value column with each column label. I owuld like to have it only for the Total Column?
    Anyone

  • Max

    Thank you so much!

  • Sandra Maniram

    Please help!

    The Sum of Field2 calculates how many times the value in Breach dips below zero but does not show the sub-total. Why? please someone HELP!!!
    I need this fopr a presentation for tomorrow!

  • Tefo Rapapadi

    Amazing staff, Everybody in the office is amazed as this, its funny how little we know about excel.