How To Add Calculated Field To Pivot Table

Contextures

Calculated Field or Calculated Item

In a pin 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 tabular array. Utilise a calculated item for formulas that refer to other items in the same pivot field. Run across examples of both types of pin table formulas, and when they should be used. Video, written steps, Excel file.

Key Departure Between Them

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

The central divergence between calculated fields and calculated items is that:

Calculated Fields are formulas that tin refer to other fields in the pivot tabular array.
Calculated Items are formulas that can refer to other items within a specific pivot field.

This video shows the fundamental 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.

Near Pivot Table Formulas

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

In that location are a few general restrictions on using pin tabular array formulas:

Formulas are available only in non-OLAP-based pivot tables.
- Note: If y'all checked the box "Add to Data Model", the pivot tabular array is OLAP-based
You can't create formulas that refer to the pivot tabular array totals or subtotals.
Formulas can't refer to worksheet cells by address or by name.

Afterwards you create formulas in a pivot table, you can use a born control to create a list of all the formulas in a pivot table.

In the examples beneath, we'll set a pin tabular array with both types of formulas, to run across where and how each type of formula works best.

Nigh Calculated Items

Here are the cardinal features of pivot table calculated items:

A calculated item becomes an particular in a pivot field.
Its adding tin can utilize the sum of other items in the same field.
First, the individual records in the source information are calculated, and and so the results are summed.
Calculated items are listed with other items in the Row or Cavalcade area of the pin table.
Calculated items are NOT shown in the PivotTable Field Listing.

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
Yous will NOT be able to add multiple copies of a field to the Values expanse.

Almost Calculated Fields

Hither are the fundamental features of pivot table calculated fields

Use calculated fields to perform calculations on other fields in the pivot table.
First the private amounts in the other fields are summed, and after that the calculation is performed on the full amount.
Sum is the only role available for a calculated field.
A calculated field becomes a new field in the pin tabular array, and its calculation can use the sum of other fields.
Calculated fields announced with the other value fields in the pivot table. Similar other value fields, a calculated field's proper name may exist 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 Detail Example

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

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

pivot field with 4 pivot items

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

calculated item formula

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

calculated item added to pivot table

Calculated Field Example

Use calculated fields to perform calculations on other fields in the pivot table. In this case, each sales representative receives a three% 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)

calculated field

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

NOTE: A calculated field cannot check the text value of a label, so apply filtering or calculated items to evidence simply the applicable items.

complex Calculated Field

Listing All Pin Table Formulas

If you lot've used calculated items and calculated fields in your pivot tabular array, you lot tin quickly create a list of all the formulas.

NOTE: The list shows all of the formulas in the selected pivot table'due south pin 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:

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

List of Formulas

A new sheet is inserted into the active workbook, with a list of the selected pivot tabular array'due south formulas.

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

List Formulas command

More Examples

Click the links below for more examples, and detailed information, for each blazon 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 higher up.

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

After you create a pivot tabular array in Excel, you lot can add together custom formulas, either calculated fields or calculated items.

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

This is Debra Dalgleish from Contextures.com.

The 2 types of formulas in a pin tabular array are calculated field and calculated particular.

To create either of those, we would select a cell in the pivot tabular array, go up to the Analyze tab, click Fields, Items, & Sets, and and so we have a selection Calculated Field or Detail.

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

So we'll try a calculated item offset, and what nosotros'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 beginning matter nosotros would practise is give this a name, and I'm going to call it Sold.

Then we take to create our formula, and information technology right now but shows equals nil.

Nosotros can come across all the fields in our pivot table here.

Because I accept lodge status selected, that's the one that information technology has highlighted in this list of fields, and it'southward showing all the items, merely I could do something with whatsoever of the other fields if I prefer.

If I wanted to piece of work with the regions, I could click information technology and see its items.

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

And to do that I'chiliad 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 simply deleted that for me and put Backorder in.

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

Plus Shipped.

So nosotros're going to go 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.

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

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

And now nosotros but accept Canceled and Sold.

And then that's a very simple Calculated Detail.

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

Well, this is the same data,

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

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

And then I've got a cell selected in this pivot tabular array, go to Analyze, Fields, Items, & Sets.

And this time nosotros've got a value field selected, so it's not offer to let us make a calculated detail for that.

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

And at present nosotros take fields, but in that location's no list of items. This time we're strictly working with the fields.

We want to exercise something with this Total, so I'll double click and it puts it in there and for our bonus they're going to get iii% 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 three% of their total sales.

And the terminal thing nosotros'll take a wait at, while we're creating formulas in a pin tabular array, 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 recall what's in it.

Just select a cell in a pivot tabular array, become back to Fields, Items, & Sets on the Analyze tab.

And then go to Listing Formulas and it puts a new sheet in the workbook.

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

Nosotros've got Calculated Item, and information technology lists whatever, or if you don't accept any, yous just accept the heading there.

It too shows the Solve Order.

And at that place's a note at the bottom nigh how that works, and I've got videos that show more than about the Solve Guild.

And the proper noun of the calculated field or detail.

And 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 Particular example. The zipped file is in xlsx format, and does not incorporate macros.