To extract data from a cell in a pivot table, use the Excel GetPivotData function, which is specially designed to extract data from a pivot table. Make changes to the formula, so you can copy it down a column
If you're creating a formula in Excel, and you click on a pivot table value, Excel might create a GetPivotData formula for you, automatically, instead of a normal cell reference.
For example, in the screen shot below, I typed an equal sign in cell A9. Next, I clicked on cell B5, in the pivot table. That cell has the total sales amount for the File Folders product.
If you're not familiar with the GetPivotData function, that long formula is not what you expected, and it can be confusing! In the following sections, you'll see:
There are advantages and disadvantages (pros and cons) to using GetPivotData, so consider these points, before you decide to use a simple cell reference, instead of a GetPivotData formula.
The GetPivotData function is a highly efficient way to get specific data from a pivot table. Here are a few of its advantages.
The GetPivotData function can cause problems sometimes. Here are a few of its disadvantages.
If you can't decide when to use GetPivotData, these guidelines might help you.
If you're creating a formula in Excel, and you click on a pivot table value, Excel might create a GetPivotData formula for you, automatically, instead of a normal cell reference.
To get a simple cell reference to a pivot table value cell:
By default, when you first use Excel, the Generate GetPivotData setting is turned on. If you prefer, you can turn that setting off, so the GetPivotData formulas are not created automatically.
To see the steps for turning off the Generate GetPivotData in Excel 2007 and later, you can watch this short video tutorial.
There are written steps below the video, to use the Ribbon command, or the Excel Options window.
In Excel 2007 and later, you can turn off the Generate GetPivotData command by using a command in the Excel Ribbon.
NOTE: This will affect ALL Excel workbooks, not just the active workbook
Another way to turn the Generate GetPivotData setting on or off is with the Excel Options.
Follow these steps to change the setting:
NOTE: This will affect ALL Excel workbooks, not just the active workbook
In the GetPivotData function syntax, there are the following arguments. The first 2 arguments are required:
For example, in the screen shot below, the GETPIVOTDATA formula has the following values for its arguments:
=GETPIVOTDATA("Total",$A$3,"Product","File Folders")
To make a GetPivotData formula more flexible, you can refer to worksheet cells, instead of typing item or field names in the GetPivotData arguments.
This makes it easier to copy a GetPivotData formula down a column, or to see different results, without changing the GetPivotData formula.
For example, type this formula in cell E4, to get the total sales for the paper product:
Next, follow these steps to make the formula flexible:
After you change the pivot item argument to a cell reference, instead of hard-coded text in the formula, it's more flexible.
Instead of creating a separate formula for each product, you can drag down the formula in cell E4. The formula has a relative reference to cell A4, so the reference will automatically change in each row.
Cell references work well for the pivot fields and pivot items, but can cause problems if you try to refer to a data field.
In this example, cell E2 contains the word "Qty", and you'd like to refer to that cell, instead of having "Qty" in the GetPivotData formula.
However, if you change the first argument, data_field, to a reference to cell E2, the result is a #REF! error
=GETPIVOTDATA(E2,$A$3,"Product","Paper")
To fix this problem, you can concatenate an empty string ( "" ) at the beginning or end of the cell reference:
=GETPIVOTDATA(E2&"",$A$3,"Product","Paper")
With this simple change to the formula, it returns the correct result.
If you use dates in a GetPivotData formula, you might get errors, even if the date is shown in the pivot table. For example, in the formula shown below, there is a reference to the date "1/1/13", and the pivot table shows the quantity sold on that date. However, the formula result in cell E4 is a #REF! error.
=GETPIVOTDATA("Quantity",$B$3,"OrderDate","1/1/13")
To prevent errors for dates, you can use one of the following methods:
-- Match the pivot table's date format
-- Use the DATEVALUE function
-- Use the DATE function
-- Refer to a cell with a valid date
-- Use the TEXT function
To get the correct results when typing a date in the GetPivotData formula, use the same date format that is shown in the pivot table.
In cell E4, the formula uses the date format that's in the pivot table -- dd/mmm/yy -- and the result is the correct quantity for that date:
=GETPIVOTDATA("Quantity",$B$3,"OrderDate","01/Jan/13")
Instead of just typing the date in the formula, add the DATEVALUE function to the date.
In cell E4, the date is entered within the DATEVALUE function -- and the result is the correct quantity for that date:
=GETPIVOTDATA("Quantity",$B$3,"OrderDate",DATEVALUE("1/1/13"))
Instead of just typing the date in the formula, use the DATE function to create the date.
In cell E4, the date is created within the DATE function -- and the result is the correct quantity for that date:
=GETPIVOTDATA("Quantity",$B$3,"OrderDate",DATE(2013,1,1))
Instead of typing the date in the formula, you can refer to a cell that contains a valid date, in any format recognized as a date by Excel.
In cell E4, the formula refers to the date in cell E2 -- and the result is the correct quantity for that date:
=GETPIVOTDATA("Quantity",$B$3,"OrderDate",E2)
Instead of just typing the date in the formula, add the TEXT function to the date.
In cell E2, the date is entered as text. The formula in cell E4 uses the TEXT function with the date format of "d-mmm":
=GETPIVOTDATA("Qty",$B$3,"Date",TEXT(E2,"d-mmm"))
Thanks to Leonid Koyfman for this tip
To see the steps for using dates in a GetPivotData formula, please watch this short video.
If you have multiple copies of a pivot table in a workbook, on different sheets, you can use GETPIVOTDATA to pull an amount from a specific pivot table. Watch this video to see the steps, and the written instructions are below the video.
If you have multiple copies of a pivot table in a workbook, on different sheets, you can use GETPIVOTDATA to pull an amount from a specific pivot table.
In this example, there are 3 pivot tables:
The pivot tables are set up using consistent names and locations:
On another sheet in the workbook, a data validation drop down list is added in cell C6, showing all the regions, which are also used in the sheet names.
The GETPIVOTDATA formula will be entered in cell D6, so the first step will be to create a simple formula there:
There is a GETPIVOTDATA formula in the cell, and the cell displays the total sales for the East region.
The formula refers to the Total Price field, and to cell B4 on the PT_East sheet.
=GETPIVOTDATA("TotalPrice",PT_East!$B$4)
Instead of leaving the hard-coded reference to the PT_East sheet, you can use the INDIRECT function in the GetPivotData function to create a range reference based on the text in cell C6.
The INDIRECT function requires one argument, INDIRECT(ref_text) and returns the range specified by the reference text argument.
Each reference in this workbook will begin with "PT_", followed by the range description in cell C6, and ending with "!$B$4". So, in this case, the formula will be:
INDIRECT("PT_" & C6 & "!$B$4")
The final step is to replace the current sheet reference in the GETPIVOTDATA formula, with the INDIRECT formula:
=GETPIVOTDATA("TotalPrice",PT_East!$B$4)
changes to:
=GETPIVOTDATA("TotalPrice",INDIRECT("PT_" & C6 & "!$B$4"))
Now, when you change the region in cell C6, the total amount changes in cell D6
With a default subtotal, the GetPivotData function works well, and returns the correct result. In the screen shot below, an equal sign was typed in cell B1, and then the Bars subtotal amount was clicked.
A GetPivotData formula was automatically created, and it returns the quantity of Bars sold.
=GETPIVOTDATA("Quantity",$A$3,"Category","Bars")
However, if the subtotal is a custom function, instead of the default function, the GetPivotData formula might show an error.
In the screen shot below, the we right-clicked on the Bars Total label, and clicked Field Settings. Then, Custom was selected for Subtotals, and Sum and Average selected.
Now, if you type and equal sign and click on either of the Bars subtotal cells, the result is a #REF! error. The GetPivotData formula looks different too, with square brackets in it.
=GETPIVOTDATA($A$3,"Category[Bars;Data,Sum]")
To fix the #REF! error, you can remove the "Data," from the GetPivotData formula. In this example, the corrected formula is:
=GETPIVOTDATA($A$3,"Category[Bars;Sum]")
With that simple change to the formula, the correct result is returned.
The GetPivotData formulas have different requirements, depending on the location and type of the Subtotals.
There are two GetPivotData formula types:
This table summarizes where the formula types can be used, with subtotals shown at the top or bottom, and how many subtotals are allowed in each location.
Download the zipped sample file for this tutorial. The file is in xlsx format, and does not contain macros
Last updated: August 12, 2022 11:28 AM