GETPIVOTDATA: The Easy Way to Retrieve PivotTable Data in Microsoft Excel

Most people know that you can reference one or more cells, tables and their column headers, or named ranges in Excel formulas. However, fewer know that you can reference specific data points in PivotTables using the GETPIVOTDATA function. Here’s how.

The GETPIVOTDATA Syntax

Whenever you use the GETPIVOTDATA function, you must input atyou least two arguments. Whether you input more than two depends on what you want to retrieve. Here’s the syntax:

=GETPIVOTDATA(a,b,c¹,c²...)

where

• a (required) is the name of the PivotTable field containing the data you want to retrieve,
• b (required) is a reference to a single cell, multiple cells, or a named range in the PivotTable, and
• c¹ and c² (optional) are the first of up to 126 pairs of field names (x¹) and item names (x²) defining the data you want to retrieve.

Argument a must always be wrapped in double quotation marks. The same can be said for field names (x¹) and item names (x²), unless they’re dates, numbers, or cell references.

Using GETPIVOTDATA to Retrieve Grand Totals

Because the GETPIVOTDATA function extracts data based on field names and item names, it’s better at dealing with structural changes to a PivotTable than direct cell references. This is why it’s a great way to retrieve grand totals for, say, a report.

In the examples used in this section, the PivotTable’s rows are countries, the columns are products, and the values are sales.

To pull grand totals from a PivotTable, you first need to make sure they’re displayed. To do this, select any cell in the PivotTable, and in the Design tab on the ribbon, expand the “Grand Totals” drop-down menu. Then, click “On For Rows And Columns.”

Now, the PivotTable shows grand totals for each row and column, as well as a grand total of everything combined in the bottom-right cell.

Retrieve the Grand Total of All Data in a PivotTable

The most basic example of a GETPIVOTDATA formula in Microsoft Excel is when the function is used to retrieve a grand total of all data in a PivotTable. In fact, the only thing you need to type is the equal (=) sign!

To extract a grand total from a PivotTable into another cell in your workbook, select the cell where you want the retrieved grand total to be displayed, and type the equal sign:

=

Then, click the cell in the PivotTable that displays the grand total, and notice that the formula is inserted automatically:

=GETPIVOTDATA("Sales",Totals!$A$3)

In this example, “Sales” is the field containing the data to be retrieved, and Totals!$A$3 is an absolute reference to the top-left cell in the PivotTable in the Totals worksheet.

When you press Enter, the grand total is successfully extracted.

The GETPIVOTDATA function only retrieves values, not formatting, so you need to apply cell, text, and number formatting manually.

Retrieve the Total of a PivotTable Column or Row

You can follow the same principle to retrieve a column or row’s grand total from a PivotTable in Excel—simply type the equal sign, click the cell containing the grand total at the bottom of a given column or the end of a given row, and press Enter.

Here, the sales total for the product named amarilla (in column B of the PivotTable in the sheet named Sales) is retrieved:

=GETPIVOTDATA("Sales",Totals!$A$3,"Product","Amarilla")

where “Sales” is the value you want to retrieve, Totals!$A$3 references a cell in the relevant PivotTable, and the “Product”,”Amarilla” pairing tells Excel to find the field named Product and then locate the item named amarilla within that field.

However, at the moment, the formula isn’t dynamic. In other words, if you wanted to retrieve the grand total for another product, you would need to edit the existing GETPIVOTDATA formula or generate a new one.

Instead, rather than hard-coding the product name in the final argument, you could reference a cell containing the product name:

=GETPIVOTDATA("Sales",Totals!$A$3,"Product",A1)

Cell references in formulas don’t need to be enclosed in double quotation marks.

Now, when you type a different product name in cell A1, the total updates accordingly.

Go one step further and create a drop-down list of products in cell A1 using Excel’s data validation tool. This saves you from having to type the products manually.

Follow the same process to return the grand total at the end of a row in the PivotTable. The following formula returns the sales total in Canada:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country","Canada")

where “Sales” is the value to retrieve, Totals!$A$3 tells Excel where the PivotTable is located, and the “Country”,”Canada” pairing tells Excel to find the field named country and then locate the item named Canada within that field.

Then, to make the formula dynamic, replace “Canada” with a reference to a cell containing the country’s name:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country",A1)

Using GETPIVOTDATA to Retrieve a Specific Data Point

As well as retrieving grand totals, Excel’s GETPIVOTDATA function can also retrieve a specific data point at the intersection of a specified column and row.

Suppose you want to see the total sales for the product named montana in Mexico.

As always with the GETPIVOTDATA function, the best approach is to let Excel generate the formula for you, and then you can make tweaks where necessary. So, after typing the equal sign into the cell where you want the resultant value to be located, click the relevant cell in the PivotTable. In this case, it’s cell D8, the intersection of the Montana column and the Mexico row.

As a result, Excel generates this formula:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country","Mexico","Product","Montana")

and when you press Enter, the montana sales total for Mexico is correctly returned.

Now, you can substitute the hard-coded values in the formula for cell references. Specifically, rather than referencing “Mexico” for the country, you can reference cell B2, and rather than referencing “Montana” for the product, you can reference cell A2:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country",B2,"Product",A2) 

​​​​​​

