The GETPIVOTDATA Function in Excel

0 Flares Filament.io 0 Flares ×

The GETPIVOTDATA function is used to query and extract data from a PivotTable. It is essential a PivotTable lookup formula.

This function can be extremely useful. When your PivotTable updates and may grow or reduce in size, or the field items may change order, GETPIVOTDATA will continue to extract the correct data.

In this blog post we will show why the GETPIVOTDATA function is useful with an example, but then show an example when we do not want it and how to turn the feature off.

Watch the Video

Using the GETPIVOTDATA Function

The function is switched on by default so when you write a reference to the cell of a PivotTable, the GETPIVOTDATA function is used by Excel.

For example, if I click in a cell and type =, and then click on cell D9 of the PivotTable below, this function is entered.

=GETPIVOTDATA("Total Sales Value",$A$3,"Product Category","Grains/Cereals","Years",2012)

A PivotTable to extract data from

This says to extract data from the Total Sales Value field of the PivotTable found in cell A3. Cell A3 is the top left corner cell of the PivotTable. It could have been any cell, but the top left corner is always chosen by default.

It then specifies to retrieve the value from the Grains/Cereals item of the Product Category field, and the 2012 item of the Years field.

So it is a very specific and structured reference. If the PivotTable changes in layout, it continues to retrieve the right data. While I cannot be sure that cell D9 would also be the value I wanted.

Making GETPIVOTDATA More Dynamic

For those that do not like GETPIVOTDATA (and there are many), the argument will be that it is too structured.

So in this example, lets make it more dynamic.

Using the same PivotTable as before I have inserted a drop down list in cell G4. This is a list of the different product categories.

Making the Pivottable more dynamic

So instead of having a GETPIVOTDATA function returning data like this.

=GETPIVOTDATA("Total Sales Value",$A$3,"Product Category","Seafood")

I could reference cell G4, so that whatever category I select the correct value is returned.

=GETPIVOTDATA("Total Sales Value",$A$3,"Product Category",G4)

By combining this useful function with cells on a spreadsheet that may contain formulas, or form controls, gets the most out of it.

And for those that dislike the GETPIVOTDATA function due its specific nature, it takes that argument away.

When GETPIVOTDATA Does Not Help

In this example, I have a PivotTable with a Country filter and the Product Category values sorted in a descending order so that the largest values are at the top.

GETPIVOTDATA not helping

Now if somebody uses the filter to change the country, the PivotTable values will move.

So lets imagine I want to return the value of the best selling product category from the PivotTable.

If I type = into a cell and then click on cell B4 (the cell containing the value of the best selling product category), the GETPIVOTDATA function write this.

=GETPIVOTDATA("Total Sales Value",$A$3,"Product Category","Dairy Products")

But I am not interested in dairy products. That just happens to be the best seller in this country. But if I change the country, it could be a different product category in the number 1 position.

So instead I would just type =B4. By typing in the reference, it bypasses the GETPIVOTDATA function.

Turning Off GETPIVOTDATA

If you do decide that the function is not helping you much in how you want to reference PivotTable data. You can switch it off.

Then when you click on the cells of a PivotTable, Excel will use normal spreadsheet references instead of GETPIVOTDATA.

To do this;

  1. Click on the PivotTable and then the Analyze tab of the Ribbon under PivotTable tools (Options tab in Excel 2007 and 2010).
  2. Click on the list arrow for the Options button on the far left side and select Generate GetPivotData.

Turn Off Generate GetPivotData

More PivotTables Tutorials

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *