Excel GETPIVOTDATA Function

Use the Excel GETPIVOTDATA Function to retrieve a summary report of your PIVOT TABLE data.

Formula

=GETPIVOTDATA(data_field, pivot_table, [field1,item1], ...)

How to use Excel GETPIVOTDATA Function

  1.    Generate a Pivot Table DATA
  2.    Reference a cell in your PIVOT TABLE as shown in figure 1-3
  3.    Type an equal sign and enter GETPIVOTDATA

Example 1

Use Excel GETPIVOTDATA Function to extract Grand Totals in a PIVOT TABLE data

Figure 1. Extracting Grand Totals of each field

Example 2

Figure 2. Excel GETPIVOTDATA generates an error message – the fields are not included in the report

Example 3

In the table below, the Excel GETPIVOTDATA Function automatically enters a formula in cell G17.

  1.    Click cell G17
  2.    Press an equal sign
  3.    Click C17 in your PIVOT TABLE
  4.    Press enter to assign a formula to G17 (cell displays the total number of trousers)

Figure 3. Extract total for each product

Explanation

The benefit of using Excel GETPIVOTDATA Function is the use of criteria to return data from your PIVOT TABLE.

In the formula above =GETPIVOTDATA(“Sum of Total”,$B$14,”Sales Product”,”trousers”) retrieves data from Sum of Total data_field in the pivot_table starting at cell $B$14 (data source). The data is limited to the “Sales Product” known as “trousers”.

Tip

  • Spell fields correctly for Excel GETPIVOTDATA Function not to return #REF error
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar