Excel Champs Blog

    How To Insert Calculation In Pivot Table With Custom Field & Item

    insert-calculation-in-pivot-tableUsing 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 Add Calculation In Pivot Table

    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.

    Use A Simple Data Table To Add Calculated Field To Insert Calculation In Pivot Table

    • 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.

    Insert Calculated Field To Add Calculation In Pivot Table

    • 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.

    Calculated Field Pop-Up Box To Insert Calculation In Pivot Table

    • 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.

    Insert Formula In Calculated Feild To Insert Calculation In Pivot Table

    • Your new calculated field is created without any number format.

    Your New Calculated Field By inserting Calculation In Pivot Table

    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.

    Calculated Item Dialog Box To Insert Calculation In Pivot Table

    • 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.

    Insert a Calculated Item To Add Calculation In Pivot Table

    But wait a minute. What is this? Grand total is changed from 1506 & $311820 to 1746 & $361600.

    Inserted Calculated Item To Insert Calculation In Pivot Table

    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