I 💓 GETPIVOTDATA and why you should too!

Excel's GETPIVOTDATA function enables you to quickly and easily build robust reports that reference PivotTables that won't break formulas when filters are applied.
🔻 DOWNLOAD EXCEL FILE HERE: www.myonlinetraininghub.com/e...
🎓 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
💬 Please leave me a COMMENT. I read them all!
🧟‍♀️ CONNECT with Mynda on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
0:39 Enable autogenerating GETPIVOTDATA
1:15 The sample data - important for understanding how GETPIVOTDATA references grouped dates
1:48 Referencing value cells in PivotTables
2:43 Making GETPIVOTDATA formulas dynamic
4:57 Data Validation with GETPIVOTDATA
5:52 Tips for working with GETPIVOTDATA

[@makisalimhussain2334]

Thank you Mynda for this informative tutorial, 👌

[@MyOnlineTrainingHub]

You are most welcome!

[@vijayarjunwadkar]

Yes, Now the purpose and usage of GETPIVOTDATA is more clear and I do love it! Thanks Mynda for this succinct tutorial! 😊👍

[@MyOnlineTrainingHub]

So pleased to hear that, Vijay! 😊

[@villiamo3861]

Excellent vid - thank you. I especially liked the data validation idea as a space-saving alternative to slicers (much as I think they're great...)

[@MyOnlineTrainingHub]

Glad it was helpful!

[@XasOverlord]

Thanks for sharing. I also love getpivotdata and use it every day

[@MyOnlineTrainingHub]

Yay, great to hear!

[@hadipermana10]

Thanks a lot for the video Mynda, very helpful !!!

[@MyOnlineTrainingHub]

My pleasure, Hadi!

[@DiamondRock2]

Very Good Hints thank you Myanda

[@MyOnlineTrainingHub]

My pleasure 😊

[@ecvargas5272]

Great content as always. This is extremely useful

[@MyOnlineTrainingHub]

Glad you think so!

[@pariahchild2K]

Wow - I never knew I could do this! I have a formula that calculates an average for a weekly report, but I use the source data to calculate it. The problem is that the worksheet gets saved and edited by someone else, who then removes the source worksheet so that some people cannot see some of the data, leaving only the worksheets that contain the pivot tables; however, this breaks the formula, so I always have to repaste the output value back into the cell to remove the formula prior sharing the worksheet with that person. With what you just shared, I think I can revise the formula so that this is no longer a worry. Thank you so much!

[@MyOnlineTrainingHub]

You sure can 👍 glad this will be useful!

[@adrianodossantosfernandes1236]

I do really like your videos! Congrats

[@MyOnlineTrainingHub]

Awesome, thank you!

[@sedarathnadcd]

Thank you for your hard effots. It is really help me to learn new areas of excel

[@MyOnlineTrainingHub]

Glad to hear that!

[@LeonardoCavalcante]

Thanks for share. Greetings from Brazil 🇧🇷

[@MyOnlineTrainingHub]

My pleasure 😊

[@lww8132]

Why am I not seeing this earlier??? It solves tons of my problems! Thank you so much!!!

[@MyOnlineTrainingHub]

Great to hear 😊

[@KKB-tt4lj]

Great video as usual!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@wayneedmondson1065]

Hi Mynda. Thanks for demystifying GETPIVOTDATA! Very helpful understanding how to make it dynamic. Thanks for sharing :)) Thumbs up!!

[@MyOnlineTrainingHub]

Glad it was helpful, Wayne!

[@sihamef2604]

interesting! thank you

[@hubertg7100]

I ❤ your lectures!

[@MyOnlineTrainingHub]

Thanks so much!

[@StanEby1]

Sheer genius. Wish I had watched this ten laborious hours ago.

[@MyOnlineTrainingHub]

😁 better late than never!

[@chrism9037]

Great video Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@gyozakeynsianism]

Very useful, thank you.

[@MyOnlineTrainingHub]

Great to hear 😊

[@visheshshah1399]

Thank You for Knowledge

[@MyOnlineTrainingHub]

My pleasure, Visheesh!

[@Godsjson]

I LOVE IT!

[@MyOnlineTrainingHub]

Great to hear, Jason!

[@777kiya]

"May be it's too soon.." cracked me up

[@yulinliu850]

