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.
The Pivot table created out of above mentioned data source seems much like same, except of pre-evaluated Grand Total.
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
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.
This will add a Percentage field in Pivot table, containing percentages of corresponding total marks obtained.
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.
How to proper subtotal of the sum if i was inserted the calculated field in pivot table? Please….
Amazing staff, Everybody in the office is amazed as this, its funny how little we know about excel.
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!
Thank you so much!
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
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
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
very nice solution, thanks buddy
just brilliant! searched all over google but this is the most practical solution!
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?
wow..thanks a lot
How do you delete a calculated field in 2010? I want to remove it from the field list permanently.
in the Insert Calculated Field screen choose the desired field and press delete (below the add button)
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.
(3 years later…) i just ran across the same problem. When I expand to add a column SOME of my calculated fields break, returning the ‘#NAME?’ error. So far, I’ve discovered this only happens when the calculated field is using a calculated field that was created immediately prior to it. If you replicate the same formula but later in the ‘solve order’ it will work and not bomb out. What I haven’t been able to do though is change the ‘solve order’ for calculated fields. THis only seems to work for Calculated Items. So, the workaround is to find all your calculated fields that break and then rebuild them again later in you field list, then remove the originals. Good luck…to you, or those who may follow trying to solve this Excel bug.
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.
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.
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.
hi
hi
r u der?
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?
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.
Thanks for that – Just what I needed!
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.