Create Pivot Table Calculated Item and Calculated Field

In Excel, you can create pivot table calculated item and calculated field formulas. This short video shows you an example of each type of pivot table formulas, and when they should be used.

Difference Between Calculated Item and Calculated Field

The key difference between a calculated item and a calculated field is:

Calculated Fields can refer to other fields in the pivot table.
Calculated Items can refer to other items within a specific pivot field

Video: Calculated Item and Calculated Field

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.

Calculated Item and Calculated Field Details

The video shows a quick example of each type of pivot table formula. For detailed information, and more examples, go to these pages on my Contextures site:

Using Calculated Items in Pivot Tables
Using Calculated Fields in Pivot Tables

Pivot Table Formula Limitations

There are a few limitations on using calculated fields and calculated items:

Formulas cannot refer to the pivot table totals or subtotals – just the field names
Formulas cannot refer to worksheet cells by their address or by a name.
Formulas are available only in non-OLAP-based pivot tables – you can’t use them in Data Model pivot tables, for example

Calculated Field Limitation

Another limitation with calculated fields is that they always use the SUM of the other values, even if those values are displayed with another function, such as COUNT.

If you need to use a count for some calculated field formulas, there are instructions on the Calculated Field Count page on my Contextures website.

Get the Sample Workbook

Go to the Calculated Field or Calculated Item page on my Contextures website, to get the sample file used in this video. That page also has more information on the differences between calculated items and calculated fields.

____________________