Grab the file I used in the video from here 👉 pages.xelplus.com/matrix-chart-file 👋 Exciting news! We've released a new, modern Excel Business Charts Course: link.xelplus.com/yt-bizcharts-course
Ms. Gharani, what do you have 'selected' when you change the chart type to a scatter plot? For me, it seems no matter where I put my mouse cursor, when I change the chart type to a scatter plot, the 'budget' and 'actual' lines convert to a series of dots like a scatter plot
As always, very good! Could I add a complication - link tasks. So that if task 2 cannot start until task 1 is completed. Would it be as simple as copying the actual end date of task 1 to the start date of task 2 cell? In this way the actual end date of task 1 changes with the work days and the start day of task 2 will change accordingly?
This is mind blowing.. I never thought it.. but is it possible if we make the horizontal axis data range to dynamic? (in your video, the horizontal axis data range it is fixed for 12 data)
Excellent video and clear presentation, everything is explained sound and slowly, but I have a problem. Although I have marked the fields that should format my x-axis, the axis is still counting the total number of cells I have marked, and not what I want to (the same pattern over and over again like at this video). Do you have some advice?
Hi Leila, i adapted this approach to work with sorted bar chart (from one of your other videos). Is there a way to assign a colour to each bar? I have four product types each year and I'm showing what percentage of sales each product type represents (instead of using a pie chart). I would like show product 1 as a black bar, product 2 to as a red bar, etc. even though the sort order may be different from year to year.
When I had the dividers piece highlighted for the three y-axis values of zero, I chose Scatter Plot like you did, making sure only the three y-axis values were selected. It changed my entire graph to scatterplot, not just the y-axis values. I looked up a vid by ExcelisFun and found I needed to go to the Combo section of Chart Types and carefully select Scatter for the Divider and click Secondary Axis. I'm using Excel 2016, so maybe it just doesn't work the same?
Yes - In Excel 2016, if you click on any of the series - so any of the lines, it will directly take you to the combo section - and yes, you need to make sure you stay in the combo section, otherwise your entire chart is changed. In 2010, you need to select the specific series before changing...
Perfect 👌 video , Leila ,could you please publish a video, how to incorporate budget variations for mtd and YTD information in this chart please thank you so much
Hi your videos are very helpful for me to prepare my executive reports. After seeing this video i am thinking is there a way to create panel chart in vertical rather than in horizontal as i have biweekly data to be represent for 6 months for 4 items. By this approach it will fill my complete sheet and i don’t want that. Thanks in advance for your support
as always it's really good... while I'm preparing dynamic dashboards I refer your videos which helping me so much. Can you help me with preparing a dashboard with dynamic map?
I am doing a similar panel but it is unable to read the first values in each series for each of the panel category hence the line graph starts from 0 for each category value before continuing with the other values correctly. Any help?
A nuance that I stumble on (or missed in the video) is that the second data set( in this example the Budget) gets graphing priority over the first data set (in this example the Actual.) Where the lines on the graph intersect, the second line overlaps the first. So in doing a year over year comparison, the current year should be the second data set so that it visibly overlaps the prior year.
Hi Jason - sorry I didn't see your comment earlier - yes it is possible - in a similar way you see here. You can use a scatter plot and activate the error bar and use the Y to get a vertical line....
The data label is always for the point plotted. So you'd have to plot a point in the place where you want to see the label for it. Once the point is plotted, you can activate data labels and the use a formula to connect to the label you want to show.... hope that helps.
So in the very next part of the vid you do talk about switching to the secondary axis, but how come when you initially switched to Scatter Plot for the three data points only those three points switched, and not everything else. I don't understand what I am doing differently.
Excel tries to figure out which axis to plot on. Sometimes, it picks the right one and sometimes not. In Excel 2016, you can already specify if you want the scatter plot on the secondary axis when you are in combo view of change-chart-type. You see a check-box right beside the series. I'll probably re-do this video for 2016 since charts changed a lot....
Hey Leila , I tried this but found that the values have to be ordered smallest to largest to get the growing effect. But in reality my data does not follow that trend. There are months when the data will be higher or lower than its previous. So unless i am doing a ytd running total it wont come out like yours. Especially if i use time in the second column. What is a workaround?
Yes - these represent YTD values. You can also show monthly values. Depends very much on what you'd like to compare. If you'd prefer to compare monthly data, you could also use a column chart. This would probably make it easier to compare each month of the different companies/products with one another...
Hello, I need your help on matrix chart. I have a data of open and close cases during last 5 years and of 5 regions. I added the growth. How can I add the growth to this chart. I tried to put it as secondary but no luck. I'm trying to show the open (1ary axis), closed (1ary axis) and growth (2ary axis). Thank you so much
Hi Ka Aa. If you use the secondary axis for growth, then you can can't fix it from 0 to 1 and use it for the lines that split the series. For that, you have to use another option - for example selecting the highest point in the chart and allowing the error bar to be that long...This way, you should be able to use the secondary axis for growth.
Grab the file I used in the video from here 👉 pages.xelplus.com/matrix-chart-file
👋 Exciting news! We've released a new, modern Excel Business Charts Course: link.xelplus.com/yt-bizcharts-course
I don't think any other youtuber shown this trick. It is very useful. Thanks.
Glad you like it!
Thank you very much mam for your clear presentation. it's really very helpful for my project. Thank you from Tamilnadu (India).
I frequently find myself visiting your videos when I'm creating reports for work. Thank you!
Glad to hear that. Let me know if there is anything specific you'd like to see and I'll add to my list.
Excelent video 📹 great explanation, thanks this will be helpful to enhance my presentations
Hi Leila as always you're awesome... Just wanted to know if you thinking about Power BI videos...
Super as usual, how could the same in Power BI please?
Very clear presentation. Thank you from New Zealand
Thanks, thanks a lot, you are the best!!!!!, please more videos about impact charts!!!!!!
Glad you liked it!
Thank you....Very usefull
Ms. Gharani, what do you have 'selected' when you change the chart type to a scatter plot? For me, it seems no matter where I put my mouse cursor, when I change the chart type to a scatter plot, the 'budget' and 'actual' lines convert to a series of dots like a scatter plot
As always, very good!
Could I add a complication - link tasks. So that if task 2 cannot start until task 1 is completed. Would it be as simple as copying the actual end date of task 1 to the start date of task 2 cell? In this way the actual end date of task 1 changes with the work days and the start day of task 2 will change accordingly?
Really applicable for me
This is mind blowing.. I never thought it.. but is it possible if we make the horizontal axis data range to dynamic? (in your video, the horizontal axis data range it is fixed for 12 data)
Excellent video and clear presentation, everything is explained sound and slowly, but I have a problem. Although I have marked the fields that should format my x-axis, the axis is still counting the total number of cells I have marked, and not what I want to (the same pattern over and over again like at this video). Do you have some advice?
I am really enjoying all your videos. Very useful and I am looking to buy your courses as well.
Thanks for your comment and support! Glad to hear you like the videos.
Hi Leila, i adapted this approach to work with sorted bar chart (from one of your other videos). Is there a way to assign a colour to each bar? I have four product types each year and I'm showing what percentage of sales each product type represents (instead of using a pie chart). I would like show product 1 as a black bar, product 2 to as a red bar, etc. even though the sort order may be different from year to year.
Awesome. Thanks for sharing. You are my hero!
My pleasure Ira. Glad you like it.
@@LeilaGharani I have watched most of your videos and i am very impressed with your knowledge and presentation simplicity. Great, great job!
When I had the dividers piece highlighted for the three y-axis values of zero, I chose Scatter Plot like you did, making sure only the three y-axis values were selected. It changed my entire graph to scatterplot, not just the y-axis values. I looked up a vid by ExcelisFun and found I needed to go to the Combo section of Chart Types and carefully select Scatter for the Divider and click Secondary Axis. I'm using Excel 2016, so maybe it just doesn't work the same?
Yes - In Excel 2016, if you click on any of the series - so any of the lines, it will directly take you to the combo section - and yes, you need to make sure you stay in the combo section, otherwise your entire chart is changed. In 2010, you need to select the specific series before changing...
Perfect 👌 video , Leila ,could you please publish a video, how to incorporate budget variations for mtd and YTD information in this chart please thank you so much
Sorry not on this video, I meant to the pie charts video sorry
Hi your videos are very helpful for me to prepare my executive reports. After seeing this video i am thinking is there a way to create panel chart in vertical rather than in horizontal as i have biweekly data to be represent for 6 months for 4 items. By this approach it will fill my complete sheet and i don’t want that. Thanks in advance for your support
Really really good. Another thing I need!
I'm glad you like it Erica.
as always it's really good... while I'm preparing dynamic dashboards I refer your videos which helping me so much. Can you help me with preparing a dashboard with dynamic map?
Glad you like them. Dynamic map is a good one. I'll add that video in later this month.
Leila Gharani thank you so much.... looking forward for dynamic map tutorial....
Great technique and very useful. Thank you
Glad to hear that :)
very well presented!
I am doing a similar panel but it is unable to read the first values in each series for each of the panel category hence the line graph starts from 0 for each category value before continuing with the other values correctly. Any help?
Great technique very intelligently done
Glad you like the technique.
A nuance that I stumble on (or missed in the video) is that the second data set( in this example the Budget) gets graphing priority over the first data set (in this example the Actual.) Where the lines on the graph intersect, the second line overlaps the first. So in doing a year over year comparison, the current year should be the second data set so that it visibly overlaps the prior year.
Its wonderful, greetings from mexico
@7:26 How to fix the axes to have a max and min values, In excel 2016 only i find the auto option?
awesome...Thanks!
Hi Leila, great tutorials! Would it be possible to use Error Bars to create a dynamic range on a football field bar chart?
Hi Jason - sorry I didn't see your comment earlier - yes it is possible - in a similar way you see here. You can use a scatter plot and activate the error bar and use the Y to get a vertical line....
That's great, thanks Leila! Is there a way to add the data label of the Error Bar at the top end of the bar instead of the bottom?
The data label is always for the point plotted. So you'd have to plot a point in the place where you want to see the label for it. Once the point is plotted, you can activate data labels and the use a formula to connect to the label you want to show.... hope that helps.
Oh great! Thanks for the tip Leila!
So in the very next part of the vid you do talk about switching to the secondary axis, but how come when you initially switched to Scatter Plot for the three data points only those three points switched, and not everything else. I don't understand what I am doing differently.
Excel tries to figure out which axis to plot on. Sometimes, it picks the right one and sometimes not. In Excel 2016, you can already specify if you want the scatter plot on the secondary axis when you are in combo view of change-chart-type. You see a check-box right beside the series. I'll probably re-do this video for 2016 since charts changed a lot....
love you video, help me very much!
Glad to hear that Yan Nan.
great utilization of tool.... thank you leila
Great work!!!
Thank you David.
Thanks!!!!
I couldn't set the vertical lines for separate each chart .
Hey Leila , I tried this but found that the values have to be ordered smallest to largest to get the growing effect. But in reality my data does not follow that trend. There are months when the data will be higher or lower than its previous. So unless i am doing a ytd running total it wont come out like yours. Especially if i use time in the second column. What is a workaround?
Yes - these represent YTD values. You can also show monthly values. Depends very much on what you'd like to compare. If you'd prefer to compare monthly data, you could also use a column chart. This would probably make it easier to compare each month of the different companies/products with one another...
Hello, I need your help on matrix chart. I have a data of open and close cases during last 5 years and of 5 regions. I added the growth. How can I add the growth to this chart. I tried to put it as secondary but no luck. I'm trying to show the open (1ary axis), closed (1ary axis) and growth (2ary axis). Thank you so much
Hi Ka Aa. If you use the secondary axis for growth, then you can can't fix it from 0 to 1 and use it for the lines that split the series. For that, you have to use another option - for example selecting the highest point in the chart and allowing the error bar to be that long...This way, you should be able to use the secondary axis for growth.
what is the J, M, S and D?
It should be the shortened version of the months - January, March, September, December
I want to press the Like Button but it I like the Like Count it displays. 😭😭
Difficult
when I select bubble chart it select the line and y values simultaneously, can't understand why ..
you can always go back to "select data" and adjust the series to the ones you need....