Modify a Pivot Table Calculated Item

After you create a calculated item in a pivot table, you might need to change its formula.

For example, in this pivot table there’s a calculated item named Sold, in the Order Status field. The Sold item sums the orders with a status of Shipped, Pending, or Backorder.


Change the Calculated Item Formula

You can change the formula, so it doesn’t include the Backorder items. To do that, follow these steps to go back into the Insert Calculated Item dialog box and modify the calculated item.

  1. In the pivot table, one of the Order Status items. For example, select cell A6, which is the Canceled item.
  2. On the Ribbon, click the Options tab, under the PivotTable Tools tab.
  3. in the Tools group, click Formulas, and then click Calculated Item.CalcItem02
  4. In the 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.CalcItem03
  6. In the Formula box, change the formula, to remove the +Backorder.CalcItem04
  7. The revised formula is =Shipped+Pending.CalcItem05
  8. Click Modify, to save the change, and then click OK to close the dialog box.


This entry was posted in Calculations. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *