Dynamic GETPIVOTDATA Formulas for Power Pivot - the PROPER way!

[@vijayarjunwadkar]

Mynda, you can simplify complex things so easily! I had trouble understanding GETPIVOTDATA, but this tutorial made it clear. Thank you! 😊👍

[@MyOnlineTrainingHub]

So pleased to hear that, Vijay 😊

[@rhonskibeat1]

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

[@MyOnlineTrainingHub]

So pleased we could help 😊

[@ultimoobrero8992]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@jetg2111]

I love these tips... Things that seem almost obvious once they are explained so well, and you understand why... Thanks so much!

[@MyOnlineTrainingHub]

Great to hear 😊

[@florianmeyer607]

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!

[@MyOnlineTrainingHub]

Great to hear!

[@armaankhan5148]

It is so nice i was doing manually in our monthly report you save my lot's of time. Thanks

[@MyOnlineTrainingHub]

Glad to hear that, Armaan!

[@iziaurrahman]

I was stuck with this problem fur last couple of days. Now I know how to come around. Thanks a lot

[@MyOnlineTrainingHub]

Happy to help!

[@starstick9485]

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.

[@MyOnlineTrainingHub]

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

[@pxxgxx1949]

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

[@MyOnlineTrainingHub]

Thanks so much! And to you 😊

[@NicolasBORDE]

Great. I was looking for an easy way to get the values for the most recent date on some data... This is perfect !

[@MyOnlineTrainingHub]

Glad you can make use of it 😊

[@alparkson5494]

Happy Holidays, Mynda! Thank you for all you do :)

[@MyOnlineTrainingHub]

Thanks so much, Al! Happy holidays to you too 😊

[@mook528]

THANK YOU FOR THIS!, I was just about to give up until I came across this video!

[@MyOnlineTrainingHub]

Glad I could help!

[@wayneedmondson1065]

Hi Mynda. What a super tip! So easy, once you demonstrate how it is done. Thanks for sharing :)) Thumbs up!!

[@MyOnlineTrainingHub]

Cheers, Wayne!

[@Muuip]

Works great for stabilizing formating in charts!

[@MyOnlineTrainingHub]

Glad it's helpful!

[@JonathanExcels]

I have been using the formula in the way you described. I just wish it was as easy as a regular pivot table.

[@MyOnlineTrainingHub]

It’s not too bad once you’re used to it 😊

[@ksumar]

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!

[@MyOnlineTrainingHub]

Hope you’ll be using it going forward 😊

[@raihansafa5514]

Thank you. This was so much needed.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@darrylmorgan]

Hi Mynda!Great Tutorial,Really Cool Tip To Make The Formula Dynamic...Thank You :)

[@MyOnlineTrainingHub]

Cheers, Darryl!

[@delroydemontagnac5242]

This was so useful. Thank you

[@MyOnlineTrainingHub]

Great to hear, Delroy!

[@iankr]

Many thanks, Mynda.

[@MyOnlineTrainingHub]

My pleasure, Ian!

[@zubairso]

This is truly amazing! I really love the getpivot function.

[@MyOnlineTrainingHub]

Great to hear!

[@phongnguyenthanh9076]

Amazing. Thank for these tips

[@MyOnlineTrainingHub]

Glad you like them!

[@lukasth_]

Love your videos! Saves so much time! 🙂 Thanks a ton!!

[@MyOnlineTrainingHub]

Great to hear 😊

[@Gaijindesune]

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").

[@MyOnlineTrainingHub]

Oops! Thanks for the correction. I can't remember where I got that data from.

[@rob4canada]

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. :-)

[@MyOnlineTrainingHub]

Thanks, Robert! Definitely embrace PivotTables 😊

[@AllenMarkPongasi]

Nailed it! Thank you very much!

[@MyOnlineTrainingHub]

Glad it helped!

[@martinargimon730]

Excellent explanation!!! Thanks a lot !

[@MyOnlineTrainingHub]

Glad you liked it :-)

[@raylee3399]

you save my life

[@MyOnlineTrainingHub]

Glad we can help 😊

[@mdexcelpro8495]

Great as and valuable tips 👍

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@misstoffeepenny]

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

[@MyOnlineTrainingHub]

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.

[@davidburch8079]

Helpful video. Thanks :)

[@MyOnlineTrainingHub]

Great to hear!

