Last updated: September 22, 2014 3:00 PM

Search Contextures Sites

Related Tutorials

.

.

.

.

.

.

.

.

.

.

.

.

## Excel Pivot Table -- GetPivotData

- GetPivotData Formula
- Turn Off Generate GetPivotData
- Video: Turn Off GetPivotData
- Using Cell References in GetPivotData
- Using Dates in GetPivotData
- Video: Dates in GetPivotData Formula
- Select Specific Pivot Table in GetPivotData
- Video: Select Specific Pivot Table in GetPivotData
- GetPivotData with Custom Subtotals

- Download the Sample File
- More Pivot Table Tutorials
## GetPivotData Formula

To extract data from a cell in a pivot table, you can enter a normal cell link, such as =B5, or you can use the GetPivotData function, which is specially designed to extract data from a pivot table.

The advantage of using the GetPivotData function is that it uses criteria to ensure that the correct data is returned, even if the pivot table layout is changed.

If you have the Generate GetPivotData feature turned on, this formula will be created automatically, when you reference a cell in a Pivot Table.

In the example above, the formula in cell A9 was created by typing an equal sign, and then clicking on cell B5. It returns the total for file folders.

=GETPIVOTDATA("Total",$A$3,"Product","File Folders")If you prefer to use a cell reference, you can:

- type the reference, e.g.
=B5- OR, use the
Generate GetPivotDatacommand to turn this feature off.There are instructions below, for finding this command## Turn Off Generate GetPivotData

## In Excel 2007/2010

In Excel 2007 and Excel 2010, you can turn off the

Generate GetPivotDatacommand by using a command in the Excel Ribbon.

- Select any cell in a pivot table.
- On the Ribbon, under PivotTable Tools, click the Options tab
- In the PivotTable group, click the drop down arrow for Options
- Click the Generate GetPivotData command, to turn the feature off or on.
## In Excel 2003/2002

In Excel 2003 or 2002, you can turn off the

Generate GetPivotDatacommand by adding a button to the PivotTable toolbar.

- On the PivotTable toolbar, click the Toolbar Options button
- Click the
Add or Remove Buttonscommand- Click PivotTable, to open the submenu
- Near the end of the commands list, click on
to add a check mark.Generate GetPivotData- Click on the worksheet, to close the menu.
On the PivotTable toolbar, click the

Generate GetPivotDatabutton to toggle this feature on and off.

## Video: Turn Off GetPivotData

## Excel 2010 / 2007 Video

To see the steps for turning off the Generate GetPivotData in Excel 2010 and Excel 2007, please watch this short video tutorial. The Excel 2003 video is below this video.

## Excel 2003 Video

To see the steps for turning off the Generate GetPivotData in Excel 2003, please watch this short video tutorial. The Excel 2007 / 2010 video is above this video.

## Using Cell References in GetPivotData

In a GetPivotData formula, you refer to the pivot table, and the field(s) and item(s) that you want the data for. For example, this formula gets the Total, from the pivot table in $A$3, for the Product field, and the Paper item.

=GETPIVOTDATA("Total",$A$3,"Product","Paper")To make a GetPivotData formula more flexible, you can refer to worksheet cells, instead of typing item or field names in the GetPivotData arguments.

Using the same example, we can type "Paper" in cell E2. Then, change the formula in cell E3, so refers to cell E2, instead of typing "Paper" in the formula.

=GETPIVOTDATA("Total",$A$3,"Product", E2)The formula returns the total for the Paper product.

## Using Cell References For Data Field

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")## Add an Empty String

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.

## Using Dates in GetPivotData

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
## Match the Date and Date Format

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")## Use the DATEVALUE Function

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"))## Use the DATE Function

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))## Refer to a Cell With a Date

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)## Video: Dates in GetPivotData Formula

To see the steps for using dates in a GetPivotData formula, please watch this short video.

## Video: Select Specific Pivot Table in GetPivotData

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.

## Select Specific Pivot Table in GetPivotData

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:

- East
- North
- All
The pivot tables are set up using consistent names and locations:

- Each sheet name begins with "PT_", followed by the region description.
- Each pivot table body range begins in cell B4
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.

## Create the Formula

The GETPIVOTDATA formula will be entered in cell D6, so the first step will be to create a simple formula there:

- In cell C6, select East from the drop down list
- Select cell D6, and type an equal sign
- Click on the PT_East sheet
- Click on the Grand Total cell, and press the Enter key
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)## Generalize the Sheet Reference

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")## Replace the Sheet Reference

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

## GetPivotData with Custom Subtotals

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.

## Top or Bottom Subtotals

The GetPivotData formulas have different requirements, depending on the location and type of the Subtotals.

There are two GetPivotData formula types:

- Normal -- =GETPIVOTDATA("Quantity",$A$3,"Category","Bars")
- [List] ----- =GETPIVOTDATA($A$3,"Category[Bars;Sum]")
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 Sample File

Download the zipped sample file for this tutorial. The file is in xlsx format, and does not contain macros

## Xtreme Pivot Table Course

Pivot table skills are essential, if you want to be an Excel master. To raise your skills to the expert level, I recommend the

Xtreme Pivot Table course, from John Michaloudis, at My Excel Online.This course has more than 200 videos -- beginner, intermediate and advanced level -- along with practice workbooks, finance business cases, and 12 months of personal support. Each short lesson is clear, and easy to follow. Work through the lessons at your own pace, and track your progress. The course is an excellent value, at a surprisingly low price.

See the course details, and watch sample videos here:

Xtreme Pivot Table Course. When you buy the course, use the coupon codeCONTEXTURESfor a10% discount

## More Pivot Table Resources

Add-In: PivotPower Premium- Beginning Pivot Tables Book
- Pivot Tables Recipe Book
- Xtreme Pivot Table Course

Tutorials:

- FAQs - Pivot Tables
- Pivot Table Introduction
- Grouping Data
- Multiple Consolidation Ranges
- Running Totals
- Summary Functions
- Clear Old Items in Pivot Table

Contextures Inc., Copyright ©2014

All rights reserved.