Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
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.
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.
In this example, we are going to calculate average selling price. And, the formula will be = amount / quantity.
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.
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.
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?