Managing and Maintaining Your Pivot Table Calculations

In your dealings with pivot tables, there will be times when you won't keep a pivot table for more than the time it takes to say, "Copy, Paste Special, Values." There will be other times, however, when it will be more cost-effective to keep your pivot table and all its functionality intact. When you do find yourself maintaining and managing your pivot table through changing requirements and growing data, you may find need to maintain and manage your calculated fields and calculated items as well.

Editing and Deleting Your Pivot Table Calculations

When your calculation's parameters change or you no longer need your calculated field or calculated item, you can activate the appropriate dialog box to edit or remove the calculation.

Simply activate the Insert Calculated Field dialog box or the Insert Calculated Item dialog box and select the Name drop-down, as shown in Figure 6.33.

Figure 6.33. Opening the Name drop-down list reveals all the calculated fields or items in the pivot table.

After you select a calculated field or item, you will have the option of deleting the calculation or modifying the formula.

Changing the Solve Order or Your Calculated Items

If the value of a cell in your pivot table is dependent on the results of two or more calculated items, you will have the option of changing the solve order of the calculated items. That is, you can specify the order in which the individual calculations are performed.

To get to the Calculated Item Solve Order dialog box shown in Figure 6.34, place your cursor anywhere in the pivot table and click the PivotTable icon in the PivotTable toolbar. Then select Formulas, Solve Order. Select any of the calculated items you see listed in order to enable the Move Up, Move Down, and Delete command buttons.

Figure 6.34. After you identify the calculated item you're working with, simply move the item up or down to change the solve order. You also have the option of deleting the item in this dialog box.

Documenting Your Formulas

Excel provides you a nice little function that lists the calculated fields and calculated items used in your pivot table, along with details on the solve order and formulas. This feature comes in especially handy if you're ever analyzing someone else's pivot table and you need to quickly determine what calculations are being applied and which fields and items they affect.

As you can see in Figure 6.35, in order to list your pivot table calculations, simply place your cursor anywhere in the pivot table and click the PivotTable icon in the PivotTable toolbar. Then select Formulas, List Formulas.

Figure 6.35. List Formulas allows you to document the details of your pivot table calculations quickly and easily.