Make a Difference With PivotTables

Q: For many years we have created PivotTables in Excel, but it is frustrating because there doesn’t seem to be a way to add, subtract or divide columns. We want to subtract our actual columns from our budget columns to produce the difference and difference percentages. If there is a way to do this, we have never been able to find it. Can you help us?

A: You are not alone; many CPAs struggle with this same situation. The problem arises when you use your mouse to write a formula that references a cell in a PivotTable. In this case, Excel automatically inserts the GETPIVOTDATA function into your formula, as shown below in cell D2.

As a result, the formula cannot be properly copied with relative references to adjacent cells.

Presented below are two possible solutions: writing formulas using the keyboard, and inserting calculation columns into the PivotTable.

1. Writing formulas using the keyboard. If you write formulas that reference PivotTable cells by typing cell references into the formula using your keyboard instead of pointing to the cell using your mouse, Excel does not insert the GETPIVOTDATA function. The screenshot below shows the resulting formula in column D (=C2–B2), which was created using the keyboard instead of the mouse pointer.

This formula can then be copied to the cells below to produce the desired column differences. (Note: A problem with this method is that as you refresh your PivotTable, it may grow or shrink, which could reposition data in different cells. As a result, formulas created with your keyboard may no longer reference the correct data cells.)

2. Inserting calculation columns into the PivotTable. A better approach is to insert a new calculation column into your PivotTable using the Fields, Items, & Sets tool. To use this tool in Excel 2010, first select a cell within the PivotTable you want to modify, then from the PivotTable Tools menu, select Options, Calculations, Fields, Items, & Sets as shown below. (In Excel 2007, select PivotTable Tools, Options, Formulas, Calculated Field).

In the resulting Insert Calculated Field dialog box (shown below), type a name (to appear as the column label) in the Name box, then create the desired formula you want in the Formula box. To create the formula, start by inserting an equal sign, then select the column you want to appear in your formula from the Fields box and click the Insert Field button to add that field to your formula. Add mathematical operators (such as “+”, “–,” “/” and “*”) and continue this process until your formula is complete. (Note: The Formula box displays only one line of your formula at a time, making it difficult to view formulas in their entirety. Therefore, when creating lengthy formulas, you must use the left and right arrow keys to scroll, view and edit it.)

When you have completed your formula, click OK and the resulting new calculation column will appear as follows:

Repeat this process to insert an additional column to calculate the percentage difference.

Note: The fact that Excel automatically inserts the GETPIVOTTABLE function into formulas when referencing PivotTable data frustrates many CPAs, but actually, it is a good solution because it allows those formulas to continue to reference the correct data, even if the PivotTable shrinks or grows.

More from the JofA:

Find us on Facebook | Follow us on Twitter