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
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!
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.
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?
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.
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?
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.
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
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
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.
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
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.
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.
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
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
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?
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
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.
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
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?
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.
The GETPIVOTDATA syntax for Power Pivot PivotTables is slightly different. See this tutorial: www.myonlinetraininghub.com/getpivotdata-function-power-pivot
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.
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
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 ?
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.
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.
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.
![Excel GetPivotData Function | Udemy Instructor, Leila Gharani [bestseller]](/img/n.gif)
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
:-) cheers, John!
Thank you for your hard effots. It is really help me to learn new areas of excel
Glad to hear that!
Sheer genius. Wish I had watched this ten laborious hours ago.
😁 better late than never!
Yes, Now the purpose and usage of GETPIVOTDATA is more clear and I do love it! Thanks Mynda for this succinct tutorial! 😊👍
So pleased to hear that, Vijay! 😊
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!
You sure can 👍 glad this will be useful!
Why am I not seeing this earlier??? It solves tons of my problems! Thank you so much!!!
Great to hear 😊
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...)
Glad it was helpful!
Very Good Hints thank you Myanda
My pleasure 😊
Thank you Mynda for this informative tutorial, 👌
You are most welcome!
I do really like your videos! Congrats
Awesome, thank you!
Thanks for sharing. I also love getpivotdata and use it every day
Yay, great to hear!
Thanks a lot for the video Mynda, very helpful !!!
My pleasure, Hadi!
I ❤ your lectures!
Thanks so much!
Thanks for share. Greetings from Brazil 🇧🇷
My pleasure 😊
Cube formulas are pretty cool as well
Yes, but they don’t work with regular PivotTables. They’re a lesson for another day 😊
I do know a bit of GETPIVOT and CUBE, but I do not understand the real benefit of the CUBE functions.
Great video as usual!
Glad you enjoyed it!
very well exppalined!
Thanks so much, Steven!
Thank you very much! love your content! you deserve 100b subs!!!
Thanks so much 😊
@@MyOnlineTrainingHub your welcome :D
"May be it's too soon.." cracked me up
Thank You for Knowledge
My pleasure, Visheesh!
Great content as always. This is extremely useful
Glad you think so!
Very useful, thank you.
Great to hear 😊
Great video Mynda!
Cheers, Chris!
why no one talked about it before? Such amazing tricks!
Glad you liked it :-)
Hi, great tutorial. How will this work when the size of your pivot table gets bigger or smaller?
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.
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?
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.
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?
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.
Hi Mynda. Thanks for demystifying GETPIVOTDATA! Very helpful understanding how to make it dynamic. Thanks for sharing :)) Thumbs up!!
Glad it was helpful, Wayne!
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
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
Hello, If original table is sql table using power query, will this video be applicable then ?
Only if you build a PivotTable using the Power Query query output.
Thank you very much!
You're welcome!
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.
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
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.
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.
I LOVE IT!
Great to hear, Jason!
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
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
Thank you very much
My pleasure 😊
interesting! thank you
Nice!
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?
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
Thanks much,
My pleasure, Indra!
Thank you I have one question how to create dashboard excel for high school?
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.
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 ?
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
I ❤️ simply
:-) thanks!
Where can I get that cool shirt?
Thank you for your videos
:-) I think you can get them from Redbubble.com
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?
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.
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
The GETPIVOTDATA syntax for Power Pivot PivotTables is slightly different. See this tutorial: www.myonlinetraininghub.com/getpivotdata-function-power-pivot
I am just curious, do you work at Microsoft ?👍
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 :-)
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.
Yes, it doesn't work on external closed files. Best to avoid that where possible.
Hi this maybe slightly out of topic, but why is getpivotdata with text and amount, the $ and comma disappears in Chart Title. Thanks
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
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 ?
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.
@@MyOnlineTrainingHub i agree about the pivotable, I dont understand why I would use GETPIVOTDATA instead of sumifs ! Or is it equivalent somehow ?
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.
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.
Sounds like you’re referencing a row or column label cell. GETPIVOTDATA only works with value field cells.