I 💓 GETPIVOTDATA and why you should too!

Рет қаралды 69,604

Күн бұрын

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

Пікірлер: 102

@makisalimhussain2334 2 жыл бұрын

Thank you Mynda for this informative tutorial, 👌

@MyOnlineTrainingHub 2 жыл бұрын

You are most welcome!

@vijayarjunwadkar 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

So pleased to hear that, Vijay! 😊

@hadipermana10 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure, Hadi!

@XasOverlord 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

Yay, great to hear!

@DiamondRock2 2 жыл бұрын

Very Good Hints thank you Myanda

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@hubertg7100 Жыл бұрын

I ❤ your lectures!

@MyOnlineTrainingHub Жыл бұрын

Thanks so much!

@ecvargas5272 2 жыл бұрын

Great content as always. This is extremely useful

@MyOnlineTrainingHub 2 жыл бұрын

Glad you think so!

@villiamo3861 2 жыл бұрын

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 2 жыл бұрын

Glad it was helpful!

@LeonardoCavalcante 2 жыл бұрын

Thanks for share. Greetings from Brazil 🇧🇷

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@sedarathnadcd Жыл бұрын

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

@MyOnlineTrainingHub Жыл бұрын

Glad to hear that!

@wayneedmondson1065 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

Glad it was helpful, Wayne!

@KKB-tt4lj 2 жыл бұрын

Great video as usual!

@MyOnlineTrainingHub 2 жыл бұрын

Glad you enjoyed it!

@777kiya 2 жыл бұрын

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

@adrianodossantosfernandes1236 2 жыл бұрын

I do really like your videos! Congrats

@MyOnlineTrainingHub 2 жыл бұрын

Awesome, thank you!

@StanEby1 Жыл бұрын

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

@MyOnlineTrainingHub Жыл бұрын

😁 better late than never!

@visheshshah1399 2 жыл бұрын

Thank You for Knowledge

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure, Visheesh!

@lww8132 Жыл бұрын

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

@MyOnlineTrainingHub Жыл бұрын

Great to hear 😊

@gyozakeynsianism 2 жыл бұрын

Very useful, thank you.

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear 😊

@Godsjson 2 жыл бұрын

I LOVE IT!

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Jason!

@chrism9037 2 жыл бұрын

Great video Mynda!

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Chris!

@stevennye5075 2 жыл бұрын

very well exppalined!

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much, Steven!

@taahatube1 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much 😊

@taahatube1 2 жыл бұрын

@@MyOnlineTrainingHub your welcome :D

@biniesh7097 2 жыл бұрын

Thank you very much!

@MyOnlineTrainingHub 2 жыл бұрын

You're welcome!

@johnmeijer1246 2 жыл бұрын

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 2 жыл бұрын

:-) cheers, John!

@sihamef2604 2 жыл бұрын

interesting! thank you

@pariahchild2K 2 жыл бұрын

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 2 жыл бұрын

You sure can 👍 glad this will be useful!

@joshuamanampiu6489 2 жыл бұрын

Thank you very much

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@yulinliu850 2 жыл бұрын

Nice!

@nigeltufnel4031 2 жыл бұрын

Cube formulas are pretty cool as well

@MyOnlineTrainingHub 2 жыл бұрын

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

@barttitulaerexcelbart9400 2 жыл бұрын

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

@KelvinAdityo 2 жыл бұрын

Thanks much,

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure, Indra!

@sanskritonapple 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it :-)

@juamlechuga5164 2 жыл бұрын

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 2 жыл бұрын

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

@ennykraft 2 жыл бұрын

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 2 жыл бұрын

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

@royyajayanthkumar6723 2 жыл бұрын

I ❤️ simply

@MyOnlineTrainingHub 2 жыл бұрын

:-) thanks!

@tiongah7690 2 жыл бұрын

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 2 жыл бұрын

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.

@joshuagodec3124 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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.

@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.

@mulertechnology8431 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

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.

@josephbradshaw4092 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

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

@madhav771 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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 9 ай бұрын

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 9 ай бұрын

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.

@darwinmercado7943 2 жыл бұрын

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 2 жыл бұрын

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

@sandipkh2282 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

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 2 жыл бұрын

@therencevaldez2841 16 күн бұрын

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

@MyOnlineTrainingHub 16 күн бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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