How to create a 4-Quadrant Matrix Chart in Excel
HTML-код
- Опубликовано: 8 фев 2018
- Want to learn how to design a salary structure? Check: www.caripros.com/design-salar...
FREE template for my video: Excel for HR - Create Annual Employee Salary Increase Template from Scratch. You can download and try it out yourself here: bit.ly/2MLLdb7
FREE actual workbook for my video "Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner". You can download and try it out yourself here: bit.ly/2UmeX2v
New course Launched! I created it to show you step-by-step how to design a salary structure with regression analysis in Excel. Check out the detail here:
caripros-hr-analytics.teachab...
Topic: How to create a 4-Quadrant Matrix Chart in Excel
Business Scenario: You need to show executive compensation by Salary value and by Compa-ratio in a Matrix chart
Formula: Scatter Chart
*****Follow-up Consulting Services*****
If you have specific question regarding your issue, you can email me at the email here goo.gl/WejijZ Note that there will be a fee of US$200 charged for solving your issue. The turnaround is within 24 hours. Any follow-up issue in 3 days will also be answered with no charge. Payment link: www.paypal.me/caripros
*****More Videos in Playlists*****
Power BI for Beginners: bit.ly/3ivKitD
Power BI for Advanced Users: bit.ly/3lE9zmO
Excel for HR goo.gl/JdeVnd
Excel for HR - Master Class goo.gl/LYfq2f
Excel Macro - Beginner goo.gl/Yae5nc
Excel Macro/VBA - Splitting a Master File goo.gl/m8CHya
Excel Macro/VBA - Auto-hide Rows or Columns bit.ly/2Mzteb5
Excel Charts Data Visualization goo.gl/2ao6BP
Excel Vlookup Function goo.gl/kP2Wpz
Excel Pivot Table Function goo.gl/rukkPs
Excel Array Function goo.gl/i4sQH8
Excel Index and Match Function goo.gl/i7VGU4
Excel Solver/Goal Seek Functions goo.gl/FTkTnj
Excel Cell Formatting Solutions goo.gl/gpa6MY
HR Analytics - Merit Matrix goo.gl/Koy7co
HR Analytics - Salary Structure goo.gl/uZBnFa
Excel Tricks goo.gl/TeqGDw
Excel Troubleshooting goo.gl/bdY5by
Fun HR Topics goo.gl/7zVg8h
For more successful stories, view at: caripros.com/index.php/success...
#ExcelforHR#HRAnalytics#Excel#HR 
Хобби
![mgk - I Think I’m OKAY (with YUNGBLUD) (Sad Version) [Official Music Video]](http://i.ytimg.com/vi/7AC1uqQLIg4/mqdefault.jpg)
I must have searched for this solution dozens of times over the past few years and never been able to get what i wanted without VBA. Very helpful!
Brilliant, just brilliant. Thank you to the author of this video.
This was really helpful, I am currently studying a CIPS diploma, so it's great to put this into my process.
Thanks!
Wonderfully helpful. Thank you for sharing this!
OMG you really safe my life with this tutorial, thank you SO SO SO much you rock!!!!!!
Thank you, this was very helpful
Excellent video, THANK YOU!
Thank you so much. it is brilliant and very clear explanation
Super easy to follow through! Thank you!!!
Very clear steps and excellent pace of presentation. Thank you
Great work! This helped me get my graph done! I'm not stressing anymore! YOU ROCK!
I'm so glad!
Extremely helpful! Thank you!
Perfect, very easy to understand. Thanks for sharing!
My pleasure!
Could you please elaborate on the "y -axis" which splits the data points vertically? Since the jobs are at different grades, how do we find a common mid point? Thanks
I can't find the option at 2:46 to select the second chart sub-type under scatter plots. I'm using Excel for Mac version 16.57. Any ideas?
Thank you!! Very helpful!
This is an excellent tutorial. Thank you very much, it has helped me in my work.
This is an excellent video and really helped me up my game. The only difficulty I encountered is I had to use the Windows version to choose the second type of scatter chart. Does anyone know how to change the chart to show the one set of data using the Mac version?
Wonderful Video ... Crystal clear explanation :-)
Brilliant presentation and guidance. Thank you
very helpful, thank you!
great video - this really helped! thanks!
My pleasure!
Nice one.. Thank you
Thank you a million times! This helped a lot
Glad it's helpful!
Great video on evaluating effort - benefit. I'm going to do this for my company to visualize all the initiatives we want to implement and see how they fall on this type of quadrant. Thanks!
Thank you very very much! I've looked for this video since years! Thank you, very helpful!
My pleasure!
hi
Thank you so much for the video.
Thank you ❤ This was very helpful for My UG Project....... 😍
This is great. Can you please show how you would filter the data? I am having trouble adding the full range of data to the filter in the chart.
Very helpful.Thankyou ❤
Hi! Thank you so much for this! Could you elaborate on the x-axis and y-axis which split the data points vertically? I do not understand how you made the calculations on the mid-point? How do you come to these numbers?
I don't normally give a comment. I'm really impressed and thanks for sharing this vital information with the world. With this information, I was able to draw not only horizontal and vertical lines but also diagonal lines.
Glad it was helpful!
Sos una genia!!!! muchas gracias!!!!!!!
Very useful. Thank you
thank you so much,,,its really help me a lot,,,
Thankyou, It was helpful :)
Excellent Tutorial! Thank you ;-)
Thank you Sheila!
Super helpful video! Tnx!
Glad to help!
Helped me a lot
Thank you so much for making this tutorial! Please keep these excel skills coming.
thank you so much
when trying to create the quadrant lines, I never have the dots show up on the axis. i added the data just as it shows in the video. Please advise
Great video on explaining this. I made an Excel on my own and thereafter made the 4-way quadrant easily. Thanks!
Hey thanks for the video! Is there anyway the circle size can be increased automatically based on their value? For. eg. "VP Legal" has the biggest circle in the matrix.
Hi why my excel doesnt has the options to change the scatter chart to just one set of dots?
Suwun, akhire saget damel grafik e...
By Google translator you said "In the end, it can make graphs ..." I will assume it is a compliment 😃
Wondering if this can be replicated for a Bubble Chart? I've given it a try, but can't connect the axis data series with a line. Please let me know if you can help!
Can you explain more on compare-ration figure?
How to get the data you used in the video. Great video
Can you do this type of chart with 3 data lists?
Excellent work. Nicely explained and the final details in my opinion were critical to encapsulating the graphic.
My only question would be the selection of the X & Y values. What were the factors driving those figures?
Question: Do you know how to make a life style finder in excel?
I want the explanation of matrix and grid in spreadsheet
Hi, thank you for such a wonderful video. I know it was published a while ago but hopefully you still receive these messages and can respond. How do you handle situations where the data points visually line up exactly in the same spot? For example, let's say in the above video that VP, Security and VP, IT lined up with the same Comp ratio and Salary? On the chart they would overlap so it would be difficult to see both represented.
hi Angela - sure this is a visualization issue when your data setup is correct as expected but you just want to make the visual more evident and clear to see for your audience. In such case, some tips that i apply are: 1) make your data dots apply different marker shape, size and/or border colors, 2) increase your data dots' transparency level so that audience can see through different layers, 3) adjust your axis slightly for each dots just to make them appear slightly apart rather than 100% overlap. hope this is helpful.
Isn't there any shortcut that mark the exact middle point on X or Y axis?
se puede hacer de 9 cuadrantes?
Instead of dots, Can I make with ranges?
This is super helpful! Question: How did you come up with the values of the x and y lines?
Usually you would know the min and max for your x and y axis and you can determine the range for your x and y axis and manually set in Excel like what I did. is this what you ask for?
@@CariprosHRAnalytics Yes! Thank you for responding. If both X and Y axes range from 0 to 100, would you use the midpoint 0, 50, and 100, 50 as X line? Is using the midpoint the right way to determine the X line?
In this way we also create x, y, z axis also
Sadly this doesn't work with Bubble charts :( I managed to get a horizontal line with a trend line (since solid line doesn't appear in the chart options). Sadly trend line doesn't work for vertical lines :(
I can't select data labels from cells in Excel for Mac. Any tips?
I hunted and found a bandage solution with a macro someone created about a year ago. Read this:
Pretty simple fix if you aren't afraid to use a macro. do not fear - try it - just follow the steps, and it will be easy.
Steps are here (I trust microsoft): support.microsoft.com/en-us/help/213750/how-to-use-a-macro-to-add-labels-to-data-points-in-an-xy-scatter-chart
but the steps are written for windows, so here's the MAC keyboard version. and it's even easier than described there. Get the macro into your spreadsheet first:
Step 1: copy and paste their code into clipboard. Then go to menu bar-
Tools/Macro/Visual Basic Editor. Click on "Microsoft Excel Objects" folder icon and select "This workbook". To the right is a blank area to paste the code. Then File/Close. That's it for the macro.
Step 2: build your XY scatter chart. The only condition is that your data must be set up as 3 adjacent columns. A pain, but that's the way it goes. So - your XY scatter data is in columns 2 and 3.
Step 3: click away from the chart, then click it again ("select it").
Step 4: In menu bar, select "Tools", "Macro", "Macros...", and select/run the macro (name is extracted from the first line of the code in step 1).
If all works correct, your XY data point now have labels next to them, taken from column 1.
Finally, if you edit/change the labels (not the data), you have to re-run the macro.
how does it work with bubble chart. I tried but failed to get it. Could you pls show how, thanks
See my reply in this video -> ruclips.net/video/5siLcJOjk58/видео.html
That selection you make at 2:31 isn't an option for me in Excel for Mac. Any ideas how to get that 2nd scatter type?
it's not an option for me either. I can't figure out where she got that from! Anyone find out?
You can check William Reith's answer which I pinned to the top. Hope it helps!
It is also not an option in Excel 2010. I would love to know a fix for the different versions of excel.
This feature exists on the Mac version, but not under INSERT>>CHART. Instead, go to the RECOMMENDED CHARTS section and click on the first SCATTER option, which should preview your two data points as one plot.
@@BourbonBladesBarbecue not on my Mac. There are only 3 charts in the recommended charts dropdown, and none of them are scatter charts. So frustrating.
I think I love you
Can anyone tell me if this is the same as the SPACE matrix? thanks..
as far as I understand SPACE matrix is a specific type of 4-quadrant matrix that's used in management consulting. So with the Excel techniques shown in this video, you can plot a SPACE matrix for your demonstration, but it's not to say that all 4-quadrant matrix are called SPACE matrix.
How do you do this in Google sheets?
Unsure if its possible. Worth a try by following or substituting the steps in the video
I followed but after getting the quadrant dividers the points from the original chart shrunk to only the first half..Am I missing something?
It maybe that your axis got auto-adjusted by Excel. You will need to adjust them manually yourself from the right hand side panels.
What's the version of this MS Excel this? (Year) .......?
Excel 2013 or above
@@CariprosHRAnalytics ok I have 2007 version only Few doubts how make lines in the Graph
All I heard was the word "actually". But not bad, thanks.
haha thanks for the feedback! I will try to do better next time! 😀
women
So great! Thank you, so helpful
Glad it was helpful!😄