Excellent videos. Considered creating a UA-cam channel on Excel until I found your channel. It’s the best. You teach intuitively, efficiently and still somehow make it entertaining. Great work!
Thank you so much, Mynda... Another really excellent video! I ALWAYS learn such a great deal by watching your impeccable trainings. Please keep'em coming!
Besides learning a very useful chart technique, I learned how to make the Getpivotdata function dynamic by using a relative reference to a cell rather than hard-coded text! That's very useful! I will use that right away.
It was so frustrating to spend all day trying to use VBA to make my charts dynamic with no success. It is midnight and I just came across your video. Thanks for saving me!
@@MyOnlineTrainingHub I'm all good thanks. I actually watch on a home TV screen from couch. The challenge is that I'm 3 meters away from the screen. So your 1080p video really helped to make things a little sharper. PS. Today's video was a little advanced for me, I think I have to go back a few steps. However I still managed to pick up a few tips. Thank you again.
@@MyOnlineTrainingHub. That's an interesting issue. I work in online media and our mantra is make content high definition. I tried watching your 720p videos via my phone and I struggled to read the functions. Either way, I look forward to learning more, so thanks for everything
the way of creating the treemap and change its cell references to the area where there pivot table is located - very smart !! I was already upset that there is no default treemap diagram as a pivot chart available
Thank you so much, your vedios are very helpful so i've been thinking of investing more time in learing excel since its essential to data analytics and before going any further i wanted to know if it will be worth it in the long run. I would truly appreciate it if you could give me your opinion on this.
Great to hear, Mohamed! Given that the world is only generating more and more data, it is now apparent that data analysis skills are not only going to be a benefit, they're going to be essential for almost every role. The earlier you get on this learning curve the better the job opportunities will be for you. There are plenty of courses out there, but of course I'd be honored to have you join one of mine: www.myonlinetraininghub.com/
Thanks for your video, it's very helpful. Would like to know if you have any solutions on combine two pivots to draw one pivot chart. There's a pivot data by several issue type by month, and need to to get the total orders by month from other pivot, and to calculate the % (total issue type/total orders by month) in one pivot to draw the % as the secondary axis in the pivot chart. Thanks in advance!
You can use the techniques shown in the video to create a single chart from multiple PivotTables. If you get stuck you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
For me, the way to do this is to load the source data into the data model and then construct the table using a combination of cubemember and cuberankedmember formulas however its a bit more intricate to get the forumlas right and takes some more time, but its a bit more robust to changes in the data and formatting
@@MyOnlineTrainingHub No, this is my requirement. when i move my mouse over a cell, can it display a loopup value in screentip. i tried many ways but failed, can you please help me. thanks!
Very good information. I’m wondering.. When you extend the ranges in the first examples to allow for growth is there a way to do that using offset or will that negatively impact the graph? I would be nervous just choosing a size when the data might extend past what I choose.
Oh yes I see know you mentioned that. And also, I does the first method work if the two charts are on different sheets..I assume so just checking. Thank you!!
Many thanks, Mynda. Is it a general rule (not just in this example) that the chart source data addresses *must* include the sheet name, even if it is on the same sheet? I hadn't realised that!
Yes, chart references require the sheet name even if the data is on the same sheet. Probably because they're an object and therefore not attached to any cells.
I have a dashboard with pivot charts but trendlines and data labels disappear when filtering and colors don't always cooperate. I believe I have heard you mention before that this is a common issue. Should I look into using the technique in this video for more reliable charts?
The main reason features get lost in Pivot Charts is because settings are applied while the chart is in a filtered state. Remove all filters, then apply your formatting and elements. It doesn't always work, because if an item disappears from the PivotTable upon filtering, then the formatting you've set for that item also gets lost. In which case you can try a regular chart from a PivotTable instead.
@@MyOnlineTrainingHub Thank you! Converting to a regular table/chart by pulling data from the pivot table did the trick. The VP of quality loves my dashboard. Learned it all from you.
Thanks Mynda, but in my case, there is a chart with combo type linked with Pivot & that pivot is getting updated based on filter at Raw & Column both level - i.e. increased & deceased the raw & column numbers... So, in this case, I need to fix one of the data at LINE chart & rest on Cluster column... Can you pl help on that... please...
Very helpfull! Thanks. But I'm also wondering if you have a solution for the Pivot "combo charts" that switch back to "Column" type. Because when I have added a slicer on this chart it changes it's chart type, which is really annoying. Hope you can help me with that :) Thanks in advance!
Thanks, Sandro! The Pivot combo chart issue you describe happens when you select an item in the slicer that doesn't have data for the line chart. You can try using the 'show items with no data' option to make sure the items don't disappear, but this probably isn't what you want if you're using a Slicer, but take look anyhow: ua-cam.com/video/qODYbzgZwus/v-deo.html
@@MyOnlineTrainingHub Thank you very much, the last part is what I need. I need to make it dynamic though, since I want to show it by week. But the last part about dates helpes. I'm going to try it out! Thanks! :)
Hi, I need to learn how to do the things explained below. I would like to add an editable pivot table column which directly alters the source data whenever edited and vice versa. Can you please help me learn how to do it ?
hi, when i added slicer to the pivot table (which is charted normally [not pivotchart] with dyanmic named ranges - there exists error prompts e.g. "Excel found a problem with one or more formula references" but when i add IFERROR to the offset formula then the error prompt goes away. however, adding IFERROR causes the Data Table (should one choose to add legend + Data Table) to not show the correct number format. is there any work around for this?
Hi Mynda, I have created three different workbooks with pivot tables and its source data.. when I try to merge it together using move or copy a link is getting created to the old sheet. Any suggestion how I can move the report and the source data which automatically updates the source data and no links are created.. Thanks in advance..
@@MyOnlineTrainingHub Thank you Mynda for the reply, however change data source wont work when we have tables. When you have a data table and a pivot chat in a sheet then this happens. I tried mapping the data using offset function instead of data table name still the issue happens..
Perhaps I misunderstood your question. Please post it with step by step details of what you're doing on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum Note: If you post a reply here I won't see it as the replies are buried by new comments.
, make a video on conditional formatting.. I want to highlight a column if it is greter than the data present in other two column... Example. If there r 3 column I want to highlight third column if it is greater than the data present in column 1 and column 2
I haven't got a video on that, but there is a written tutorial here: www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas If you get stuck, please post your question on our forum: www.myonlinetraininghub.com/excel-forum
The spill error occurs when there are cells containing data in the range the formula wants to spill the results to. Please refer to this tutorial that explains the spill error and other nuances of dynamic arrays: www.myonlinetraininghub.com/excel-dynamic-arrays
This viedo is really powerful! Thank you Mynda! Its such a shame that the method 2 (GETPIVOTDATA) seems not applicable to PowerPivot and only works with PivotTable.
Hi Mynda, this is absolutely incredible! Thank you for sharing, you're my new go-to Excel expert.
So pleased it was helpful!
This tutorial just saved me 😩😩😩🤣 I know these are strong words but I LOVE YOU 😩
You are most welcome 😊
Excellent videos. Considered creating a UA-cam channel on Excel until I found your channel. It’s the best. You teach intuitively, efficiently and still somehow make it entertaining. Great work!
Wow, thanks so much!
Super tutorial. I love your method number 3, easy-peasy, but unknown for me until today. Thank you very much Mynda!
You are most welcome, Ivan!
I am jealous of your expertise. Thank you for great lessons
My pleasure 😊
Thank you so much, Mynda... Another really excellent video! I ALWAYS learn such a great deal by watching your impeccable trainings. Please keep'em coming!
Thanks! Will do :-)
Besides learning a very useful chart technique, I learned how to make the Getpivotdata function dynamic by using a relative reference to a cell rather than hard-coded text! That's very useful! I will use that right away.
So glad it was doubly useful to you :-)
It was so frustrating to spend all day trying to use VBA to make my charts dynamic with no success. It is midnight and I just came across your video. Thanks for saving me!
Glad I could help, Lucas!
Great video. It basically showed me that none of the methods are really viable for my dataset that is huge, but it saved me time from attempting it =D
Glad it was helpful, James!
Hi Mynda. Great tricks for using standard charts with Pivot Table data. Thanks for sharing :)) Thumbs up!!
My pleasure, Wayne!
I was trying this the other day - and here's your video. Thank you!
Hi there, i'm loving this 1080p video.
Your effort has been noticed and valued. Thank you
Hope all the zooming and panning doesn’t make you nauseous 😉
@@MyOnlineTrainingHub I'm all good thanks.
I actually watch on a home TV screen from couch. The challenge is that I'm 3 meters away from the screen. So your 1080p video really helped to make things a little sharper.
PS. Today's video was a little advanced for me, I think I have to go back a few steps. However I still managed to pick up a few tips. Thank you again.
The challenge I have is there are people who watch on their phones...for them 1080p is too small to see. It's a difficult balance.
@@MyOnlineTrainingHub. That's an interesting issue. I work in online media and our mantra is make content high definition. I tried watching your 720p videos via my phone and I struggled to read the functions. Either way, I look forward to learning more, so thanks for everything
Priceless explanations! Many thanks!
Glad you enjoyed it!
Liked your tutorials a lot... simple and one of the best i have seen so far. Thank you very much
Thanks for your kind words, Krishnaraj!
Quite superb ! Glad I found your channel !
Awesome, thank you!
This is very helpful, thanks a lot for your tremendous help~
You're very welcome!
Beautiful! Today I realized how much I didn't know? Thanks
So pleased you enjoyed my video :-)
Excellent Tips. Thank you. 😊
You are so welcome!
10:54 WOW!!! cool stuff Mynda!!!
Thanks! Hope you can make use of it :-)
Thanks, you Just saved my day!
Glad I could help 😊
Excellent. Relevant for my next task today! Thanks a lot.
Great timing :-)
Very intellgent Mynda👍 and thanks for sharing your know-hows and expecting more😍
Thank you so much!
Sheer brilliance.
4 contrasting approaches 👍
Thanks so much!
thanks my teacher and happy Eid for all of your family
Same to you!
Thank you so much for this Tip. Helps a lot
Great to hear!
Excelente, un tema para aprender más! Congratulations for this video! Thank you!
Gracias, Leonidas!
Just brilliant and smart! Thanks a ton for sharing.
My pleasure, Saba :-)
Hi Mynda..I've Just Learnt 3 Simply Awesome Techniques,Wicked Stuff! Thank You :)
Great to hear, Darryl 😊
Wow this was so helpful! Thanks!!
So glad!
Amazing video. Can you please do a video explaining DAX functions in detail?
Thanks, Zubair! A video explaining DAX functions in detail would be weeks long ;-) I'll keep it in mind though.
@@MyOnlineTrainingHub Or point me to a resource from where I can start learning DAX functions.
@@zubairso you can learn them in my Power Pivot course: www.myonlinetraininghub.com/power-pivot-course
Wow, thanks, that's really great. Thanks from Germany
You're very welcome, Gerald!
the way of creating the treemap and change its cell references to the area where there pivot table is located - very smart !! I was already upset that there is no default treemap diagram as a pivot chart available
Glad you liked it :-)
A very helpful video, thanks for sharing
Glad it was helpful, Clive!
Great video!
Glad you enjoyed it, Chris :-)
Thank you 😊
Glad I can help 😊
Thank you so much, your vedios are very helpful
so i've been thinking of investing more time in learing excel since its essential to data analytics
and before going any further i wanted to know if it will be worth it in the long run.
I would truly appreciate it if you could give me your opinion on this.
Great to hear, Mohamed! Given that the world is only generating more and more data, it is now apparent that data analysis skills are not only going to be a benefit, they're going to be essential for almost every role. The earlier you get on this learning curve the better the job opportunities will be for you. There are plenty of courses out there, but of course I'd be honored to have you join one of mine: www.myonlinetraininghub.com/
Thank you Mynda, you are the best
This is awesome
Glad it was helpful!
Thanks for your video, it's very helpful. Would like to know if you have any solutions on combine two pivots to draw one pivot chart. There's a pivot data by several issue type by month, and need to to get the total orders by month from other pivot, and to calculate the % (total issue type/total orders by month) in one pivot to draw the % as the secondary axis in the pivot chart. Thanks in advance!
You can use the techniques shown in the video to create a single chart from multiple PivotTables. If you get stuck you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great Job.
Cheers, Ayman!
A pain in the neck less now. Thank you, Mynda!
:-) hope so.
Love it!! thanks
Glad you like it, Zayd!
For me, the way to do this is to load the source data into the data model and then construct the table using a combination of cubemember and cuberankedmember formulas however its a bit more intricate to get the forumlas right and takes some more time, but its a bit more robust to changes in the data and formatting
Cube formulas are a great idea, but like you say, require special skills to get them right :-)
Love this, can you do a video on sorting slicers? My slicers always place month, dates and days in the wrong order.
Here is a post on how to sort Slicers: www.myonlinetraininghub.com/sorting-excel-date-slicers
@@MyOnlineTrainingHub thank you. Like the look of that dashboard. How dud you get tabs up top?
The 'tabs' in the video are Slicers: www.myonlinetraininghub.com/ill-have-a-slicer-that
Hi your videos are very helpful for me. Can you please help me with how to display vlookup value of a cell when cursor moved over the cell.?
I'm not sure what you're referring to, Prabhu. Did this occur in the video?
@@MyOnlineTrainingHub No, this is my requirement. when i move my mouse over a cell, can it display a loopup value in screentip. i tried many ways but failed, can you please help me. thanks!
You'd need to use VBA for this as there's no built in functionality, sorry.
Thank you for a very nice video. kindly can u share the excel file u used
The link to where you can download the Excel file is in the video description.
Thank you 🏆🏆🏆🎖🎖🎖
You are most welcome :-)
wowww!!! Niceeee
Thaaaaannnnnkkkks :-)
Wonderful!
Many thanks, Donna!
Very good information. I’m wondering.. When you extend the ranges in the first examples to allow for growth is there a way to do that using offset or will that negatively impact the graph? I would be nervous just choosing a size when the data might extend past what I choose.
Yes, you can use OFFSET or INDEX to generate a dynamic named range for the chart :-)
Oh yes I see know you mentioned that. And also, I does the first method work if the two charts are on different sheets..I assume so just checking. Thank you!!
All methods work if charts are on different sheets :-)
Hi Mynda, thanks a ton for video. However the very last technique didn't work for me, I used this in Bar chart, any idea why?
Hard to say, but happy to look at it for you if you post your question and sample Excel file on our forum: www.myonlinetraininghub.com/excel-forum
Many thanks, Mynda. Is it a general rule (not just in this example) that the chart source data addresses *must* include the sheet name, even if it is on the same sheet? I hadn't realised that!
Yes, chart references require the sheet name even if the data is on the same sheet. Probably because they're an object and therefore not attached to any cells.
Thank you so much Madam
You are most welcome, Rahul :-)
I have a dashboard with pivot charts but trendlines and data labels disappear when filtering and colors don't always cooperate. I believe I have heard you mention before that this is a common issue. Should I look into using the technique in this video for more reliable charts?
The main reason features get lost in Pivot Charts is because settings are applied while the chart is in a filtered state. Remove all filters, then apply your formatting and elements. It doesn't always work, because if an item disappears from the PivotTable upon filtering, then the formatting you've set for that item also gets lost. In which case you can try a regular chart from a PivotTable instead.
@@MyOnlineTrainingHub Thank you! Converting to a regular table/chart by pulling data from the pivot table did the trick. The VP of quality loves my dashboard. Learned it all from you.
Thanks for sharing
My pleasure :-)
Thanks Mynda, but in my case, there is a chart with combo type linked with Pivot & that pivot is getting updated based on filter at Raw & Column both level - i.e. increased & deceased the raw & column numbers...
So, in this case, I need to fix one of the data at LINE chart & rest on Cluster column...
Can you pl help on that... please...
Sure. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Very helpfull! Thanks. But I'm also wondering if you have a solution for the Pivot "combo charts" that switch back to "Column" type. Because when I have added a slicer on this chart it changes it's chart type, which is really annoying. Hope you can help me with that :) Thanks in advance!
Thanks, Sandro! The Pivot combo chart issue you describe happens when you select an item in the slicer that doesn't have data for the line chart. You can try using the 'show items with no data' option to make sure the items don't disappear, but this probably isn't what you want if you're using a Slicer, but take look anyhow: ua-cam.com/video/qODYbzgZwus/v-deo.html
@@MyOnlineTrainingHub Thank you very much, the last part is what I need. I need to make it dynamic though, since I want to show it by week. But the last part about dates helpes. I'm going to try it out! Thanks! :)
Hi,
I need to learn how to do the things explained below.
I would like to add an editable pivot table column which directly alters the source data whenever edited and vice versa.
Can you please help me learn how to do it ?
Can't be done, Arun. PivotTables can't edit the source data, sorry.
Great video!! Only one problem!! My Excel is not that advance!!!
You can do this with any version of Excel. The only thing you're missing will be the treemap chart, but this technique can work with other charts too.
hi, when i added slicer to the pivot table (which is charted normally [not pivotchart] with dyanmic named ranges - there exists error prompts e.g. "Excel found a problem with one or more formula references" but when i add IFERROR to the offset formula then the error prompt goes away. however, adding IFERROR causes the Data Table (should one choose to add legend + Data Table) to not show the correct number format. is there any work around for this?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, I have created three different workbooks with pivot tables and its source data.. when I try to merge it together using move or copy a link is getting created to the old sheet. Any suggestion how I can move the report and the source data which automatically updates the source data and no links are created..
Thanks in advance..
You need to edit the reference to the source data via the PivotTable tab on the ribbon > Change Data Source.
@@MyOnlineTrainingHub Thank you Mynda for the reply, however change data source wont work when we have tables. When you have a data table and a pivot chat in a sheet then this happens. I tried mapping the data using offset function instead of data table name still the issue happens..
Perhaps I misunderstood your question. Please post it with step by step details of what you're doing on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum Note: If you post a reply here I won't see it as the replies are buried by new comments.
, make a video on conditional formatting.. I want to highlight a column if it is greter than the data present in other two column...
Example. If there r 3 column I want to highlight third column if it is greater than the data present in column 1 and column 2
I haven't got a video on that, but there is a written tutorial here: www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas If you get stuck, please post your question on our forum: www.myonlinetraininghub.com/excel-forum
Wow... Tks
Welcome 😊
I get a spill error while using the filter formula
The spill error occurs when there are cells containing data in the range the formula wants to spill the results to. Please refer to this tutorial that explains the spill error and other nuances of dynamic arrays: www.myonlinetraininghub.com/excel-dynamic-arrays
7:50
👍
Glad you liked it, Nithasha!
😀👍
:-) glad you liked it.
Weel Done
Cheers, Shakira :-)
nice #playwithdata
Thanks, Nikhil!
I 'd rather name this video How To HACK EXCEL CHARTS! :)
Dang, yes I should have :-)
You just made me like getpivotdata...😆
Wonderful to hear!
This viedo is really powerful! Thank you Mynda! Its such a shame that the method 2 (GETPIVOTDATA) seems not applicable to PowerPivot and only works with PivotTable.
Glad you liked it! This is how to use GETPIVOTDATA with Power Pivot PivotTables: www.myonlinetraininghub.com/getpivotdata-function-power-pivot
@@MyOnlineTrainingHub Fantastic！Thank you so much! Can't wait to apply this new trick to my work.