Using calculation in the pivot table is like a custom calculation which not exists in the source data but works like other fields.
In simple words, these are the calculations within the pivot table.
Excel gives us two options for calculation in a pivot table, one is calculated field & other is calculated item.
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 is not having any type of calculation like this.
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
Really helped a lot. Thank you!