Home > Pivot Table > Calculation > GetPivotData

Excel Pivot Table GetPivotData Function

To get data from a cell in a pivot table, use Microsoft Excel GetPivotData function to reference pivot table cell. See how to turn off GetPivotData formulas. How to fix GetPivotData formula, to copy it down a column with dynamic references

GetPivotData Formula

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 wanted to create a link to cell B5

• First, I typed an equal sign in cell A9.
• Next, I clicked on cell B5, in the pivot table
• Excel automatically created this strange-looking formula:
  • =GETPIVOTDATA("Total",$A$3,"Product","File Folders")

Why did Excel Create GetPivotData Formula?

Usually, if you're building a formula, and you click on a cell, Excel creates a simple link to that cell, like this: =B5

However, if you click on one of the summarized numbers in a pivot table, Excel tries to help you, and it creates a GetPivotData formula.

Tips

1) Turn it off: If you don't need that automatic formula feature, the short video in the next section shows how to turn it off. (This will affect ALL Excel workbooks on your computer)

2) Fix It: When you drag down a GetPivotData formula, it might show the same result in every row. See how to fix the formula, so it works correctly.

3) Benefits: There are advantages to using GetPivotData, so check out the list of GetPivotData Pros and Cons, before you turn it off forever!

How do I turn off GetPivotData?

To see the steps for turning off the GetPivotData in Excel , you can watch this short video tutorial. There are written steps below the video, using 2 different methods to stop GetPivotData formulas.

Video Timeline

• 00:00 Automatic GetPivotData formula
• 00:24 Copy down GetPivotData formula
• 00:38 Turn Off GetPivotData feature
• 01:01 Test Cell Link
• 01:18 Turn On GetPivotData feature

Steps to Turn Off GetPivotData

By default, when you first use a newly installed Excel, the GetPivotData setting is turned on. If you prefer, you can turn that setting off, by using either the Excel Ribbon, or Excel Options.

• NOTE: This is an application-level setting in Excel, and affects ALL workbooks on your computer, not just the active workbook.

1) Use Ribbon Command

To turn off the Generate GetPivotData command, using the Excel Ribbon, follow these steps:

NOTE: This will affect ALL Excel workbooks, not just the active workbook

1. Select any cell in a pivot table.
2. On the Ribbon, click the PivotTable Analyze tab
• Or, under PivotTable Tools, click the Options tab
3. At the left click the arrow on the PivotTable command
4. Next, click the drop down arrow for Options
5. Click the Generate GetPivotData command, to turn the feature off or on.

2) Change Excel Options

Another way to turn the Generate GetPivotData setting on or off is with the Excel Options.

Remember - This will affect ALL Excel workbooks, not just the active workbook

Follow these steps to change the setting:

• Select any cell on the worksheet - the active cell does NOT need to be in a pivot table
• At the top left of the Excel window, click the File tab
• In the list at the left, click Options (or click More, then click Options)
• In the Excel Options window, at the left, click the Formulas category
• Scroll down to the Working with formulas section
• To turn off GetPivotData, remove the check mark for this option:
  • Use GetPivotData functions for PivotTable references
• Click OK, to close the Options window

GetPivotData Pros and Cons

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.

GetPivotData Advantages

The GetPivotData function is a highly efficient way to get specific data from a pivot table. Here are a few advantages of using this function to get pivot data.

• Excel builds formula automatically, based on pivot table cell that was clicked.
• formula shows correct result, even if pivot table layout changes - has pivot field and pivot item names
• formula can be modified, to make it more flexible

GetPivotData Disadvantages

The GetPivotData function can cause problems sometimes. Here are a few of its disadvantages.

• formula is long and confusing, compared to a simple cell reference
• default formula is specific to the cell that was clicked -- pivot field and pivot item names are hard-coded into the formula
• when you drag the formula down to rows below, same result shows in each row
• if you try to modify GetPivotData formula, it could return errors

When to Use GetPivotData

