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:
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:
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:
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:
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 500 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. The calculated item is used to create a total of all the Sold items (Backordered, Pending and Shipped).
NOTE: A calculated field cannot check the text value of a label, so use filtering or calculated items to show only the applicable items.
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: December 9, 2016 4:05 PM