Make a Difference With PivotTables

BY J. CARLTON COLLINS

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 (=C2B2), 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  |   View JofA videos

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.