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.

CalcItem01

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.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

_________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Calculated Item, Pivot Table, pivot table tutorial. Bookmark the permalink.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>