MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: See Why GETPIVOTDATA Might Not Be Entirely Evil


June 12, 2019 - by Bill Jelen

Excel See Why GETPIVOTDATA Might Not Be Entirely Evil. Photo Credit: Manki Kim at Unsplash.com

Most people first encounter GETPIVOTDATA when they try to build a formula outside a pivot table that uses numbers in the pivot table. For example, this variance percentage won’t copy down to the other months due to Excel inserting GETPIVOTDATA functions.

A pivot table has months down the side, years 2018 and 2019 across the top. Outside of the pivot table, in column E, someone tried to build a formula of =D3/C3-1 but instead got a formula with two GETPIVOTDATA functions.

Excel inserts GETPIVOTDATA any time you use the mouse or arrow keys to point to a cell inside the pivot table while building a formula outside the pivot table.


By the way, if you don’t want the GETPIVOTDATA function to appear, simply type a formula such as =D5/C5-1 without using the mouse or arrow keys to point to cells. That formula copies without any problems.

If you typed the formula =D5/C5-1 without touching the mouse or arrow keys, you get a formula without GETPIVOTDATA.

Here is a data set that contains one plan number per month per store. There are also actual sales per month per store for the months that are complete. Your goal is to build a report that shows actuals for the completed months and plan for the future months.

A data set with Store Name in column A. Each store has 12 rows of Plan numbers (Jan, Feb, through Dec) and eventually 12 rows of Actual numbers.

Build a pivot table with Store in Rows. Put Month and Type in Columns. You get the report shown below, with January Actual, January Plan, and the completely nonsensical January Actual+Plan.

The first iteration of the pivot table has stores in column A. Across the top is Jan Actual, Jan Plan, then a meaningless Jan Total.

If you select a month cell and go to Field Settings, you can change Subtotals to None.

You can get rid of the Jan Actual Plus Plan column. Double-click on the Jan heading in B4. In the Field Settings, change Subtotals from Automatic to None.

This removes the useless Actual+Plan. But you still have to get rid of the plan columns for January through April. There is no good way to do this inside the pivot table.

But there is still a problem. You have to remove Jan Plan and Feb Plan because you already have actuals.

So, your monthly workflow becomes:

  1. Add the actuals for the new month to the data set.
  2. Build a new pivot table from scratch.
  3. Copy the pivot table and paste as values so it is not a pivot table anymore.
  4. Delete the columns that you don’t need.

There is a better way to go. The following very compressed figure shows a new Excel worksheet added to the workbook. This is all just straight Excel, no pivot tables. The only bit of magic is an IF function in row 4 that toggles from Actual to Plan, based on the date in cell P1.

This report is not a pivot table. It has months across the top, stores down the side. A row shows Actual or Plan using an IF statement.

The very first cell that needs to be filled in is January Actual for Baybrook. Click in that cell and type an equal sign.

Start from what will be the first numeric cell in the report. In this case, it is a cell for January Actual Baybrook. Type an equals sign in that cell.

Using the mouse, navigate back to the pivot table. Find the cell for January Actual for Baybrook. Click on that cell and press Enter. As usual, Excel builds one of those annoying GETPIVOTDATA functions that cannot be copied.

But today, let’s study the syntax of GETPIVOTDATA.

The first argument below is the numeric field "Sales". The second argument is the cell where the pivot table resides. The remaining pairs of arguments are field name and value. Do you see what the auto-generated formula did? It hard-coded "Baybrook" as the name of the store. That is why you cannot copy these auto-generated GETPIVOTDATA formulas. They actually hard-code names into formulas. Even though you can‘t copy these formulas, you can edit them. In this case, it would be better if you edited the formula to point to cell $D6.

The resulting formula is =GETPIVOTDATA("Sales",Sheet3!$A$3,"Store","Baybrook","Month","Jan",Type,"Actual")

The figure below shows the formula after you edit it. Gone are "Baybrook", "Jan", and "Actual". Instead, you are pointing to $D6, E$3, and E$4.

Change the formula to use cell references instead of hard-coded values. The resulting formula is =GETPIVOTDATA("Sales",Sheet3!$A$3,"Store",$D6,"Month",E$3,Type,E$4)

Copy this formula and then choose Paste Special, Formulas in all of the other numeric cells.

Paste Special, Formulas

Now here‘s your monthly workflow:

  1. Build an ugly pivot table that no one will ever see.
  2. Set up the report worksheet.

Each month, you have to:

  1. Paste new actuals below the data.
  2. Refresh the ugly pivot table.
  3. Change cell P1 on the report sheet to reflect the new month. All the numbers update.

    The only magic in the worksheet is an IF statement that changes the word Budget to Actual based on the Through Date in R1.

You have to admit that using a report that pulls numbers from a pivot table gives you the best of both worlds. You are free to format the report in ways that you cannot format a pivot table. Blank rows are fine. You can have currency symbols on the first and last rows but not in between. You get double-underlines under the grand totals, too.

Thanks to @iTrainerMX for suggesting this feature.

Title Photo: Manki Kim at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.