This works nicely - thank you. I am looking for a way to create a graph where I can show as above, but instead of employee level, I want to be able to show actual job titles (and median of role holders) ...in the same way as you have done with individual employees above. Is there a way to do this, without have job titles (180 of them) listed along the X Axis? Than you.
Very good knowledge video, except whatever or however I tried to replicate the step (and reading all the comments below) the salary axis cannot be one with the range axis, it keeps shown far to the right creating its own axis (except if you make it secondary). perhaps there's some hidden tricks? anyone can help this?
Thanks for sharing. The sets of data I have is like 3000 employees. I start the JG from 5-25 and the scattergram runs out of the range (after JG25). Eg JG 5 is sitting on JG10 on the chart and JG 23 is showing after JG25 (empty space) I am using office 365. How to address this?
I am interested to see a video about how to create a commission scheme for the sales team. What variables should I take into consideration, how do I distribute the percentages, is it based off of revenue or profit, and anything else you might help us with. Thank you for the incredible work.
Sales commissions are almost always complex and for good reasons. I am not gonna be able to cover that in one video, but if you have any specific issues that is good to solve within one video and be useful for a broader audience, welcome to let me know!
@@CariprosHRAnalytics so we have a service as an aggregator for our partners and the sales team are required to sign new partners for different percentages of their profit so we can deliver their service. Out of this service, which is automated we collect customer data that the partners could benefit from by having insights on hotspots to open new shops. So we want to sign new partners on different rate like 5% 10% and so on. Also if we can sell market data that would be an added value.
The scatter diagram that I placed at the last stage made all the other column charts go to the left, and everything became out of scale... as if the x-axis data was out of line... I had a lot of data in the scatter diagram, and it was all within range of the other.... same number of grades, etc.... not sure how to troubleshoot, can anyone please help!
Hi Rachel. Thanks for very useful guides. Btw, if I want to show the EE name or ID data in each orange dot so we can immediately visualize which person is in where in the chart, how I should do. I try many ways but fail. Thank you.
Nice work Rachel! This is very helpful. How do we plot the employee data on the floating chart if we are using an older version of excel? I don't have the combo chart option.
Hi there, thank you so much the vid, this is helpful! Quick question - after pouring in the employee data, there is an extra column on the X-axis that does not mark with any grades (Nil), and seems all my employee data is plotted at one level up compared to the salary structure I built. Could you offer your thoughts please? Thanks!
Your tutorial is very helpful. However, while i was trying it, i could not get the columns and points to align. I was able to create a floating bar chart following your tutorial. I have 5 grades. However, when i was already incorporating the individual salary in the salary structure, the points and the columns do not align. The columns went to the far left of the chart and became thinner even if i adjust it using Format Data Series. Dont know what i did wrong.
This is a very helpful video, thank you! When I go to add a large number of employees, my floating bar chart with my salary structure gets squished all the way to the left. Is there a way to fix this?
it's likely that in your data, you have employees who do not have a job level (blank x-axis value or wrong value). When you filter them out, the chart should work as in the video.
@@CariprosHRAnalytics Hi thank you so much for the great tutorial, i to experience the same trouble as the person commented above.. i have tried as per your comment but the graph still doesn’t reflect the employee data correctly it did not map to the correct grade
I plotted my employees on the primary axis but they don't stack up within the floating bars that correspond to their grade. They appear in a line instead.
Are your grades all in numbers (eg. 1,2,3,...)? I find that with Excel, if you use non-numeric reference for grades like A,B,C... they won't align with the bars. you have to use numbers.
@@CariprosHRAnalytics Exactly, I have been wondering what the problem is with my chart for quite a while. And I did not have the faintest idea what went wrong until I read your comment above. My problem was that I typed G1, G2 to denote Grade 1, Grade 2, which is the source of all problems. By the way, thank you for the great visualisation of an important topic.
hi would you be able to let me know the typical charts to use for a salary review and do you have examples of any charts that might be used in a typical salary review?
Hi Ben, very good question. For salary review I have personally used this floating bar chart for sure, and the Performance Rating Distribution Bar Chart , and the Stacked Waterfall Chart, and the 4-Quadrant Matrix Chart. You can watch my Playlist for Excel charts to see how they are created and what purpose they are used for.: ua-cam.com/play/PLLBzkESv1Bg5OedR6_5t82IP2FQsggIb9.html
I have a problem - when I change the design to a custom combination my employee data stays on the far right and wont overlap with the salary scale - what do I do? I did change employee to scatter and they are far right. I did add the x axis to grade but, they wont overlap?
It's because Excel numerically counts the data series (Grade) from the primary table (Salary Ranges), regardless of the Grade value you use. So replace the grade in the employee data or add a helper column to add the numerical count of each grade. Worked for me! For detail, see: answers.microsoft.com/en-us/msoffice/forum/all/aligning-x-axis-of-scatter-x-y-data-to-clustered/a7728981-cd40-4df1-820c-f236cae5aa63
HI Roshni, I can help at a cost as I mentioned in the video description: ******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$50 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
I made the floating chart just fine from the first video. I am now trying to add employee info exactly how the video says but as soon as I try to add in employee info, it gets rid of the floating chart? any ideas? please help
Is your x-axis all numbers? I find that if you don't use numbers, this type of combo chart may not work. Alternatively, it's likely that you are using a different version of Excel which automatically assumed the chart type. To change that you can try in the "Change Chart Type" under Chart Tools -> Design -> Type, and manually adjust chart type to column chart for your chart.
@@CariprosHRAnalytics Perfect! I got it - thank you. Is there a way to hover or click on the scatter chat (employees) and have it be identified by employee ID/name? These videos are super helpful.
@@tmcgowan00 good question! The short answer is no Excel is not able to do this. When you hover over the dots, you can see what salary amount it is, but you can't see the employee ID/name. I am currently trying to see if we can realize what you want in Power BI, if i figure it out, I will post something out. =)
Thank you very much for the video, it really helped me in my initial PG days when I chose HR as a specialization. 😇
This works nicely - thank you. I am looking for a way to create a graph where I can show as above, but instead of employee level, I want to be able to show actual job titles (and median of role holders) ...in the same way as you have done with individual employees above. Is there a way to do this, without have job titles (180 of them) listed along the X Axis? Than you.
Very good knowledge video, except whatever or however I tried to replicate the step (and reading all the comments below) the salary axis cannot be one with the range axis, it keeps shown far to the right creating its own axis (except if you make it secondary). perhaps there's some hidden tricks? anyone can help this?
Thanks for sharing. The sets of data I have is like 3000 employees. I start the JG from 5-25 and the scattergram runs out of the range (after JG25). Eg JG 5 is sitting on JG10 on the chart and JG 23 is showing after JG25 (empty space) I am using office 365. How to address this?
I am interested to see a video about how to create a commission scheme for the sales team. What variables should I take into consideration, how do I distribute the percentages, is it based off of revenue or profit, and anything else you might help us with. Thank you for the incredible work.
Sales commissions are almost always complex and for good reasons. I am not gonna be able to cover that in one video, but if you have any specific issues that is good to solve within one video and be useful for a broader audience, welcome to let me know!
@@CariprosHRAnalytics so we have a service as an aggregator for our partners and the sales team are required to sign new partners for different percentages of their profit so we can deliver their service. Out of this service, which is automated we collect customer data that the partners could benefit from by having insights on hotspots to open new shops. So we want to sign new partners on different rate like 5% 10% and so on. Also if we can sell market data that would be an added value.
The scatter diagram that I placed at the last stage made all the other column charts go to the left, and everything became out of scale... as if the x-axis data was out of line... I had a lot of data in the scatter diagram, and it was all within range of the other.... same number of grades, etc.... not sure how to troubleshoot, can anyone please help!
same here, the combo chart of ranges and actual pay are on different scales (despite the numerical values being the same)
Hi Rachel. Thanks for very useful guides. Btw, if I want to show the EE name or ID data in each orange dot so we can immediately visualize which person is in where in the chart, how I should do. I try many ways but fail. Thank you.
If you have multiple with the same grade and the same current salary, what's the best way to illustrate this?
Nice work Rachel! This is very helpful. How do we plot the employee data on the floating chart if we are using an older version of excel? I don't have the combo chart option.
Hi there, thank you so much the vid, this is helpful!
Quick question - after pouring in the employee data, there is an extra column on the X-axis that does not mark with any grades (Nil), and seems all my employee data is plotted at one level up compared to the salary structure I built. Could you offer your thoughts please? Thanks!
My salary grade data is on the far left of the chart and my employee data is on the far right. I can't get them to overlap. Can you help?
Your tutorial is very helpful. However, while i was trying it, i could not get the columns and points to align. I was able to create a floating bar chart following your tutorial. I have 5 grades. However, when i was already incorporating the individual salary in the salary structure, the points and the columns do not align. The columns went to the far left of the chart and became thinner even if i adjust it using Format Data Series. Dont know what i did wrong.
can you do it horizontally?
Excellent efforts. easy explanation and up to the point. keep it up
Hi! Thanks that’s helpful. But why my dots on a right side of the chart and not on the bars?
I have the same problem
@@eslamri7051 me too :(
job email I did it but with two different charts one of them invisible with no color and I put it on the other one to take this result like the video
Hello Madina, I have the exact same issue. Did you find a solution ?
How do we link to job titles as opposed to grades?
This is a very helpful video, thank you! When I go to add a large number of employees, my floating bar chart with my salary structure gets squished all the way to the left. Is there a way to fix this?
it's likely that in your data, you have employees who do not have a job level (blank x-axis value or wrong value). When you filter them out, the chart should work as in the video.
@@CariprosHRAnalytics Hi thank you so much for the great tutorial, i to experience the same trouble as the person commented above.. i have tried as per your comment but the graph still doesn’t reflect the employee data correctly it did not map to the correct grade
@@tahhak2504 do you have text in your grades? if so it may not work either and you can try using only numbers for your grade
I had the same problem... and could not solve it...
I plotted my employees on the primary axis but they don't stack up within the floating bars that correspond to their grade. They appear in a line instead.
Are your grades all in numbers (eg. 1,2,3,...)? I find that with Excel, if you use non-numeric reference for grades like A,B,C... they won't align with the bars. you have to use numbers.
@@CariprosHRAnalytics Exactly, I have been wondering what the problem is with my chart for quite a while. And I did not have the faintest idea what went wrong until I read your comment above. My problem was that I typed G1, G2 to denote Grade 1, Grade 2, which is the source of all problems.
By the way, thank you for the great visualisation of an important topic.
This does not work if you have a number of employee's in one pay grade
hi would you be able to let me know the typical charts to use for a salary review and do you have examples of any charts that might be used in a typical salary review?
Hi Ben, very good question. For salary review I have personally used this floating bar chart for sure, and the Performance Rating Distribution Bar Chart , and the Stacked Waterfall Chart, and the 4-Quadrant Matrix Chart. You can watch my Playlist for Excel charts to see how they are created and what purpose they are used for.: ua-cam.com/play/PLLBzkESv1Bg5OedR6_5t82IP2FQsggIb9.html
Hi what about if internal pay ranges and performance ratings dont exist in the business
I have a problem - when I change the design to a custom combination my employee data stays on the far right and wont overlap with the salary scale - what do I do? I did change employee to scatter and they are far right. I did add the x axis to grade but, they wont overlap?
It's because Excel numerically counts the data series (Grade) from the primary table (Salary Ranges), regardless of the Grade value you use. So replace the grade in the employee data or add a helper column to add the numerical count of each grade. Worked for me!
For detail, see: answers.microsoft.com/en-us/msoffice/forum/all/aligning-x-axis-of-scatter-x-y-data-to-clustered/a7728981-cd40-4df1-820c-f236cae5aa63
Did you ever get a reply? I am having the same issue.
bars are on the left, the scatter is far to the right - something is missing
I have been trying to create this for around 100 employees but it's not working correctly. Could you please help?
HI Roshni, I can help at a cost as I mentioned in the video description: ******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$50 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
Thank you
I made the floating chart just fine from the first video. I am now trying to add employee info exactly how the video says but as soon as I try to add in employee info, it gets rid of the floating chart? any ideas? please help
Is your x-axis all numbers? I find that if you don't use numbers, this type of combo chart may not work. Alternatively, it's likely that you are using a different version of Excel which automatically assumed the chart type. To change that you can try in the "Change Chart Type" under Chart Tools -> Design -> Type, and manually adjust chart type to column chart for your chart.
@@CariprosHRAnalytics Perfect! I got it - thank you. Is there a way to hover or click on the scatter chat (employees) and have it be identified by employee ID/name? These videos are super helpful.
@@tmcgowan00 good question! The short answer is no Excel is not able to do this. When you hover over the dots, you can see what salary amount it is, but you can't see the employee ID/name. I am currently trying to see if we can realize what you want in Power BI, if i figure it out, I will post something out. =)
Hi，I have trouble in putting the individual salary in correct grade after setting the X axis. Could you please help?
I have the same issue. Can you help
@@nazneenjn What are your grade labels? Are they numbers? Letters? A combination? I had the same problem forever and I think I figured out a fix.
What are your grade labels? Are they numbers? Letters? A combination? I had the same problem forever and I think I figured out a fix.