Excel Pivot Table Calculated Item
In addition to the existing items in a pivot table field, you can create one or more calculated items, using custom formulas. Also, see the difference between Calculated Items and Calculated Fields
A Calculated Item is a custom formula in an Excel pivot table, that can use the sum of other items in the same field. For example, calculate the sum of 2 other items in a field.
Restrictions: Here are a few general restriction on using custom formulas:
Features: Here are the key features of pivot table calculated items:
Warnings: If you create a calculated item in a field, be aware of these issues:
To see the steps for creating a calculated item, watch this short video. The written steps are below the video.
Note: In newer versions of Excel, the Calculated Item command is on the PivotTable Analyze tab, under Fields, Items & Sets.
In this example, the pivot table contains an Order Status field, and the orders currently have the following types of status:
In the pivot table, you could create a calculated item, named Sold, to sum all the units that have been sold, for orders with a status of Shipped, Pending, or Backorder.
Follow these steps to create a calculated item:
=Shipped + Pending + Backorder
Note: You can include the space characters or omit them.
The new calculated item, Sold, is added to the Row area in the pivot table.
However, the Grand Totals have increased, because the Sold item duplicates the values from other items.
In the pivot table, you can hide the Shipped, Pending, and Backorder items, because they are included in the Sold calculated item.
With those pivot items hidden, the pivot table will show the correct Grand Totals.
If you no longer need a calculated item in a pivot table, you can manually remove it. Follow these steps to remove any unwanted calculated items:
If you frequently need to remove calculated items in a pivot table, you can use a macro to remove them. The following code remove the calculated item whose label is selected.
Add this code to a regular module, in a workbook that is always open, such as the Personal Workbook. Then, add a button on the Quick Access Toolbar, or on the Ribbon, to run the macro.
Sub CalcItemRemove() Dim pi As PivotItem On Error Resume Next If Not pi Is Nothing Then If pi.IsCalculated Then pi.Delete Else MsgBox "Please select a calculated item label" End If Else MsgBox "Please select a pivot item label" End If End Sub
After you create a calculated item in a pivot table, you might need to change its formula.
In the previous section, you created a calculated item named Sold, in the Order Status field. The Sold item sums the orders with a status of Shipped, Pending, or Backorder.
You can change the calculated item's formula, so it doesn't include the Backorder items.
Follow these steps to modify the calculated item:
Instead of item names, you can use index numbers in a calculated item's formula. This can be a helpful solution if the pivot table source data changes each month, to use the previous month's data. Instead of referring to specific dates in the calculated field, use the index numbers.
For example, to sum the data for the first date in the OrderDate field and the fifteenth date, create a calculated item in the OrderDate field, with the following formula:
You can also refer to pivot items by their index number, relative to the calculated item. For example, you could create a calculated item named DateCalc, with the following formula:
If the DateCalc calculated item is moved to the top of the list of OrderDates, it calculates the difference between the value for the OrderDate that is three rows below and the OrderDate that is two rows below.
Tip: To move the item, right-click the DateCalc item, click Move, and then click Move "DateCalc" to Beginning.
Warning: If you move the calculated item into one of the referenced positions, you create a circular reference.
Warning: If you use a negative number in the relative position, the number is automatically changed to a positive number, and the formula will not produce the expected results.
If you have two or more calculated items in a pivot table, there might be cells that are affected by multiple calculated items. This can cause problems, if the formulas are not solved in the order that you expected.
For example, in the pivot table shown below, there is a calculated item -- CancelRate -- in the Status field. If you select a cell in the CancelRate row, the formula shows in the formula bar. It is calculating the cancellation rate for the total number of policies.
Later, a calculated item -- Northeast -- is created in the Region field. When you select the CancelRate cell in that column, it shows the Northeast formula, instead of the CancelRate formula.
The rate is shown as 11.7%, which is not correct. We don't want it to add the other rates, we want it to use the CancelRate formula.
To fix the problem, you can change the Solve Order for the calculated items:
The message at the bottom of the Calculated Item Solve Order dialog box explains that the last formula listed is the one that determines the cell's value.
We'll move CancelRate to the bottom, so its formula will be used in the CancelRate row.
Note: When you change the Solve Order, it affects all calculated items in the pivot table.
With the Solve Order changed, the percentages in the CancelRate row are now showing the correct values -- 5.8% for the Northeast and 2.7% for the Southwest.
When you click on the Northeast CancelRate cell, the CancelRate formula is showing, so the solve order change has fixed the problem.
To see the steps for creating calculated items, and changing the solve order, please watch this short video.
Watch this video to see how to create a calculated item, hide the zero value rows, and allow multiple filter types. The written instructions are below the video
If you create a calculated item, extra items might appear in the pivot table, such as each city being listed under each region, with zero amounts in some rows.
To hide these zero items, use a Values filter:
With a built-in command, you can quickly create a list of the calculated items and calculated fields in the selected pivot table. Watch this short video to see the steps, and the written instructions are on the Pivot Table Calculated Fields page.
1) Download the Create a Calculated Item sample file. The zipped Excel workbook is in xlsx format, and does not contain any macros.
2) Download the Calculated Item Index Number sample workbook.
3) Download the Solve Order sample file.
4) Download the Hide Items Created With Zero Values sample file
Last updated: October 30, 2022 12:56 PM