Use If Formula in Pivot Table

Before you start, 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.

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.

If you have multiple calculated items or formulas, you can adjust the order of calculation.

Add a calculated field

  1. Click the PivotTable.

    This displays the PivotTable Tools, adding the Analyze and Design tabs.

  2. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.

    Excel Ribbon Image

  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. Click the PivotTable.

    This displays the PivotTable Tools, adding the Analyze and Design tabs.

  2. If items in the field are grouped, on the Analyze tab, in the Group group, click Ungroup.

    Excel Ribbon Image

  3. Click the field where you want to add the calculated item.

  4. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Item.

    Excel Ribbon Image

  5. In the Name box, type a name for the calculated item.

  6. 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).

  7. Click Add.

Enter different formulas cell by cell for calculated items

  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.

Adjust the order of calculation for multiple calculated items or formulas

  1. Click the PivotTable.

    This displays the PivotTable Tools, adding the Analyze and Design tabs.

  2. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Solve Order.

    Excel Ribbon Image

  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.

View all formulas that are used in a PivotTable

You can display a list of all the formulas that are used in the current PivotTable.

  1. Click the PivotTable.

    This displays the PivotTable Tools, adding the Analyze and Design tabs.

  2. On theAnalyze tab, in the Calculations group, click Fields, Items, & Sets, and then click List Formulas.

    Excel Ribbon Image

Edit a PivotTable formula

Before you edit a formula, determine whether that formula is in a calculated field or a calculated item. If the formula is in a calculated item, also determine whether the formula is the only one for the calculated item.

For calculated items, you can 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.

Determine whether a formula is in a calculated field or a calculated item

  1. Click the PivotTable.

    This displays the PivotTable Tools, adding the Analyze and Design tabs.

  2. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click List Formulas.

    Excel Ribbon Image

  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, you would find this formula in the Sales cell for the MyItem row and January column.

  4. Continue by using one of the following editing methods.

Edit a calculated field formula

  1. Click the PivotTable.

    This displays the PivotTable Tools, adding the Analyze and Design tabs.

  2. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.

    Excel Ribbon Image

  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 Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Item.

    Excel Ribbon Image

  3. In the Name box, select the calculated item.

  4. In the Formula box, edit the formula.

  5. Click Modify.

Edit an individual formula for a specific cell of a calculated item

  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.

    Tip: If you have multiple calculated items or formulas, you can adjust the order of calculation. For more information, see Adjust the order of calculation for multiple calculated items or formulas.

Delete a PivotTable formula

Note: Deleting a PivotTable formula removes it permanently. If you do not want to remove a formula permanently, you can hide the field or item instead by dragging it out of the PivotTable.

  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:

    • To delete a calculated field, click anywhere in the PivotTable.

    • To delete a calculated item, in the PivotTable, click the field that contains the item that you want to delete.

      This displays the PivotTable Tools, adding the Analyze and Design tabs.

  3. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field or Calculated Item.

    Excel Ribbon Image

  4. In the Name box, select the field or item that you want to delete

Related Videos