Excel for HR: Salary Structure Floating Bar Chart with Employee Data
Вставка
- Опубліковано 28 вер 2024
- Want to learn how to design a salary structure? Check: www.caripros.c...
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-an...
Chart Type: Clustered Column Chart + Scatter
Business Scenario: Create a Salary Structure and show how employees are paid compared to the Structure
~-~~-~~~-~~-~
Please watch: "How to create a Merit Matrix for Salary Increase with Goal Seek function"
• How to Create a Merit ...
~-~~-~~~-~~-~
*****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/...
*****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/in...
#ExcelforHR#HRAnalytics#Excel#HR
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.
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)
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?
can you do it horizontally?
How do we link to job titles as opposed to grades?
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.
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. =)
bars are on the left, the scatter is far to the right - something is missing
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.
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?
If you have multiple with the same grade and the same current salary, what's the best way to illustrate this?
Thank you very much for the video, it really helped me in my initial PG days when I chose HR as a specialization. 😇
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.
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!
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.
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.
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.
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 ?
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
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.
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...
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
Thank you
Excellent efforts. easy explanation and up to the point. keep it up