Excel GETPIVOTDATA Function to Pull Data from a PivotTable

In this video, we explain the GETPIVOTDATA function of Excel. Why is it useful, how to use it and how to turn it off when you do not need it.
Learn more advanced Excel tricks bit.ly/3CGCm3M
The GETPIVOTDATA function in Excel is used to query and extract data from a PivotTable. It can be incredibly useful but is not always the approach that you want.
In this video, we see an example of how useful it can be, but then another where it did not help so we disable its functionality.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

[@wayneedmondson1065]

Hi Alan.. thanks for this great video on GETPIVOTDATA. The way you explain it makes total sense. Also, I like the tip of disabling it for certain circumstances or just directly typing in a cell reference if needed on the fly. Thanks for the insights. Thumbs up!

[@Computergaga]

Thank you Wayne.

[@rhcpmorley]

Big thanks - spent 2 hours trying to get some data out in the right format (with 'help' of other online tuition) and failing. Then spent 5 mins watching your video, and bingo! got it done in about 2 minutes!

[@Computergaga]

Excellent work! Thanks Simon.

[@user-vj7nl4xf9e]

@@Computergaga how did you know his name is simon??

[@TheTeguhdinatazt]

thankss alan, ... this is awesome, and helpp me

[@Computergaga]

Great to hear! Thanks Teguh.

[@thihazaw3301]

Thank you sir

[@Computergaga]

You're very welcome 👍

[@lulut2010]

Yes very useful thank you

[@Computergaga]

Brilliant! Thank you.

[@AkaExcel]

Awesome trick, thank you!

[@Computergaga]

Thank you.

[@kundansharma1417]

Thanks..Where can i download file for practice...

[@jasonjackson4555]

Great tutorial!
Question: In your example, would there a way to manipulate the GetPivotData function so that it always looks for the data from the most recent year?
I want to apply this function to a sheet where I’m listing sales discount percentages. I want to display the discount % entry that has the most recent “last modified” date in my data table. Every product/customer has its own unique “last modified” date. Would I have to do some sort of max value lookup in the source data table for the product/customer combination to get the most recent date and then use that value as the item argument in the getpivotdata function?

[@Computergaga]

Does this video help Jason - th-cam.com/video/KTKU0xYGLbc/w-d-xo.html
This returns the last transaction date. You then need the value associated with that. I'll have a proper look if I get a chance.

[@williamcattr267]

How about finding the average transaction value of customers in an Excel Pivot table from a particular geographic area for transactions where the quantity of items ordered is exactly 1?
Anybody know?

[@gabrielguzman3784]

Question: I have a Pivot Chart that is pulling the data in number format but the Pivot Table has it listed in percentage format. I know each column has a cell format and within the Pivot Table I have overwritten the cell format to have percentage rather than number. Does anyone know a workaround to have the chart pull the data listed within the cell and not the format of the column? Thanks!

[@NatGovender]

Hi Alan, When press the = and select a in the Pivot Table it does not automatically generate the the "GETPIVOTDATA" function.
How can i change it so that it does?

[@Computergaga]

If you click the analyze tab of the PivotTable, and then Options there is an option to Generate GetPivotData.

[@NatGovender]

@@Computergaga Hi, After some research I realised that the function only works on numeric data and not text data.

[@cooljoker2027]

the problem i used it but when u add new data will show in pivot tible but not in the shit new range that we created

[@Computergaga]

Depending on your scenario, another method can be used. GETPIVOTDATA looks to pull a specific value from the table. You may need a FILTER or Power Query approach.

[@fouadhoblos3611]

First time I learn something about this damned gtpivotdata function.. I always got #ref.. Any idea how to make use of it to return values in pivot tables that are aggregates? like by year quarters months ..

[@Computergaga]

We can get these aggregates with the grouping feature of PivotTables. Right-click a date and Group By.
GETPIVOTDATA is just a lookup for an existing PivotTable value. If you have a PivotTable with the value for Qtr 3 for example, then sure GETPIVOTDATA could then return it for you.

[@fouadhoblos3611]

@@Computergaga hello.. thank you a lot for the comment.. however, that is not exactly my problem..
Once grouping by dates (year, quarter, month) is done than put as columns, using getpivotdata to extract values in aggregate cells from the pivot table yields #ref..
For example: get sum of such category for such quarter/month..
We need that in finance to put certain aggregates in charts and use them in ratios..
While the pivot table gets those aggregates, extracting them for ratios and charts is a haunting nightmare..

[@williamenglish9036]

Something about this gave me a headache.

[@Computergaga]

😯

[@ariefahmedshaik729]

Horrible slang