Welcome Guest. Sign in or Signup

1 Answers

Building Formulas off of Excel Pivot Tables

Asked by: 972 views Microsoft Office, resolved

How do I “copy down” formulas referring to cells in a pivot table? The values do not change like relative values should because the references look like:

GETPIVOTDATA(“Loan Volume”,$A$2,”RealtyCo”,””)


1 Answers

  1. Solution Center on Oct 14, 2016

    Chris Gemignani explains:

    “New versions of Excel contain an evil feature called GETPIVOTDATA. Here’s a screencast (Windows Media only for now) to show you an example:

    Excel Evil Feature

    These results are bad because they don’t change when you copy and paste formulas. The user interface problem is that you commonly create a formula then copy/paste it down to propogate that formula. You expect relative references to be the default. The numbers in the PivotTable look just like any ordinary data, so you expect to be able to build formulas using them. However, GETPIVOTDATA treats your data like absolute references, which means that certain numbers will be managed in one mode and others in another mode.

    This breaks with your experience and generates results you don’t expect. When you catch them, that is! If you don’t catch the results, you can be publishing numbers that are just plain wrong.

    Here’s how to fix this.

    • Click inside the pivot table
    • Choose pivottable tools >> analyze
    • In the PivotTable section of the ribbon below the pivot table name, select Options
    • uncheck ‘Generate GetPivotData’

Answer Question