One of the best ways to become an advanced pivot table user and use Excel for data analysis is by using calculated items and calculated field in a pivot table.
In layman’s language, I would say using formulas in a pivot table or custom calculation which don’t exist in the source data but works like other fields.
In simple words, these are the calculations within the pivot table. In the below example, you can see a pivot table with a calculated field which is calculating the average selling price.
On the other hand, source data doesn’t have any type of field like this.
NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.
Performing Calculations within Your Pivot Tables
As I have said there are two different ways to add custom formulas directly into the pivot table. Let’s learn each of these methods one by one and learn how you can use them in expanding your analysis skills.
Whats is a Calculated Field in a Pivot Table
A calculated field in a pivot table is a custom field that you can create using a custom formula that uses the existing fields for the calculation. In simple words, you can add a new field that is not in the data source but as a virtual column to your data set which according to the formula you have used.
I you look at the above example again:
We have sales amount and the total number of hours worked by the sales employees, that’s what we have in the data source also.
But just by using those two fields we have added a new field (Average Sales Per Hour) whose result is driven by using a formula and that formula is:
=Sales_Amount/Hours_Spend
How To Insert A Calculated Field In Pivot Table
In Excel pivot table, calculated field is like all other fields of your pivot table, but they don’t exist in the source data. But, they are created by using formulas in the pivot table.
Follow these simple steps to insert calculated field in a pivot table.
- First of all, you need a simple pivot table to add a Calculated Field.
- Just click on any of the fields in your pivot table. You will see a pivot table option in your ribbon which further having further two options (Analyze & Design) Click on the analyze option, then on Fields, Items, & Sets. You will further get a list of options, just click on the calculated field.
- After clicking the calculated field, you will get a pop-up menu, just like below. This popup menu comes with two input options (name & formula) & a selection option.
- Name: Name of the calculated Field which will show in your pivot table.
- Formula: An input option to insert formula for calculated field.
- Fields: A drop down option to select other fields from source data to calculate a new field.
In this example, we are going to calculate average selling price. And, the formula will be = amount / quantity.
- In Fields option, select Amount & click on insert, then insert “/” division operator & insert quantity after that.
- Press OK.
- Now a new Field appears in your Pivot Table.
- Your new calculated field is created without any number format.
How To Insert A Calculated Items In Pivot Table
Calculated items are like all other items of your pivot table, but the difference is that they are not in existence in your source data.
They are just created by using a formula.
You can edit, change or delete calculated Items as per your requirement.
- Just click on any of the items in your pivot table. You will see a pivot table option on your ribbon having further two options (Analyze & Design).
- Click on the Analyze, then on Fields, Items, & Sets.
- You will further get a list of options, just click on Calculated Item.
- After clicking the calculated item, you will get a pop-up menu, just like above. This popup menu comes with two input options (Name & Formula) & two selection options (Field & Items).
- We had already discussed “Name, Formula & Fields” in calculated fields.
- Items: To select the items for calculation.
In this example, we are going to calculate average for the first half of the year & for the 2nd half of the year.We just have to add the formula & it will =average(jan, feb, mar, apr, may, jun).
We just have to add the formula & it will =average(jan, feb, mar, apr, may, jun).
Do the same for the 2nd half.
Now you have to calculate items in your pivot, showing an average of 1st 6 months & 2nd 6 months of the year.
But wait a minute. What is this? Grand total is changed from 1506 & $311820 to 1746 & $361600.
The reason behind this is, pivot table totals & subtotal include your calculated fields while the calculation of total & sub-total. So, you need to filter your calculated items if you want to show the actual picture.
Things To Remember
- Don’t forget to remove 0 from formula input option while inserting a formula for calculation
- You can only able to use formulas which don’t require cell references.
- You have to check whether calculated items are affecting you pivot results(Sub Totals & Grand Totals)
- Adjust the solve order are per your calculation requirement
Last Words
By using a calculation in a pivot table, you can analyze data in the better way.
There is no need to apply the formula to entire data, you just have to put it in your pivot table & while changing or updating there is only one formula to change.
How are you using the calculation in pivot table at your end?
Other Pivot Table Tips
- Refresh All Pivot Tables At Once
- Add Rank in Pivot Table
- Add Running Total In Pivot Table
- Group Dates In Pivot Table
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
Is it possible to find a difference between two columns in a pivot table?
Hello Excel Champs, one question regarding calculated field, I’m trying to add a new field but formula is “X”column multiplied by TOTAL of “Y” Column, is that possible? To multiplied one field by the ColumnTOTAL of another field? Thanks!
Really helped a lot. Thank you!