[@chrism9037]

Great video thanks Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@madelinemoisio7418]

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.

[@MyOnlineTrainingHub]

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.

[@IlaPatel811]

Love it! Thanks for sharing!

[@MyOnlineTrainingHub]

You are so welcome!

[@rglugo18]

Very helpful video. Thanks

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@almighty.rex.1]

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.

[@MyOnlineTrainingHub]

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.

[@Gary-ln3td]

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.

[@MyOnlineTrainingHub]

Yes, you can use GETPIVOTDATA with regular PivotTables as explained here: kzbin.info/www/bejne/gnXJkIKIo7h2q5Y

[@ajitsinghjasdhol7392]

Thank you very much

[@MyOnlineTrainingHub]

My pleasure 😊

[@imdtaurus]

amazing thank you so much

[@MyOnlineTrainingHub]

You're very welcome!

[@ze0000]

Great! Can you make some videos about cubo functions? Thanks

[@MyOnlineTrainingHub]

I’ll keep it in mind 👍

[@mehmetakarca6400]

Thank you. I have a question. How can I use this to get value under the date format?

[@MyOnlineTrainingHub]

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.

[@johannessheriff5066]

Helpful, Great work.

[@MyOnlineTrainingHub]

Thanks so much 😊

[@shoppersdream]

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

[@MyOnlineTrainingHub]

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

[@edsta714]

Wouldn’t Cube formulas be better if it’s in the data model?

[@jeremylau93]

I think so. I usually use cube function instead of getpivotdata function.

[@chiquita683]

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

[@MyOnlineTrainingHub]

Cube formulas definitely have advantages over GETPIVOTDATA, but they’re also a bit harder to learn.

[@shakthiatukorala595]

Great..! I like this tutorial..

[@MyOnlineTrainingHub]

Great to hear 😊

[@richardsimmonds6592]

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.

[@MyOnlineTrainingHub]

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

[@abdomicron2367]

i love that really helped me :D

[@MyOnlineTrainingHub]

Great to hear!

[@ahchan2]

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

[@MyOnlineTrainingHub]

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

[@AbirBaidya]

Thanks...

[@MyOnlineTrainingHub]

Our pleasure, Abir!

[@kayalha]

Helpful 💯

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@mihaelastancu4924]

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!

[@MyOnlineTrainingHub]

Hi Mihaela, I'm using a Samson G-Track microphone and I use Camtasia Studio to record my videos. Hope that helps :-)

[@mihaelastancu4924]

@@MyOnlineTrainingHub Yes, thank you very much!

[@valli6174]

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.

[@MyOnlineTrainingHub]

I don't know of a single reliable source of dividend data that will allow you to automate the gathering of this information.

[@mrunalmokal1296]

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.

[@MyOnlineTrainingHub]

Not using GETPIVOTDATA, no.

[@dat1dud336]

Is there a way to exclude Nov and December if you choose Newfoundland?

[@MyOnlineTrainingHub]

Sure, you could wrap the formula in an IF that checks if the result = 0 and then return blank if it does.

[@anasabdullah1799]

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.

[@MyOnlineTrainingHub]

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

[@republikadugave420]

Can you make this dynamic for a normal pivot table?

[@MyOnlineTrainingHub]

Yes. See my GETPIVOTDATA for regular PivotTables tutorial: www.myonlinetraininghub.com/excel-getpivotdata-function

[@k-mark9187]

Can you reference the field though? YOu are only making the item dynamic

[@MyOnlineTrainingHub]

You can reference any value area cell. If you want to query the data model directly, then check out CUBE functions: kzbin.info/www/bejne/jafEm6SwbNp1mNU

[@anildkumble4988]

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

[@MyOnlineTrainingHub]

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]")

[@PaulRichards1]

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?

[@MyOnlineTrainingHub]

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.

[@PaulRichards1]

@@MyOnlineTrainingHub Thanks, I've been wondering if I'm missing something obvious.

[@MyOnlineTrainingHub]

You can change the BS calculation to only display the current period, thus only returning the sum of one value.

[@peterh3213]

you're a lifesaver! thank you!

[@MyOnlineTrainingHub]

Glad it helped!

[@dunghuynhchi8229]

Love you and your videos Mynda 🥹. Thank you so much!!!

[@MyOnlineTrainingHub]

Glad you like them 😊