Calculated Field or Calculated Item

In a pivot table, calculated fields and calculated items are custom formulas. Use a calculated field for pivot table formulas that refer to other fields in the pivot table. Use a calculated item for formulas that refer to other items in the same pivot field. See examples of both types of pivot table formulas, and when they should be used. Video, written steps, Excel file.

Key Difference Between Them

In an Excel pivot table, you can use custom formulas to create calculated fields and calculated items.

The key difference between calculated fields and calculated items 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.

This video shows the key 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. There is a full Video Transcript below.

About Pivot Table Formulas

In an Excel pivot table, calculated fields and calculated items are custom formulas.

There are a few general restrictions on using pivot table formulas:

• Formulas are available only in non-OLAP-based pivot tables.
  • Note: If you checked the box "Add to Data Model", the pivot table is OLAP-based
• 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.

In the examples below, we'll set up a pivot table with both types of formulas, to see where and how each type of formula works best.

About Calculated Items

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

Calculated Item Warning

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.

About Calculated Fields

Here are the key features of pivot table calculated fields

• Use calculated fields to perform calculations on other fields in the pivot table.
• First the individual amounts in the other fields are summed, and after that 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

Calculated Item Example

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.

Calculated Field Example

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.

List All Pivot Table Formulas

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.

List the Formulas

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

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

   

List of 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.

More Examples

Click the links below for more examples, and detailed information, for each type of pivot table formula:

-- Using Calculated Items in Pivot Tables

-- Using Calculated Fields in Pivot Tables

Video Transcript

Here is the full transcript of the Calculated Field and Calculated Item video, shown above.

'------------------------------

After you create a pivot table in Excel, you can add custom formulas, either calculated fields or calculated items.

In this video we'll see when to use either type of formula and what you can do with those formulas, such as combining several items into one, or adding something to the total with a special calculation.

This is Debra Dalgleish from Contextures.com.

The two types of formulas in a pivot table are calculated field and calculated item.

To create either of those, we would select a cell in the pivot table, go up to the Analyze tab, click Fields, Items, & Sets, and then we have a choice Calculated Field or Item.

The calculated field lets us work with any of the fields in the pivot table and the calculated items lets you work with any items in a specific field.

So we'll try a calculated item first, and what we're going to do is work with this Order Status and instead of having Canceled plus 3 different types below it, we're going to combine all of these 3 into something called Sold.

Here, I'll click Calculated Item, and the first thing we would do is give this a name, and I'm going to call it Sold.

Then we have to create our formula, and it right now just shows equals zero.

We can see all the fields in our pivot table here.

Because I have order status selected, that's the one that it has highlighted in this list of fields, and it's showing all the items, but I could do something with any of the other fields if I prefer.

If I wanted to work with the regions, I could click it and see its items.

But we'll go back to Order Status, and what we want to do is get the total for Backorder, plus Pending, plus Shipped.

And to do that I'm going to double click on Backorder, and that puts it up into the formula, so I didn't have to delete what was there.

It just deleted that for me and put Backorder in.

Then we'll do a plus and Pending. I'll double click it.

Plus Shipped.

So we're going to get the total of those 3, and I'll click Add and OK.

Now what's happened is, it still has Canceled, our other 3 items, and Sold.

So we're getting double the amount for each of these items.

So the last step here would be to hide the 3 items that are being included in Sold.

And now we just have Canceled and Sold.

So that's a very simple Calculated Item.

The other type of formula in a pivot table is a Calculated Field.

Well, this is the same data,

I've now listed all the sales reps here, how many total units they sold, and the total amount of their sales.

We're going to give everyone a bonus, and it's going to be based on what total they sold.

So I've got a cell selected in this pivot table, go to Analyze, Fields, Items, & Sets.

And this time we've got a value field selected, so it's not offering to let us make a calculated item for that.

So Calculated Field, and we're going to name this, and we'll call it Bonus.

And now we have fields, but there's no list of items. This time we're strictly working with the fields.

We want to do something with this Total, so I'll double click and it puts it in there and for our bonus they're going to get 3% of their sales.

So I'll do an asterisk to multiply, and .03 would be our multiplier.

Click OK. And there's the bonus that each person will get, based on 3% of their total sales.

And the last thing we'll take a look at, while we're creating formulas in a pivot table, is getting a list of all those formulas that we've added.

So this is helpful if you're taking over a pivot table that someone else built or if you built something a while ago and you can't remember what's in it.

Just select a cell in a pivot table, go back to Fields, Items, & Sets on the Analyze tab.

Then go to List Formulas and it puts a new sheet in the workbook.

And we've got Calculated Fields. If you have any, it might be blank.

We've got Calculated Item, and it lists any, or if you don't have any, you just have the heading there.

It also shows the Solve Order.

And there's a note at the bottom about how that works, and I've got videos that show more about the Solve Order.

And the name of the calculated field or item.

And then it prints out the formula.

Get the Sample File

Click the link to get 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.

More Tutorials

Calculated Field - Count

Calculated Items

Running Totals

Summary Functions

Clear Old Items in Pivot Table