Use the GETPIVOTDATA function to extract values from a Pivot Table

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

The GETPIVOTDATA function is a useful way to reference the data contained in pivot tables or look up values contained within it. Using GETPIVOT you can look up a series of row/column field values and find the corresponding sum/count value from the Pivot table, using it in a similar way to SUMIF.

Since this is the default way Excel refers to values in a pivot table (though this can be turned off in the ‘ Formulas ‘ section of Excel options), the best starting point for building this function is to refer to any pivot table column value and Excel will automatically reference it using a GETPIVOTDATA formula. This formula, however, contains all hard-coded values and so will need to be tweaked in order to be able to make it dynamically refer to a cell value (e.g a field for which you want to obtain data). Once the formula is dynamically referring to a list of ‘lookup’ values and returning the corresponding numbers from the pivot table, the formula can then be dragged down/across all the lookup values.

For example in the Topic Example Workbook, on the ‘GETPIVOTDATA Analysis’ sheet we have a series of state/payment type combinations. Using a GETPIVOTDATA function will allow us to return the corresponding total shipping fee (as shown in the Pivot table) for each combination.

In the Topic Example Workbook above if we refer to the Pivot table value in cell B10 of the Pivot Table (referring to the Total shipping fee by Check in NY) the formula that comes up is =GETPIVOTDATA(“Shipping Fee”,’Pivot Table’!$A$3,”Ship State/Province”,”NV”,”Payment Type”,”Check”)

Looking at each component of the formula in turn:

• “Shipping Fee” – This is the total we want to return (the field in our values bucket). This will generally remain static if there is only one field in the values bucket.
• ‘Pivot Table’!$A$3″: this is just a reference to the location of the pivot table (the starting cell). If we give the pivot table a name (in the ‘Pivot Table Options’ ribbon that appears when in the pivot table) then we can refer to that instead. But provided we always want to refer to this pivot table, we need not change this reference.
• “Ship State/Province”- this is the name of the first field (row/column value) in the pivot table. Provided the structure of the pivot table remains the same we also don’t need to change this
• “NV” – this is the hard coded value of the State field; if we want to flex this according to a value we have in a cell then we just need to delete the word ‘NV and replace it with a cell reference. In the above example, in cell C11 of the ‘GETPIVOTDATA analysis’ tab, we want it to refer to the state value contained in cell B11 so we change our GETPIVOTDATA formula to =GETPIVOTDATA(“Shipping Fee”,’Pivot Table’!$A$3,”Ship State/Province”,B11,”Payment Type”,”Check”)
• -“Payment Type” – the second field (row/column value) in the pivot table.
• -“Check” – the hard-coded value for the ‘Payment Type’ field. Again if we want to make this dynamically refer to the list of payment types we have in cells C27:C48 of the ‘GETPIVOTDATA Analysis’ tab then we just replace the word ‘Check’ with the cell reference in that range. So our GETPIVOTDATA formula becomes =GETPIVOTDATA(“Shipping Fee”,’Pivot Table’!$A$3,”Ship State/Province”,B27,”Payment Type”,C27)

Now that the GETPIVOTDATA formula is dynamic we can drag it down to see the value for any State/Payment Type combination, the full list of which is in the range B27:C48.

If we want to capture any invalid combinations or values we can use the IFERROR function around the formula to return a more friendly error message (e.g. “Invalid combination”)

In the ‘GETPIVOTDATA Analysis’ tab, populate the yellow cells with the appropriate GETPIVOTDATA formulas:

• In cell C3 refer to cell B10 on the ‘Pivot Table’ sheet and view the GETPIVOTDATA formula that is shown. (If no GETPIVOTDATA formula is shown, change your Excel Options)
• In cell C11, repeat the formula above. Then tweak the GETPIVOTDATA formula so that it refers to the state in cells B11:B21 rather than the hard-coded value of ‘NV’. Drag down the formula to the range C11:C21.
• In Cell E27 also repeat the GETPIVOTDATA formula by referring to cell B10 on the Pivot Table Sheet. This time tweak the formula to refer to the state in cells B27:B48 and the Payment Type in cells C27:C48.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.