Thank you much Mynda! Been having issue with my dashboard's headline, pivot data moves when I click on a slicer. Funny, all I needed was to enable that generate GetPivotData option. You really save the day(s).
Thank you so much for this video!!! I've spent hours trying to figure out how I can make my GETPIVOTDATA dynamic now that I am using powerpivot and this is the only video that have clearly explained a solution!! Thank you so much!!!!
Merry Christmas Mynda, Phil & team. Thanks so much for the year's tips and all the work you do. I'm looking forward to more in the New Year and wish you all the best for the years ahead. Stay safe xx
I have been following your videos - Very helpful for me. I am a retired VP and manage my own stock portfolio. I have several sheets in my work book that use a column of stock symbols in separate tables. When I add or subtract a stock in say the master table I would like all of the tables to update with the new stock entry. I have tried many methods with no success - very frustrating for me. Note: I am not a pivot table user. Regards and thanks.
Glad you find my videos helpful 😊 your source data would be better in a single table. You can use Power Query to automate the gathering of the data into a single table: www.myonlinetraininghub.com/power-query-consolidate-excel-sheets
Yea it would be good to show why/when it's better to use this vs the cube formula. I assume they each have different uses, I tend to just use cube formulas all the time
Hi this is a fab video, I finally understand the breakdown of this function. I'm wondering how I can get the data from the pivot table for year based on todays date?, where my row is year. any help appreciated
Can't say without seeing your file, but the easiest thing to do is type = in an empty cell and then click on the value in the PivotTable you want. This will show you the formula syntax and from there you can reconstruct it with cell references.
Great tips! Thank you for your clear explanations and the Canadian data shout-out!! Just wanted to point out that Saskatchewan is misspelled. Right now it says "Saskachewan" (you are missing the "T").
Mynda, thank you for this amazing video! This was so helpful in understanding how to use getpivotdata for a data model table. I see that you showed us how to change the formula so that it was reading the months from a referenced cell, do you have any advice on doing that for a reference cell that contains a date? I keep getting #REF! when I try to replace a date with a cell that contains a date. I am following the syntax you shared. The source table has the date formatted as a date so I don't understand why the data model table does not have the value formatted that way.
When working with dates in PivotTables I always use my mouse to have Excel write the GETPIVOTDATA formula, then I can see the date structure it's expecting and make sure the cells I want to reference are in this same format.
Do you have a video on how to pull values from a PowerPivot table into another sheet in the same workbook? I have my powerpivot table set up but have another sheet that I would like to pull values from one of the columns into another sheet. I’ve tried everything.
The only way is to load the data via a Power Pivot PivotTable. If you put all the columns from the Table in the PivotTable, it will show you all the data. Change the layout to 'Tabular' and remove subtotals and grand totals, and you have yourself a table.
Thanks for the video. Is there a way to create a normal pivot table from the PowerQuery data, and then use the getpivotdata formula? I ask because the formula is much cleaner to make dynamic in various ways and also user friendly to read/understand that way, compared to the beastly PowerQuery getpivotdata formula.
It depends on whether you're summarising the data by day, month, quarter, year etc. However, if you type = and then click on the value field you want, you'll see the syntax required for the date that value field relates to and from there you can modify the formula to refer to dynamic cells.
Hello, can I reduce the informatiob that shown after drilling the pivot table, for ex, I have a table with name, age, gender, and I insert a pivot table and I want to show only name, and age after drill down the result in the pivot. Thank you.
Maybe. It's difficult to visualise what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi, could you possible do a dividend sheet, where you could monitor dividends automatically from a dashboard? I´ve been trying to create a dashboard like that to better monitor my stocks, how much I will get, when I will get it and so on, but I don´t know how to implement the data automatically.
Thanks, Minda! Very Nice! Can you please help me with this issue? Somebody created a very complicated Pivot Table and I am trying to understand that Pivot Table. I added a custom field as Subtotal in the PT( total of 4 columns-ColumnName4) + ( total of 3 columns-ColumnName3). I used PowerPivot and Data Model and now Custom Field has become a separate Excel table and I want it to become a part of the existing Pivot table. (None of the fields match with the Data Source because there are a lot of aggregate functions used and renamed columns) Dealing with this Data Source is a nightmare. In your example, this was not a simple Pivot Table. Please help me. I just want to show the Sum of 2 Columns from PT and want that custom field to become a part of this PT. Thanks
That sounds very odd. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, should this work if i have a pivottable directly sourcing data from a Power Bi dataset? I cant seem to get it to work, despite when hovering over a month i am trying to reference to from a cell the yellow formula preview renders correctly but the formula still results in a #REF! error.
Not sure what you mean by 'the yellow formula preview'. You should be able to type = and then click on the value field in the PivotTable to get the GETPIVOTDATA formula to populate. From there you can edit the formula. If you're still stuck, you're welcome post your question and sample Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi I have a requirement where in i have to enter getpivot formula multiple time for set of combination. E.g.using the pivot shown in video In cell i want to have data for 3 items from row and 3 items from columns but in one cell. Is there any easy way. Since i have to enter i have to manual copy paste the formula in cell and then change the range to get correct data in particular cell. Its very tds.
Thank you for the Video, may I know how can I reference a "range" into the argument? I would like to run a Max array function and return the value in this case
Hi Mynda, I have a technical question :) and I hope you'll help me clear it out: could you please tell me what kind of audio setup you're using for recording your videos? Microphone, recorder, any other important ingredient for obtaining such a wonderful clarity of your recorded sound? Something specific to pay attention to, when going out shopping for this kind of equipment? Any advice on that? :) Thank you very much in advance!
You can reference any value area cell. If you want to query the data model directly, then check out CUBE functions: th-cam.com/video/Wvajqz7uBbs/w-d-xo.html
Can you please make a video describing - how to make date dynamic -in power pivot - pivot table. Date part of formula says [GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&[2024-04-02T00:00:00]" If you can make a video for us, it will be very helpful. Regards
You can modify the formula like so where the date is in cell C4: [GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&["&TEXT(C4,"yyyy-mm-dd")&"T00:00:00]")
Can you make a video that shows how to deal with balance sheet vs income statement variables in your data set? For example, let's say I have monthly data for both my IS and BS but I want to show total 2021 in a pivot table. I want the pivot table to add up all the IS variables, but I only want the BS variable to show the YE 21 ending value, not the sum of every balance from every month of the year. Is there a way to do that?
It's tricky with PivotTables to segregate data like that. You might be best to use CUBE functions or GETPIVOTDATA to build your reports so you can pick and choose the figures you want in your reports.
Thank you much Mynda! Been having issue with my dashboard's headline, pivot data moves when I click on a slicer. Funny, all I needed was to enable that generate GetPivotData option. You really save the day(s).
Glad it was helpful!
Thanks! Was having so much trouble to find the right way to have this dynamic values for power query pivot tables! You helped a ton!
Great to hear!
Mynda, you can simplify complex things so easily! I had trouble understanding GETPIVOTDATA, but this tutorial made it clear. Thank you! 😊👍
So pleased to hear that, Vijay 😊
Thank you so much for this video!!! I've spent hours trying to figure out how I can make my GETPIVOTDATA dynamic now that I am using powerpivot and this is the only video that have clearly explained a solution!! Thank you so much!!!!
So pleased we could help 😊
I love these tips... Things that seem almost obvious once they are explained so well, and you understand why... Thanks so much!
Great to hear 😊
I was stuck with this problem fur last couple of days. Now I know how to come around. Thanks a lot
Happy to help!
Merry Christmas Mynda, Phil & team. Thanks so much for the year's tips and all the work you do. I'm looking forward to more in the New Year and wish you all the best for the years ahead. Stay safe xx
Thanks so much! And to you 😊
It is so nice i was doing manually in our monthly report you save my lot's of time. Thanks
Glad to hear that, Armaan!
THANK YOU FOR THIS!, I was just about to give up until I came across this video!
Glad I could help!
Great. I was looking for an easy way to get the values for the most recent date on some data... This is perfect !
Glad you can make use of it 😊
Works great for stabilizing formating in charts!
Glad it's helpful!
I have been following your videos - Very helpful for me. I am a retired VP and manage my own stock portfolio. I have several sheets in my work book that use a column of stock symbols in separate tables. When I add or subtract a stock in say the master table I would like all of the tables to update with the new stock entry. I have tried many methods with no success - very frustrating for me. Note: I am not a pivot table user. Regards and thanks.
Glad you find my videos helpful 😊 your source data would be better in a single table. You can use Power Query to automate the gathering of the data into a single table: www.myonlinetraininghub.com/power-query-consolidate-excel-sheets
Happy Holidays, Mynda! Thank you for all you do :)
Thanks so much, Al! Happy holidays to you too 😊
I have been using the formula in the way you described. I just wish it was as easy as a regular pivot table.
It’s not too bad once you’re used to it 😊
Useful. I had actually switched this feature off as it now reads B3, B4 etc on = Cell ...as I didn't really appreciate this concept!
Hope you’ll be using it going forward 😊
Hi Mynda. What a super tip! So easy, once you demonstrate how it is done. Thanks for sharing :)) Thumbs up!!
Cheers, Wayne!
Nailed it! Thank you very much!
Glad it helped!
you save my life
Glad we can help 😊
This is truly amazing! I really love the getpivot function.
Great to hear!
Thank you. This was so much needed.
Glad it was helpful!
Wouldn’t Cube formulas be better if it’s in the data model?
I think so. I usually use cube function instead of getpivotdata function.
Yea it would be good to show why/when it's better to use this vs the cube formula. I assume they each have different uses, I tend to just use cube formulas all the time
Cube formulas definitely have advantages over GETPIVOTDATA, but they’re also a bit harder to learn.
Hi this is a fab video, I finally understand the breakdown of this function. I'm wondering how I can get the data from the pivot table for year based on todays date?, where my row is year. any help appreciated
Can't say without seeing your file, but the easiest thing to do is type = in an empty cell and then click on the value in the PivotTable you want. This will show you the formula syntax and from there you can reconstruct it with cell references.
Great as and valuable tips 👍
Glad you liked it 😊
Great video and very helpful. I tend to avoid pivot tables but you videos are making me rethink that stance. Shout out for Canadian data. :-)
Thanks, Robert! Definitely embrace PivotTables 😊
Hi Mynda!Great Tutorial,Really Cool Tip To Make The Formula Dynamic...Thank You :)
Cheers, Darryl!
Love your videos! Saves so much time! 🙂
Thanks a ton!!
Great to hear 😊
Great tips! Thank you for your clear explanations and the Canadian data shout-out!!
Just wanted to point out that Saskatchewan is misspelled. Right now it says "Saskachewan" (you are missing the "T").
Oops! Thanks for the correction. I can't remember where I got that data from.
Helpful video. Thanks :)
Great to hear!
Mynda, thank you for this amazing video! This was so helpful in understanding how to use getpivotdata for a data model table.
I see that you showed us how to change the formula so that it was reading the months from a referenced cell, do you have any advice on doing that for a reference cell that contains a date?
I keep getting #REF! when I try to replace a date with a cell that contains a date. I am following the syntax you shared. The source table has the date formatted as a date so I don't understand why the data model table does not have the value formatted that way.
When working with dates in PivotTables I always use my mouse to have Excel write the GETPIVOTDATA formula, then I can see the date structure it's expecting and make sure the cells I want to reference are in this same format.
Do you have a video on how to pull values from a PowerPivot table into another sheet in the same workbook? I have my powerpivot table set up but have another sheet that I would like to pull values from one of the columns into another sheet. I’ve tried everything.
The only way is to load the data via a Power Pivot PivotTable. If you put all the columns from the Table in the PivotTable, it will show you all the data. Change the layout to 'Tabular' and remove subtotals and grand totals, and you have yourself a table.
Thank you very much
My pleasure 😊
Excellent explanation!!! Thanks a lot !
Glad you liked it :-)
Thanks for the video. Is there a way to create a normal pivot table from the PowerQuery data, and then use the getpivotdata formula? I ask because the formula is much cleaner to make dynamic in various ways and also user friendly to read/understand that way, compared to the beastly PowerQuery getpivotdata formula.
Yes, you can use GETPIVOTDATA with regular PivotTables as explained here: th-cam.com/video/LDf_ORnSCu4/w-d-xo.html
Many thanks, Mynda.
My pleasure, Ian!
This was so useful. Thank you
Great to hear, Delroy!
Thank you. I have a question. How can I use this to get value under the date format?
It depends on whether you're summarising the data by day, month, quarter, year etc. However, if you type = and then click on the value field you want, you'll see the syntax required for the date that value field relates to and from there you can modify the formula to refer to dynamic cells.
Amazing. Thank for these tips
Glad you like them!
Very helpful video. Thanks
Glad you liked it 😊
Great video thanks Mynda!
Cheers, Chris!
i love that
really helped me :D
Great to hear!
Hello, can I reduce the informatiob that shown after drilling the pivot table, for ex,
I have a table with name, age, gender, and I insert a pivot table and I want to show only name, and age after drill down the result in the pivot.
Thank you.
Maybe. It's difficult to visualise what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi, could you possible do a dividend sheet, where you could monitor dividends automatically from a dashboard? I´ve been trying to create a dashboard like that to better monitor my stocks, how much I will get, when I will get it and so on, but I don´t know how to implement the data automatically.
I don't know of a single reliable source of dividend data that will allow you to automate the gathering of this information.
Thanks, Minda! Very Nice! Can you please help me with this issue? Somebody created a very complicated Pivot Table and I am trying to understand that Pivot Table. I added a custom field as Subtotal in the PT( total of 4 columns-ColumnName4) + ( total of 3 columns-ColumnName3). I used PowerPivot and Data Model and now Custom Field has become a separate Excel table and I want it to become a part of the existing Pivot table. (None of the fields match with the Data Source because there are a lot of aggregate functions used and renamed columns) Dealing with this Data Source is a nightmare. In your example, this was not a simple Pivot Table. Please help me. I just want to show the Sum of 2 Columns from PT and want that custom field to become a part of this PT. Thanks
That sounds very odd. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Love it! Thanks for sharing!
You are so welcome!
Thanks...
Our pleasure, Abir!
Hi Mynda, should this work if i have a pivottable directly sourcing data from a Power Bi dataset? I cant seem to get it to work, despite when hovering over a month i am trying to reference to from a cell the yellow formula preview renders correctly but the formula still results in a #REF! error.
Not sure what you mean by 'the yellow formula preview'. You should be able to type = and then click on the value field in the PivotTable to get the GETPIVOTDATA formula to populate. From there you can edit the formula. If you're still stuck, you're welcome post your question and sample Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi
I have a requirement where in i have to enter getpivot formula multiple time for set of combination.
E.g.using the pivot shown in video
In cell i want to have data for 3 items from row and 3 items from columns but in one cell.
Is there any easy way. Since i have to enter i have to manual copy paste the formula in cell and then change the range to get correct data in particular cell. Its very tds.
Not using GETPIVOTDATA, no.
Is there a way to exclude Nov and December if you choose Newfoundland?
Sure, you could wrap the formula in an IF that checks if the result = 0 and then return blank if it does.
Great! Can you make some videos about cubo functions? Thanks
I’ll keep it in mind 👍
Helpful, Great work.
Thanks so much 😊
Thank you for the Video, may I know how can I reference a "range" into the argument? I would like to run a Max array function and return the value in this case
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great..! I like this tutorial..
Great to hear 😊
Hi Mynda, I have a technical question :) and I hope you'll help me clear it out: could you please tell me what kind of audio setup you're using for recording your videos? Microphone, recorder, any other important ingredient for obtaining such a wonderful clarity of your recorded sound? Something specific to pay attention to, when going out shopping for this kind of equipment? Any advice on that? :) Thank you very much in advance!
Hi Mihaela, I'm using a Samson G-Track microphone and I use Camtasia Studio to record my videos. Hope that helps :-)
@@MyOnlineTrainingHub Yes, thank you very much!
Can you reference the field though? YOu are only making the item dynamic
You can reference any value area cell. If you want to query the data model directly, then check out CUBE functions: th-cam.com/video/Wvajqz7uBbs/w-d-xo.html
Can you please make a video describing - how to make date dynamic -in power pivot - pivot table. Date part of formula says
[GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&[2024-04-02T00:00:00]" If you can make a video for us, it will be very helpful. Regards
You can modify the formula like so where the date is in cell C4:
[GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&["&TEXT(C4,"yyyy-mm-dd")&"T00:00:00]")
Helpful 💯
Glad you liked it 😊
Can you make this dynamic for a normal pivot table?
Yes. See my GETPIVOTDATA for regular PivotTables tutorial: www.myonlinetraininghub.com/excel-getpivotdata-function
Can you make a video that shows how to deal with balance sheet vs income statement variables in your data set? For example, let's say I have monthly data for both my IS and BS but I want to show total 2021 in a pivot table. I want the pivot table to add up all the IS variables, but I only want the BS variable to show the YE 21 ending value, not the sum of every balance from every month of the year. Is there a way to do that?
It's tricky with PivotTables to segregate data like that. You might be best to use CUBE functions or GETPIVOTDATA to build your reports so you can pick and choose the figures you want in your reports.
@@MyOnlineTrainingHub Thanks, I've been wondering if I'm missing something obvious.
You can change the BS calculation to only display the current period, thus only returning the sum of one value.
you're a lifesaver! thank you!
Glad it helped!
Love you and your videos Mynda 🥹. Thank you so much!!!
Glad you like them 😊