30 Advanced Excel Tips for 2026

Excel Champs / Pivot Table / Formulas in a Pivot Table (Calculated Fields & Items)
By Puneet Gogia (Microsoft MVP)

Formulas in a Pivot Table (Calculated Fields & Items)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Click any cell in the pivot table and go to PivotTable Analyze → Fields, Items, & Sets → Calculated Field (the tab is called Analyze in older versions). Type a name, delete the 0 in the Formula box, build the formula from the field list, for example =Amount/Quantity for average selling price, then click Add and OK.

A calculated field works on the totals, not row by row. =Amount/Quantity is right because it divides total amount by total quantity, but =Price*Quantity multiplies two totals and gives the wrong result. For row-level maths, add a helper column to the source data and refresh the pivot.

To calculate across the items of one field, such as months, click one of those items in the pivot and choose Calculated Item from the same menu. A formula like =AVERAGE(Jan,Feb,Mar,Apr,May,Jun) adds a new item alongside the months.

Calculated items are counted in subtotals and grand totals, so the totals no longer match your source. Filter out either the calculated items or the items they are built from so nothing is counted twice. You also cannot add a calculated item to a grouped field, such as dates grouped by month.

Neither option can use cell references or named ranges, and both are greyed out in a pivot table built on the Data Model. There, right-click the table name in the PivotTable Fields pane, choose Add Measure and write a DAX formula such as =SUM(Sales[Amount])/SUM(Sales[Quantity]).

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 fields in a pivot table.

They let you use formulas in a pivot table to create custom calculations which don’t exist in the source data but work 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 calculates the average selling price, while the source data doesn’t have any field like this.

insert calculation in pivot table

Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.

Calculated Field in a Pivot Table

In an Excel pivot table, a calculated field is like all the other fields of your pivot table, but it doesn’t exist in the source data. Instead, it’s created by using a formula in the pivot table. Follow these simple steps to insert the calculated field in a pivot table.

  1. 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
  2. Just click on any of the fields in your pivot table. You will see two pivot table tabs in your ribbon (Analyze & Design), called PivotTable Analyze and Design in Microsoft 365. Click on the Analyze tab, then on Fields, Items, & Sets. You will get a list of options; just click on Calculated Field.
    Insert Calculated Field To Add Calculation In Pivot Table
  3. After clicking Calculated Field, you will get a dialog box, just like below. It comes with two input options (Name & Formula) & a selection option (Fields).
    Calculated Field Pop-Up Box To Insert Calculation In Pivot Table
  4. Name: Name of the calculated field, which will show in your pivot table.
  5. Formula: An input option to insert the formula for the calculated field.
  6. Fields: A list to select other fields from the source data to calculate the new field.
  7. In this calculated field example, we are going to calculate the average selling price, and the formula will be = amount/quantity.
  8. In the Fields list, select Amount & click on Insert Field, then type the “/” division operator & insert Quantity after that.
  9. Press OK.
  10. Now a new field appears in your pivot table.
    Insert Formula In Calculated Feild To Insert Calculation In Pivot Table
  11. Your new calculated field is created without any number format.
    Your New Calculated Field By inserting Calculation In Pivot Table

Calculated Items in a Pivot Table

Calculated items are like all other items of your pivot table, but the difference is that they don’t exist in your source data. They are created by using a formula on the items of a row or column field. You can edit, change or delete calculated items as per your requirement.

  1. Just click on any of the items in your pivot table. You will see two pivot table tabs on your ribbon (Analyze & Design).
  2. Click on the Analyze tab, then on Fields, Items, & Sets.
  3. You will get a list of options; just click on Calculated Item.
    Calculated Item Dialog Box To Insert Calculation In Pivot Table
  4. After clicking Calculated Item, you will get a dialog box, just like above. It comes with two input options (Name & Formula) & two selection options (Fields & Items).
  5. We had already discussed “Name, Formula & Fields” in calculated fields.
  6. Items: To select the items for the calculation.

Now, back to calculated items. In this example, we are going to calculate the average for the first half of the year & for the second half of the year. With the Month field selected, we just have to add the formula.

=average(jan, feb, mar, apr, may, jun)

Now you have calculated items in your pivot, showing the average of the first six months and the second six 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 that pivot table totals & subtotals include your calculated items when they calculate the total and subtotal. So you need to filter out your calculated items (or the months they are based on) if you want to show the actual picture.

Things To Remember

  1. Don’t forget to remove the 0 from the Formula box (it starts as = 0) while inserting a formula for the calculation.
  2. You can only use formulas that don’t need cell references or defined names.
  3. You have to check whether calculated items are affecting your pivot results (Sub Totals and Grand Totals).
  4. Adjust the solve order (Fields, Items, & Sets ➜ Solve Order) as per your calculation requirement.

Calculated Fields Work on Sums

There’s one thing you need to know about calculated fields: they always work on the sum of the fields, not row by row. That’s why =Amount/Quantity gives the right average price (total amount divided by total quantity). But a formula like =Price*Quantity gives a wrong total, because it multiplies the sum of the prices by the sum of the quantities. In that case, add a column with the calculation to the source data instead.

You can also use functions in a calculated field. For example, the following formula gives a 5% bonus when the total amount is more than 100,000:

=IF(Amount>100000, Amount*5%, 0)

Edit, Delete or List Calculations

  • Edit: open Calculated Field (or Calculated Item) again, pick your calculation from the Name drop-down, change the formula and click Modify.
  • Delete: pick it from the Name drop-down in the same dialog box and click Delete.
  • List: go to Fields, Items, & Sets ➜ List Formulas to get a new sheet with all the calculated fields and items of the pivot table.

Frequently Asked Questions

Why is Calculated Field greyed out?

Your pivot table is based on the Data Model (for example, you ticked “Add this data to the Data Model”). Data Model pivots don’t support calculated fields and items; use a measure (Power Pivot ➜ Measures ➜ New Measure) instead.

Why can’t I add a calculated item?

Select a cell of a row or column field item first (not a value cell). Calculated items also don’t work when a field is grouped (like grouped dates) or when the field uses Average, StdDev or Var in the values area.

What is the difference between a calculated field and a calculated item?

A calculated field makes a new column of values from other fields (like Amount/Quantity). A calculated item makes a new item inside an existing field (like an average of January to June inside the Month field).

5 thoughts on “Formulas in a Pivot Table (Calculated Fields & Items)”

  1. Very clear, concise directions which saves time
    Thank you

    Reply
  2. Is it possible to find a difference between two columns in a pivot table?

    Reply
    • Yes. Just write the formula that way. For example, if I want to calculate the difference between “target completion date” and “actual completion date”, the formula would be “=ACTUAL-TARGET”. In this instance, I would then have to format the new calculated column from date to number.

      Reply
  3. 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!

    Reply

Leave a Comment