Pivot Tables: Crunch the numbers using 3 tools in Excel — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily

Pivot Tables: Crunch the numbers using 3 tools in Excel

by on
in Centerpiece,Excel Training,Office Technology

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 care­­ful to click on the dropdown arrow and not just the Options button. Click Generate GetPivotData so the check mark is re­­moved. 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”).

GetPivotData

Now that you know how to turn Generate GetPivotData off, there are some good reasons to keep it on for cal­­cu­lating 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 ap­­pears 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.

Calculated Field

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:

  1. 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.
  2. 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.

Leave a Comment