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.
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:
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.
In an Excel pivot table, calculated fields and calculated items are custom formulas.
There are a few general restrictions on using pivot table formulas:
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.
Here are the key features of pivot table calculated items:
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:
Here are the key features of pivot table calculated fields
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.
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:
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 links below for more examples, and detailed information, for each type of pivot table formula:
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.
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.
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.
Last updated: February 16, 2022 3:25 PM