Excel for HR: Salary Structure Floating Bar Chart with Employee Data

แชร์
ฝัง
  • เผยแพร่เมื่อ 30 ต.ค. 2024

ความคิดเห็น • 50

  • @prexakunverparmar2628
    @prexakunverparmar2628 2 ปีที่แล้ว +1

    Thank you very much for the video, it really helped me in my initial PG days when I chose HR as a specialization. 😇

  • @Monstersusu
    @Monstersusu 4 ปีที่แล้ว +5

    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?

  • @davidchambers5075
    @davidchambers5075 2 ปีที่แล้ว +1

    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.

  • @nhungbui1832
    @nhungbui1832 11 หลายเดือนก่อน

    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.

  • @floalas
    @floalas 8 วันที่ผ่านมา

    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?

  • @de_life_stream6044
    @de_life_stream6044 5 ปีที่แล้ว +1

    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.

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  5 ปีที่แล้ว +1

      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!

    • @de_life_stream6044
      @de_life_stream6044 5 ปีที่แล้ว

      @@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.

  • @AmirSafa-t7y
    @AmirSafa-t7y ปีที่แล้ว +2

    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!

    • @paulmreagan
      @paulmreagan ปีที่แล้ว

      same here, the combo chart of ranges and actual pay are on different scales (despite the numerical values being the same)

  • @DRLAMB85
    @DRLAMB85 5 ปีที่แล้ว +2

    If you have multiple with the same grade and the same current salary, what's the best way to illustrate this?

  • @MC-vl1sd
    @MC-vl1sd 5 ปีที่แล้ว +1

    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!

  • @rbreal10
    @rbreal10 2 ปีที่แล้ว +1

    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?

  • @willgreen2284
    @willgreen2284 6 ปีที่แล้ว +1

    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.

  • @evahermogenes1502
    @evahermogenes1502 8 หลายเดือนก่อน

    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.

  • @AmirSafa-t7y
    @AmirSafa-t7y 11 หลายเดือนก่อน

    can you do it horizontally?

  • @mohameddeyab7668
    @mohameddeyab7668 4 ปีที่แล้ว

    Excellent efforts. easy explanation and up to the point. keep it up

  • @krystinamyers9843
    @krystinamyers9843 6 ปีที่แล้ว +1

    How do we link to job titles as opposed to grades?

  • @Acting1435
    @Acting1435 5 ปีที่แล้ว +1

    Hi! Thanks that’s helpful. But why my dots on a right side of the chart and not on the bars?

    • @eslamri7051
      @eslamri7051 4 ปีที่แล้ว

      I have the same problem

    • @jobemail3468
      @jobemail3468 4 ปีที่แล้ว

      @@eslamri7051 me too :(

    • @eslamri7051
      @eslamri7051 4 ปีที่แล้ว

      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

    • @lyndahocini773
      @lyndahocini773 3 ปีที่แล้ว

      Hello Madina, I have the exact same issue. Did you find a solution ?

  • @jessicaheadington4669
    @jessicaheadington4669 5 ปีที่แล้ว +1

    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.

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  5 ปีที่แล้ว +1

      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.

    • @emrullahtan8695
      @emrullahtan8695 4 ปีที่แล้ว

      @@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.

  • @andrewgenecki5160
    @andrewgenecki5160 3 ปีที่แล้ว

    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?

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  3 ปีที่แล้ว

      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.

    • @tahhak2504
      @tahhak2504 3 ปีที่แล้ว

      @@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

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  3 ปีที่แล้ว

      @@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

    • @AmirSafa-t7y
      @AmirSafa-t7y ปีที่แล้ว

      I had the same problem... and could not solve it...

  • @juanitacamacho2982
    @juanitacamacho2982 2 ปีที่แล้ว

    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?

    • @gjavo6400
      @gjavo6400 2 ปีที่แล้ว

      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

    • @rbreal10
      @rbreal10 2 ปีที่แล้ว

      Did you ever get a reply? I am having the same issue.

  • @DundeMan
    @DundeMan 6 ปีที่แล้ว

    Thank you

  • @benhoward100
    @benhoward100 6 ปีที่แล้ว

    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?

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  6 ปีที่แล้ว

      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.: th-cam.com/play/PLLBzkESv1Bg5OedR6_5t82IP2FQsggIb9.html

    • @benhoward100
      @benhoward100 6 ปีที่แล้ว

      Hi what about if internal pay ranges and performance ratings dont exist in the business

  • @roshnihegde6232
    @roshnihegde6232 5 ปีที่แล้ว

    I have been trying to create this for around 100 employees but it's not working correctly. Could you please help?

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  5 ปีที่แล้ว

      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

  • @tmcgowan00
    @tmcgowan00 4 ปีที่แล้ว

    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

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  4 ปีที่แล้ว

      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.

    • @tmcgowan00
      @tmcgowan00 4 ปีที่แล้ว

      @@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.

    • @CariprosHRAnalytics
      @CariprosHRAnalytics  4 ปีที่แล้ว

      @@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. =)

  • @billykong7650
    @billykong7650 6 ปีที่แล้ว

    Hi,I have trouble in putting the individual salary in correct grade after setting the X axis. Could you please help?

    • @nazneenjn
      @nazneenjn 4 ปีที่แล้ว

      I have the same issue. Can you help

    • @davidluke4082
      @davidluke4082 3 ปีที่แล้ว

      ​@@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.

    • @davidluke4082
      @davidluke4082 3 ปีที่แล้ว

      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.

  • @ErnieGarcia-b9n
    @ErnieGarcia-b9n 20 วันที่ผ่านมา

    This does not work if you have a number of employee's in one pay grade

  • @crusader41ify
    @crusader41ify 6 หลายเดือนก่อน

    bars are on the left, the scatter is far to the right - something is missing