Show All

Hide All

For best results in a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.), work in the associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) where you can see the individual data values that the formula calculates.

1. Determine whether the formula is in a calculated field (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or a calculated item (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.). 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 (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), 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 (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) 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 (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), 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 (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), 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 (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), 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 (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), 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.