If you can't decide when to use GetPivotData, these guidelines might help you.

When should you use GetPivotData?

• you want to refer to a specific value in the pivot table, based on it pivot field and pivot item names
• the pivot table layout might change, but the value in the formula will remain in the pivot table
• you know how to modify the GetPivotData formula, to change specific names to cell references

When is a simple formula better?

• you want to do a quick calculation, based on a few pivot table numbers
• you need to copy the formula down to rows below, and have the results change in each row
• you don't know how to modify the GetPivotData formula with cell references

Simple Link to Pivot Cell

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.

• You can turn off GetPivotData feature, as described in the section above
• OR, follow the steps below, to create a simple cell reference to a pivot table value

Get a Simple Cell Reference

To get a simple cell reference to a pivot table value cell:

• If Excel has already created a GetPivotData function in your formula, delete that part of your formula
• Then, instead of clicking on the pivot table cell, type its cell address, e.g. : =B5

In this screen shot, I deleted the long formula in cell A9, and typed the cell reference for cell B5.

Create GetPivotData Formula

In the GetPivotData function syntax, there are the following arguments.

The first 2 arguments are required:

• data_field: Name of pivot table field in the Values area, that has the data you need. Enter a text string, inside double quotation marks, or refer to cell that contains the pivot field name
• pivot_table: Reference to any cell in the pivot table that contains the data you need
• field1, item1: (optional) From 1 to 126 pairs of field names and pivot item names, to pull specific data from the data field.

GetPivotData Formula

In the screen shot below, the GETPIVOTDATA formula has the following values for its arguments:

=GETPIVOTDATA("Total",$A$3,"Product","File Folders")

• data_field: "Total" is the name of the pivottable field in the values area
• pivot_table: $A$3 refers to a cell in the pivot table
• field1, item1: (optional) Get data from "Product" field, and "File Folders" item in that field

Ex 1) Cell References in GetPivotData

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 the following formula in cell E4, to get the total sales for the paper product:

• =GETPIVOTDATA("Total",$A$3,"Product","Paper")

Next, follow these steps to make the formula flexible:

• Select cell E4
• In the formula bar, select the item name, including the quote marks: "Paper"
• Next, click on cell A4 on the worksheet, where the Paper product name appears
• The cell reference, A4, will automatically replace the selected text in the formula
  • =GETPIVOTDATA("Total",$A$3,"Product", A4)
• Press the Enter key, to complete the formula change

Drag GetPivotData Formula Down

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.
• In column E, there is a correct total for each of the Product field items, based on the item names in column A.

Ex 2) Cell References For Value 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 that simple change to the formula, it returns the correct result.

Ex 3) 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 see the steps for using dates in a GetPivotData formula, you can watch this short video. There are written steps below the video.

GetPivotData Examples - Using Dates

To prevent errors for dates, you can use one of the following methods, and there are detailed examples below:

-- 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

Match 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 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 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 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)

Use TEXT Function

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

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.

Video Timeline

• 00:00 Worksheets and Pivot Tables
• 00:50 Dashboard Formula
• 02:07 Add INDIRECT Function
• 03:28 Test Dashboard Drop Down

GetPivotData Example: Select Specific Pivot Table

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:

1. In cell C6, select East from the drop down list
2. Select cell D6, and type an equal sign
3. Click on the PT_East sheet
4. 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. It shows the total from the specified pivot table.

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.

FAQ: GetPivotData Function

1) How do I turn off GetPivotData in Excel?

To turn off the automatic GetPivotData formulas, follow these steps:

1. Select any cell, in any pivot table
2. On the Excel Ribbon, click the PivotTable Analyze tab
3. Click the drop-down arrow on the PivotTable command
4. Next, click the drop-down arrow for Options
5. In the list of options, click the Generate GetPivotData command, to toggle that feature off or on

NOTE: This is an application-level setting, and will affect ALL Excel files that you open on your computer.

Download the Sample File

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

More Pivot Table Resources

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table