As a result, when you change the product and country in cells A2 and B2, respectively, GETPIVOTDATA returns the corresponding value.

Using GETPIVOTDATA to Retrieve a Figure Based on a Date

One way to see date-specific information in a PivotTable is to insert a timeline slicer. However, where timeline slicers let you filter a PivotTable based on a certain time period, the GETPIVOTDATA function lets you extract information from a PivotTable and takes up less space.

The key to using GETPIVOTDATA with dates is to structure the PivotTable correctly. In the examples used in this section, the PivotTable’s rows are dates, the columns are products, and the values are sales. Also, grand totals for rows and columns are displayed.

To make the data clearer, I right-clicked one of the dates in column A, clicked “Group,” selected “Months” and “Years,” and clicked “OK.”

As a result, the dates are split into 2013 and 2014, with each month given its own row.

You can see this change in the PivotTables Fields pane, where Years (Date) and Months (Date) are displayed as separate Rows fields.

Retrieve a Grand Total From a Specified Year

Now that the PivotTable is properly structured by year and month, you can use the GETPIVOTDATA function to extract a grand total for a given year.

Imagine you want to retrieve the total for 2013. To do this, type the equal symbol, and click the cell in the PivotTable that contains this value. Straightaway, Excel generates the formula for you:

=GETPIVOTDATA("Sales",Sales!$A$3,"Years (Date)",2013)

where “Sales” is the field containing the data to be retrieved, and Sales!$A$3 is an absolute reference to the top-left cell in the PivotTable in the Sales worksheet, “Years (Date)” is the field name, and 2013 is the item name within that field.

Now, make the formula dynamic. In other words, replace the hard-coded year with a cell reference. In this case, the year is in cell A2:

=GETPIVOTDATA("Sales",Sales!$A$3,"Years (Date)",A2)

When you press Enter, the total for 2013 is displayed in cell B2.

Because the formula is dynamic, if you change the year to 2014, the figure updates accordingly.

Retrieve a Grand Total From a Specified Month of a Specified Year

As well as retrieving a yearly grand total from a PivotTable in Excel, you can extract a total for a specific month of a specified year.

As always, start by typing the equal symbol, and select the relevant cell from the PivotTable. So, for example, to return the grand total for January 2014, you’ll need to select cell H11.

Here’s the formula that Excel generates automatically:

=GETPIVOTDATA("Sales",Sales!$A$3,"Months (Date)",1,"Years (Date)",2014)

Notice how the month is stated as a number (1 for January). As a result, when you make this formula dynamic, you need to make sure that whatever you input results in a number between 1 (January) and 12 (December).

This is where the MONTH function comes into play. Here’s the dynamic formula:

=GETPIVOTDATA("Sales",Sales!$A$3,"Months (Date)",MONTH(A2&1),"Years (Date)",B2)

where

• “Months (Date)” is the first row field name,
• The item name in this first field is represented by MONTH(A2&1), which takes the three-letter month in cell A2 and turns it into a numerical value between 1 and 12,
• “Years (Date)” is the second row field name, and
• B2 tells Excel that the item name in this second field is determined by the value in cell B2, which, in this case, is 2014.

Retrieve a Specific Data Point Based on a Date

Let’s combine all the tips explained so far in this guide to return a specific sales total for a given product in a given month of a given year.

First, type the equal symbol, and click an individual data point within the PivotTable to generate the formula you’re going to work with.

Here’s the resultant formula:

=GETPIVOTDATA("Sales",Sales!$A$3,"Product","Velo","Months (Date)",3,"Years (Date)",2014)

Now, adapt the formula to make it dynamic. Change “Velo” to A2 (the cell containing the product name), “2014” to C2 (the cell containing the year), and 3 to MONTH(B2&1), which converts the month in cell B2 to a number:

=GETPIVOTDATA("Sales",Sales!$A$3,"Product",A2,"Months (Date)",MONTH(B2&1),"Years (Date)",C2)

Then, press Enter to see the result.

Finally, test that the formula works as expected by changing the details in cells A2 to C2 and cross-referencing the result with the PivotTable.

Points to Note When Using GETPIVOTDATA

Before you go ahead and use this handy function in your Excel workbooks, take note of these final pointers:

• Typing = and clicking a cell in a PivotTable only triggers a GETPIVOTDATA formula if the tool is enabled. To check this, select the PivotTable, open the “PivotTable Analyze” tab, and in the Options drop-down menu beneath the PivotTable’s name, see whether the Generate GetPivotData option has a check mark next to it. If it doesn’t, click it once.

• If the second argument of a GETPIVOTDATA formula references a cell that is not a PivotTable, you’ll see a #REF! error. The same goes for any arguments that do not match a field name in the PivotTable.
• Remember to use double quotes in the GETPIVOTDATA arguments, unless they contain dates, numbers, or cell references.
• If you use a whole date as a GETPIVOTDATA argument, rather than just a month or year, you’ll need to use the DATE function to turn it from text to a recognized date format.

---

GETPIVOTDATA is just one of the many lookup functions in Microsoft Excel, but its uniqueness in how it works with PivotTables sets it apart from the rest. If you’re working with data in a regular range or formatted as an Excel table, you might consider using XLOOKUP, INDEX with XMATCH, or FILTER instead.