Nice!

[@juamlechuga5164]

Thank you very much for your channel, thanks to you I learned a lot. It is possible to make a video related to daily executive report with small graphs but with more numbers; the problem I have is that when putting together a report the pivot tables collide their spaces and leave in some fixed format for printing. A3

[@MyOnlineTrainingHub]

Great to hear! Perhaps you'd be better off using Sparklines if you have a lot of numbers and want small charts: www.myonlinetraininghub.com/excel-sparklines

[@biniesh7097]

Thank you very much!

[@MyOnlineTrainingHub]

You're welcome!

[@stevennye5075]

very well exppalined!

[@MyOnlineTrainingHub]

Thanks so much, Steven!

[@taahatube1]

Thank you very much! love your content! you deserve 100b subs!!!

[@MyOnlineTrainingHub]

Thanks so much 😊

[@taahatube1]

@@MyOnlineTrainingHub your welcome :D

[@joshuamanampiu6489]

Thank you very much

[@MyOnlineTrainingHub]

My pleasure 😊

[@KelvinAdityo]

Thanks much,

[@MyOnlineTrainingHub]

My pleasure, Indra!

[@johnmeijer1246]

I like this. So many times I turned it off. Because I did not understand it. So now I learned not to turn off people when I do not understand them and keep the communication channel open...haha..really nice

[@MyOnlineTrainingHub]

:-) cheers, John!

[@sanskritonapple]

why no one talked about it before? Such amazing tricks!

[@MyOnlineTrainingHub]

Glad you liked it :-)

[@tiongah7690]

great video! I hope that you can point out the advantages/disadvantages of GETPIVOTDATA vs calculated fields, apart of they are being or not being the part of the pivot table. Is there anything where calculated field can or cannot do where GETPIVOTDATA can?

[@MyOnlineTrainingHub]

GETPIVOTDATA simply extracts values from the PivotTable, it doesn't calculate values like you can with calculated fields unless you nest it or perform further math on the results. Plus, it's not part of the PivotTable, so not nearly as useful.

[@ennykraft]

I wish you had shown us more examples of how to make the function dynamic. It worked for this example but I ran into difficulties when I had subtotals.

[@MyOnlineTrainingHub]

The same concept applies to sub-totals, so I'm not sure why you're having trouble. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@nigeltufnel4031]

Cube formulas are pretty cool as well

[@MyOnlineTrainingHub]

Yes, but they don’t work with regular PivotTables. They’re a lesson for another day 😊

[@barttitulaerexcelbart9400]

I do know a bit of GETPIVOT and CUBE, but I do not understand the real benefit of the CUBE functions.

[@joshuagodec3124]

Thank you so much these are great. Can you please make a video for a workaround. When you add a calculated field to a pivot table, it no longer narrows down the fields of a slicer.. For example, if you have a filter for year, and another for month and then another for days. If you filter for Feb, it will show all 31 days in the slicer when there is a calculated field instead of showing the 28, and making 29,30,31 unclickable. I hope I make sense! Maybe you already have a video for this.. If so, please share because I may not be searching correctly.

[@MyOnlineTrainingHub]

Glad the video was helpful, Joshua. When working with dates it's best to allow the PivotTable to group the dates, then the filtering of dependent slicers will work correctly.

[@hollydunne2687]

Brilliant video, thank you! I'm still getting used to pivot tables- is there a difference between using the getpivotdata function and calculated fields please?

[@MyOnlineTrainingHub]

Thanks, Holly! A calculated field is a formula that’s part of the PivotTable, where as GETPIVOTDATA is a formula that references the data in a PivotTable.

[@royyajayanthkumar6723]

I ❤️ simply

[@MyOnlineTrainingHub]

:-) thanks!

[@leezygeezer]

Hi, great tutorial. How will this work when the size of your pivot table gets bigger or smaller?

[@MyOnlineTrainingHub]

The GETPIVOTDATA function references a field value, rather than a specific cell, therefore as the PivotTable changes size, the GETPIVOTDATA function is able to locate the relevant value. Try it and you will see.

[@josephbradshaw4092]

Where can I get that cool shirt? Thank you for your videos

[@MyOnlineTrainingHub]

:-) I think you can get them from Redbubble.com

[@mulertechnology8431]

