Show All

Edit a PivotTable or PivotChart formula

For best results in a PivotChart report, work in the associated PivotTable report where you can see the individual data values that the formula calculates.

1. Determine whether the formula is in a calculated field or a calculated item. If the formula is in a calculated item, determine whether the formula is the only one for the calculated item.

   How?

   1. Click the report.
   2. On the PivotTable toolbar, click PivotTable, point to Formulas, and then click List Formulas.
   3. In the list of formulas, find the formula you want to change listed under Calculated Field or Calculated Item.

      When there are multiple formulas for a calculated item, the default formula entered when the item was created has the calculated item name in column B. For additional formulas for a calculated item, column B has both the calculated item name and the names of intersecting items.

      For example, you might have a default formula for a calculated item named MyItem, and another formula for this item identified as MyItem January Sales. In the PivotTable report, you'd find this formula in the Sales cell for the MyItem row and January column.

2. Do one of the following:

   Edit a calculated field formula

   1. Click the report.
   2. On the PivotTable toolbar, click PivotTable or PivotChart, point to Formulas, and then click Calculated Field.
   3. In the Name box, click the calculated field for which you want to change the formula.
   4. In the Formula box, edit the formula.
   5. Click Modify, and then click OK.

   Edit a single formula for a calculated item

   1. Click the field that contains the calculated item.
   2. On the PivotTable toolbar, click PivotTable or PivotChart, point to Formulas, and then click Calculated Item.
   3. In the Name box, click the calculated item.
   4. In the Formula box, edit the formula.
   5. Click Modify, and then click OK.

   Edit individual formulas for specific cells of a calculated item

   For example, if a calculated item named Strawberries has a formula of =Oranges * .25 across all months, you can change the formula to =Oranges *.5 for June, July, and August.

   1. Click a cell for which you want to change the formula.

      To change the formula for several cells, hold down CTRL and click the additional cells.

   2. In the formula bar, type the changes to the formula.
3. If you have multiple calculated items or formulas, adjust the order of calculation.

   How?

   1. Click the report.
   2. On the PivotTable toolbar, click PivotTable or PivotChart, point to Formulas, and then click Solve Order.
   3. Click a formula, and then click Move Up or Move Down.
   4. Continue until the formulas are in the order that you want them to be calculated.

Note  When you edit a formula in a PivotChart report or its associated PivotTable report, some chart formatting may be lost.