If summary functions and custom calculations don't provide the results that you want, you can create your own formulas in calculated fields and calculated items . For example, you could add a calculated item with the formula for the sales commission, which could be different for each region. The PivotTable report would then automatically include the commission in the subtotals and grand totals.

Notes:

What do you want to do?

Create a formula

  1. Decide whether you want a calculated field or a calculated item within a field.
    • Use a calculated field when you want to use the data from another field in your formula.
    • Use a calculated item when you want your formula to use data from one or more specific items within a field.
  2. Do one of the following.

    Add a calculated field

    1. Click the PivotTable report.
    2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
    3. In the Name box, type a name for the field.
    4. In the Formula box, enter the formula for the field.

      To use the data from another field in the formula, click the field in the Fields box, and then click Insert Field. For example, to calculate a 15% commission on each value in the Sales field, you could enter = Sales * 15%.

    5. Click Add.

    Add a calculated item to a field

    1. If items in the field are grouped, on the Options tab, in the Group group, click Ungroup.
    2. Click the field where you want to add the calculated item.
    3. On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
    4. In the Name box, type a name for the calculated item.
    5. In the Formula box, enter the formula for the item.

      To use the data from an item in the formula, click the item in the Items list, and then click Insert Item (the item must be from the same field as the calculated item).

    6. Click Add.
    7. If you ungrouped items in step 1, regroup them if you want. For more information, see
  3. For calculated items, you can enter different formulas cell by cell.

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

    Do the following:

    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.
  4. If you have multiple calculated items or formulas, adjust the order of calculation by doing the following:
    1. Click the PivotTable report.
    2. On the Options tab, in the Tools group, click 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.

Display a list of formulas

To display a list of all the formulas used in the current PivotTable report, do the following:

  1. Click the PivotTable report.
  2. On the Options tab, in the Tools group, click Formulas, and then click List Formulas.

Edit a formula

  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 by doing the following:
    1. Click the PivotTable report.
    2. On the Options tab, in the Tools group, click Formulas, and then click List Formulas.
    3. In the list of formulas, find the formula that you want to change listed under Calculated Field or Calculated Item.

      When there are multiple formulas for a calculated item, the default formula that was entered when the item was created has the calculated item name in column B. For additional formulas for a calculated item, column B contains 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 would 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 PivotTable report.
    2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
    3. In the Name box, select the calculated field for which you want to change the formula.
    4. In the Formula box, edit the formula.
    5. Click Modify.

    Edit a single formula for a calculated item

    1. Click the field that contains the calculated item.
    2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
    3. In the Name box, select the calculated item.
    4. In the Formula box, edit the formula.
    5. Click Modify.

    Edit individual formulas for specific cells of a calculated item

    For example, if a calculated item named OrangeCalc 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 by doing the following:
    1. Click the PivotTable report.
    2. On the Options tab, in the Tools group, click 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.

Delete a formula

Tip If you don't want to delete a formula permanently, you can hide the field or item. To hide a field, drag it out of the report.

  1. Determine whether the formula is in a calculated field or a calculated item .

    Calculated fields appear in the PivotTable Field List. Calculated items appear as items within other fields.

  2. Do one of the following:

    Delete a calculated field

    1. Click the PivotTable report.
    2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
    3. In the Name box, select the field that you want to delete.
    4. Click Delete.

    Delete a calculated item

    1. Click the field with the item that you want to delete.
    2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
    3. In the Name box, select the item that you want to delete.
    4. Click Delete.