Thank you I have one question how to create dashboard excel for high school?

[@MyOnlineTrainingHub]

You can use the techniques I teach in my dashboard tutorials to apply to any data. The charts etc. will depend on the KPIs you want to measure. There’s no one size fits all.

[@madhav771]

Hi mam I have a doubt Let's say I have 2 pivot tables and iam using getpivotdata to divide a number from first pivot table to the second. After getting the values, how to sort them in descending order. Because I couldn't able to order the numbers. Kindly help

[@MyOnlineTrainingHub]

Hi Maddy, if you have Microsoft 365 you can wrap the GETPIVOTDATA formulas in the SORT function: www.myonlinetraininghub.com/excel-functions/excel-sort-function

[@sandipkh2282]

How many extra rows and columns that we can add to excel spreadsheet ? Or, is it limited to 10,48,576 rows and 16,384 columns ?

[@MyOnlineTrainingHub]

You missed a digit from the number of rows, there's 1,048,576. If you need more rows then I recommend you use Power Pivot which can handle tens of millions of rows of data. More on Power Pivot here: www.myonlinetraininghub.com/power-pivot-course

[@sandyj1561]

Sorry, I need to pull the data behind a line in a pivot table into another sheet without having to manually click on the total in the line of the pivot. Does that make sense and is it even possible?

[@MyOnlineTrainingHub]

Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@gregbernard7861]

I use it sparingly in non complex scenarios. I don’t think it worked well when using against alternate file pivot. I think the source file needed to be open kind of like SUMIF.

[@MyOnlineTrainingHub]

Yes, it doesn't work on external closed files. Best to avoid that where possible.

[@OmarAl93]

Whenever I'm trying to reference the cell to a total cell in the PivotTable, it's not turning me a Getpivotdata like in your case automatically! Is there another workround to use the function?

[@MyOnlineTrainingHub]

Hi Omar, you may have turned off GETPIVOTDATA. You can turn it back on via the PivotTable tab > then on the far left under the Options drop down, make sure GETPIVOTDATA has a check mark against it.

[@darwinmercado7943]

Hi I was trying to make my pivot dynamic but my pivot are in measures data from the data model and it always shows an error #ref

[@MyOnlineTrainingHub]

The GETPIVOTDATA syntax for Power Pivot PivotTables is slightly different. See this tutorial: www.myonlinetraininghub.com/getpivotdata-function-power-pivot

[@sandipkh2282]

I am just curious, do you work at Microsoft ?👍

[@MyOnlineTrainingHub]

Hi Sandip, no, I don't work at Microsoft. However, Microsoft recognise me as an Excel MVP for my contribution to helping the Excel community :-)

[@mugeshm970]

[@therencevaldez2841]

Hi this maybe slightly out of topic, but why is getpivotdata with text and amount, the $ and comma disappears in Chart Title. Thanks

[@MyOnlineTrainingHub]

Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@Leontor1234]

I don't get it ... I mean, you used the GETPIVOTDATA formula to get the values that would appear in the Pivot Table. But usually for that we can use SUMIFS , right ? Why would I use GETPIVOTDATA ?

[@MyOnlineTrainingHub]

Because a PivotTable can automatically summarise your data in a fraction of the time it takes to write all those SUMIFS formulas and built the table manually. PivotTables are also more robust and will automatically add new items to be summed, as well as sort, filter and more.

[@Leontor1234]

@@MyOnlineTrainingHub i agree about the pivotable, I dont understand why I would use GETPIVOTDATA instead of sumifs ! Or is it equivalent somehow ?

[@MyOnlineTrainingHub]

Because when you summarise your data with a PivotTable it does the same thing SUMIFS does with a fraction of the effort. If you then want to extract some of the PivotTable's results to another sheet/cell, then you'd use GETPIVOTDATA to link to the PivotTable. That way if more data is added to the PivotTable, the GETPIVOTDATA formulas won't break.

[@theresadubonetti181]

I have a check mark on GETPIVOTDATA under “options”, but it is not working when I type = the click on a cell. It is just inputting the cell letter/number. I am getting a little aggravated. I keep getting a REF error no matter what I do.

[@MyOnlineTrainingHub]

Sounds like you’re referencing a row or column label cell. GETPIVOTDATA only works with value field cells.