You’ve made that giant leap to using Pivot Tables. Congratulations! Now it’s time to gnash some numbers.
Relative Cell References
Most people want to use the values in their Pivot Table formulas the same way they use nonpivoted data. However, the default setting for Pivot Tables produces this type of formula. =GETPIVOTDATA(“Sum of Qtr 4”, $A$3,”Product”,”Alice Mutton”)-GETPIVOTDATA(“Sum of Qtr 1”, $A$3,”Product”,”Alice Mutton”). To get something more like: =C5-B5, you need to turn off Generate GetPivotData.
This is done on the Pivot Table Tools Options tab (2007 and 2010, Analyze tab in 2013) with the Options dropdown button, Pivot Table group. Be careful to click on the dropdown arrow and not just the Options button. Click Generate GetPivotData so the check mark is removed. Now your formulas will appear as =C5-B5 instead of =GETPIVOTDATA(“Sum of Qtr 4”, $A$3,”Product”,”Alice Mutton”)-GETPIVOTDATA(“Sum of Qtr 1”, $A$3,”Product”,”Alice Mutton”).
Now that you know how to turn Generate GetPivotData off, there are some good reasons to keep it on for calculating pivoted data. For example, if you always want to perform a calculation using an item subtotal or Pivot Table grand total, GetPivotData is your best bet.
Depending upon how your Pivot Table grows, a GetPivotData formula will always refer to that item total or grand total regardless of where it appears on the worksheet. If your Grand Total appears on row 30 now, but when you filter it, it appears on row 25, the total created at the top of the example worksheet will always show the total. If you used relative cell references (above), it would show whatever value was in C30.
Perhaps one of the best ways to introduce a formula into your Pivot Table is to create a Calculated Field. The new calculation becomes a selectable field in every Pivot Table that uses that data source. For example, if you always want to know the difference between Qtr1 and Qtr2, create a calculated field as follows:
- With your cursor anywhere in your Pivot Table, click the Fields, Items & Sets button in the Calculations group, Pivot Table Options contextual tab. Choose Calculated Field.
- Name the field, and build your formula by selecting fields and using the Insert Field button to add them to your formula. You can type functions or math operators the way you normally would.