In an Excel pivot table, what is the difference between calculated fields and calculated items? When should these formulas be used, and what are their differences?

In a pivot table, you can use custom formulas to create calculated fields and calculated items. This tutorial will show examples of both types of pivot table formulas, and when they should be used.

The **key difference** between them is that:

- Calculated Fields are formulas that
**can refer to other fields**in the pivot table. - Calculated Items are formulas that
**can refer to other items within a specific pivot field**.

In this example, we'll set up a pivot table with both types of formulas, to see where and how they work. Click the links below for detailed information on each type of formula:

This video shows the difference between the two types of formulas, and shows how to set up a calculated item, and a calculated field, and then print a list of all the formulas.

Use custom formulas in an Excel pivot table, to create calculated fields and calculated items.

There are a few general restriction on using formulas though:

- Formulas are available only in non-OLAP-based pivot tables
- You can't create formulas that refer to the pivot table totals or subtotals.
- Formulas can't refer to worksheet cells by address or by name.

After you create formulas in a pivot table, you can use a built-in command to create a list of all the formulas in a pivot table.

Here are the key features of pivot table calculated items:

- A calculated item becomes an item in a pivot field.
- Its calculation can use the sum of other
**items in the same field**. - The individual records in the source data are calculated, and then the results are summed.
- Calculated items are listed with other items in the Row or Column area of the pivot table.
- Calculated items are NOT shown in the PivotTable Field List.

Click here to learn how to set up Calculated Items in Pivot Tables

If you create a calculated item in a field, the following restrictions will be placed on that pivot field:

- You will NOT be able to move the field to the Report Filters area
- You will NOT be able to add multiple copies of a field to the Values area.

Here are the key features of pivot table calculated fields

- Use calculated fields to perform
**calculations on other fields**in the pivot table. - For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.
- Sum is the only function available for a calculated field.
- A calculated field becomes a new field in the pivot table, and its calculation can use the sum of other fields.
- Calculated fields appear with the other value fields in the pivot table. Like other value fields, a calculated field's name may be preceded by Sum of.
- Calculated fields appear in the PivotTable Field List.

Click here to learn how to set up Calculated Fields in Pivot Tables

You can create a calculated item when you want to perform calculations on specific items in a pivot field.

In the example shown below, the Order Status field has four items -- Backorder, Canceled, Pending and Shipped.

In the Order Status field, you could create a calculated item named Sold, that sums the orders with a status of Shipped, Pending, or Backorder, but doesn't include Canceled orders.

In the screen shot below, the Sold calculated item is showing, and the Backorder, Pending and Shipped items have been hidden.

Use calculated fields to perform calculations on other fields in the pivot table. In this example, each sales representative receives a 3% bonus if they sold more than 100 units.

The calculated field formula checks the value in the Units field, and calculates the bonus amount based on 3% of the Total field.

**=IF(Units>100,Total*3%,0)**

Here is the result, with the bonus showing in the applicable rows.

**NOTE**: A calculated field cannot check the text value of a label, so use filtering or calculated items to show only the applicable items.

If you’ve used calculated items and calculated fields in your pivot table, you can quickly create a list of all the formulas.

**NOTE**: The list shows all of the formulas in the selected pivot table's pivot cache, even if those formulas are not currently displayed in the pivot table.

Follow these steps to create the list of pivot table formulas:

- Select any cell in the pivot table.
- On the Ribbon, under the PivotTable Tools tab, click the Analyze tab.
- In the Calculations group, click Fields, Items, & Sets
- ClickÂ List Formulas.

A new sheet is inserted into the active workbook, with a list of the selected pivot table's formulas.

Calculated fields (if any), are listed first, and then the Calculated Items (if any). The Solve Order is also shown, with a note on how the solve order works and how to change it.

Click the link to download the sample file that was used in the Calculated Field and Calculated Item example. The zipped file is in xlsx format, and does not contain macros.

Last updated: March 26, 2021 3:46 PM