I 💓 GETPIVOTDATA and why you should too!

Подписаться 617 тыс.

Просмотров 70 тыс.

50% 1

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

Наука

Опубликовано:

4 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 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!

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

@XasOverlord 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Yay, great to hear!

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

@ecvargas5272 2 года назад

Great content as always. This is extremely useful

@MyOnlineTrainingHub 2 года назад

Glad you think so!

@adrianodossantosfernandes1236 2 года назад

I do really like your videos! Congrats

@MyOnlineTrainingHub 2 года назад

Awesome, thank you!

@lww8132 Год назад

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

@MyOnlineTrainingHub Год назад

Great to hear 😊

@chrism9037 2 года назад

Great video Mynda!

@MyOnlineTrainingHub 2 года назад

Cheers, Chris!

@DiamondRock2 2 года назад

Very Good Hints thank you Myanda

@MyOnlineTrainingHub 2 года назад

My pleasure 😊

@taahatube1 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Thanks so much 😊

@taahatube1 2 года назад

@@MyOnlineTrainingHub your welcome :D

@LeonardoCavalcante 2 года назад

Thanks for share. Greetings from Brazil 🇧🇷

@MyOnlineTrainingHub 2 года назад

My pleasure 😊

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

@KKB-tt4lj 2 года назад

Great video as usual!

@MyOnlineTrainingHub 2 года назад

Glad you enjoyed it!

@777kiya 2 года назад

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

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

@visheshshah1399 2 года назад

Thank You for Knowledge

@MyOnlineTrainingHub 2 года назад

My pleasure, Visheesh!

@gyozakeynsianism 2 года назад

Very useful, thank you.

@MyOnlineTrainingHub 2 года назад

Great to hear 😊

@Godsjson 2 года назад

I LOVE IT!

@MyOnlineTrainingHub 2 года назад

Great to hear, Jason!

@stevennye5075 2 года назад

very well exppalined!

@MyOnlineTrainingHub 2 года назад

Thanks so much, Steven!

@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

@biniesh7097 2 года назад

Thank you very much!

@MyOnlineTrainingHub 2 года назад

You're welcome!

@sihamef2604 2 года назад

interesting! thank you

@sanskritonapple 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you liked it :-)

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

@joshuamanampiu6489 2 года назад

Thank you very much

@MyOnlineTrainingHub 2 года назад

My pleasure 😊

@yulinliu850 2 года назад

Nice!

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

@KelvinAdityo 2 года назад

Thanks much,

@MyOnlineTrainingHub 2 года назад

My pleasure, Indra!

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

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

@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

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

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

@royyajayanthkumar6723 2 года назад

I ❤️ simply

@MyOnlineTrainingHub 2 года назад

:-) thanks!

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

@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

@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

@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

@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

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