Search Contextures Sites

Excel Pivot Table Calculated Item

 

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

 

 

For information on Calculated Fields, please visit: Excel Pivot Table Calculated Field

 

Create a Calculated Item

In addition to the existing items in a pivot table field, you can create one or more calculated items.

Warning: If you create a calculated item in a field, you will:

  • NOT be able to move the field to the Report Filters area
  • NOT be able to add multiple copies of a field to the Values area.
  • Extra items might be created in the pivot tables, such as cities appearing in all regions, with zero values.

In this example, the pivot table contains an Order Status field, and the orders currently have the following types of status:

  • Shipped
  • Pending
  • Backorder
  • Canceled

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:

  1. In the pivot table, select a cell that contains an Order Status item. For example, select cell A5, that contains the Backorder item. 
  2. On the Ribbon’s Options tab, click Calculations
  3. Click Fields, Items & Sets, and then click Calculated Item.
    • Note: If the Excel window is wide enough, you'll see Fields, Items & Sets as a separate command.
    • In Excel 2007, on the Ribbon’s Options tab, in the Tools group, click Formulas, and then click Calculated Item.
  4. calculateditem01

  5. Type a name for the Calculated Item, for example, Sold, and then press the Tab key to move to the Formula box.
  6. In the Fields list, select Order Status, and in the Items list, double-click Shipped, and then type a plus sign (+).
  7. Double-click Pending, type a plus sign, and then double-click Backorder. The complete formula is
  8. =Shipped + Pending + Backorder

    Note: You can include the space characters or omit them.

    calculateditem02

  9. Click OK, to save the calculated item, and to close the dialog box.

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.

calculateditem03

Hide the Unnecessary Pivot Items

In the pivot table, you can hide the Shipped, Pending, and Backorder items, because they are included in the Sold calculated item.

calculateditem04

With those pivot items hidden, the pivot table will show the correct Grand Totals.

calculateditem05

Delete a Calculated Item Manually

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:

  1. Select the cell that contains the label for the Formula1 calculated item.
  2. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (or the Options tab in Excel 2010).
  3. In the Calculations group, click Fields, Items and Sets (Click Formulas in Excel 2010).
  4. Click Calculated Item.
  5. From the drop-down list of formulas, select the formula you want to delete.
  6. Click the Delete button
  7. Select and delete any other unwanted items, and then click the Close button.

    remove calculated item

Delete a Calculated Item With a Macro

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

Modify a Calculated Item

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:

  1. In the pivot table, select one of the Order Status items. For example, select cell A6, which is the Canceled item.
  2. On the Ribbon’s Options tab, click Calculations
  3. Click Fields, Items & Sets, and then click Calculated Item.
    • Note: If the Excel window is wide enough, you'll see Fields, Items & Sets as a separate command.
    • In Excel 2007, on the Ribbon’s Options tab, in the Tools group, click Formulas, and then click Calculated Item.
  4. In the Calculate Item dialog box, click the drop down arrow for the Name box.
  5. Select Sold, which is the name of the calculated item you want to change.
  6. calculateditemmodify03

  7. In the Formula box, change the formula, to remove the +Backorder.
  8. calculateditemmodify04

  9. The revised formula is =Shipped+Pending
  10. calculateditemmodify05

  11. Click Modify, to save the change, and then click OK to close the dialog box.

Watch the Create a Calculated Item Video

To see the steps for creating a calculated item, please watch this short video tutorial.

Use Index Numbers in a 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 refering 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:

= OrderDate[1]+OrderDate[15]

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:

= OrderDate[+3]-OrderDate[+2]

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.

Change the Calculated Item Solve Order

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.

calculated item cancel rate

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.

calculated item wrong formula

Change the Solver Order

To fix the problem, you can change the Solve Order for the calculated items:

  • Select a cell in the pivot table, and then on the Ribbon, under PivotTable Tools, click the Options tab
  • In the Calculations group, click Fields, Items & Sets, and click Solve Order.

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.

  • Click on the CancelRate item, and click the Move Down button, twice, to move it to the bottom of the list.
  • Click Close

Note: When you change the Solve Order, it affects all calculated items in the pivot table.

The Correct Results

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.

 calculated item solve order changed

Watch the Solve Order Video

To see the steps for creating calculated items, and changing the solve order, please watch this short video.

Hide Items Created With Zero Values

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:

  1. Right-click a cell that contains a City row label
  2. In the popup menu, click Filter, and then click Value Filters.
  3. In the Value Filter window, from the first drop-down list, select Qty, which is the Values field you want to check.
  4. In the second drop-down list, select does not equal
  5. In the third box, type 0 (zero), and then click OK

Video:Hide Items Created With Zero Values

Watch this video to see how to create a calculated item, hide the zero value rows, and allow multiple filter types.

Download the Sample File

  1. Download the Create a Calculated Item sample file. The zipped file is in xlsx format.
  2. Download the Calculated Item Index Number sample file.
  3. Download the Solve Order sample file.
  4. Download the Hide Items Created With Zero Values sample file

Pivot Table Training

If you're looking for a video-based pivot table course,I recommend the following. Both are excellent quality, with clear, easy-to-follow instructions.

Xtreme Pivot Table course

By John Michaloudis, at My Excel Online.

Videos: 200+, total time, approx 9 hours, download

Sample Excel workbooks, to use with the video lessons.

Summary: Detailed coverage of pivot table topics, with in-depth coverage of topics.

Core Pivot

By Dave Bruns at ExcelJet.

Videos: 49, total time, approx 2.5 hours, online

Sample Excel workbooks, to use with the video lessons.

Summary: Short, to the point, coverage of pivot table topics, for rapid learning

 

More Pivot Table Resources

Tutorials:

 

 

Privacy Policy

 

Contextures Inc., Copyright ©2014
All rights reserved.

 

Last updated: November 2, 2014