Excel for HR Market Benchmark Pay Report with Floating Bar Chart (Part 1)

Hello everyone, today I want to show you how to benchmark with market pay for multiple jobs.

So the business scenario is like this which is actually a vertical one.

So you are at your manager and then a business manager come to you to complain as they always do that.

We're not paying enough. So let's say for this example, I have this is an IT manager and then he has team members and I use services which is a general department.

And he also has a team of data analytics which is an emerging, changing team that a lot of company hiring.

So he's complaining that we're not paying enough for the data analytics and analytical people that we have in the company and he'll want to increase that for those people.

So with that request, what do we do now?

Like rather than just give them the money that we want to of course we need to do some analysis.

So the first step that I would do is really just pull out the market information, the data information for those two specific jobs.

So I'm not showing that here, but then to do that, you really need to understand the jobs, you need the job description from the manager or interview manager on standing with the job does.

And then benchmark them to the market pay service, like for example, Mercer, Aaron Hewitt, Hey Group.

And then you can get some kind of market data like this and it's usually a wrench with the minimum and the median, which is market 50% and max.

If you have some good service, like the one that I have, you would also have the 25th percentile, which is what I have as p25 and the 75th percentile, which means that if you're not caught from me, I was at p 75th basically means that for all the people in the market.

75% of people are paid below this point. So this is a table. So this is actually the table that you want to have to star with.

And you can also see that because we have different job levels, I have three levels mark here, analyst level senior analyst level and manager level.

And actually just with those two table add a glance, you can roughly if you compare those two can roughly have an idea, okay, it is true that the data analytics wants a paid slightly higher.

But how do we visualize that for our manager and also for the budget approval?

This is what I want to show you today, like this is exactly the chart that I want to show you. So rather than showing them all those numbers, just show them this chart is visual is easy.

It shows the two jobs side by side by job level and then for each of the two market pricing, you have the minimum, the medium and the max showing here.

So just looking at this chart, you know exactly that data analysis is actually not paid very competitively on the low end.

But then when it comes to the higher end, like the senior analyst level and the manager level, they are definitely paid higher.

And then this is very user friendly for anyone who has even no experience with compensation.

So how do we come to here from here is what I want to show today? So to start with you actually need to organize those two table into something like this, which is the market pay data.

So basically you would have because you have two dimensions, the first one is job level. So if you want to have the job family side by side, then you have the job level first.

And then you organize the job family side by side, IT service and data analytics, and then you organize the market data accordingly. So for example, actually if you like to look at my formula here, I'm actually linking.

Oh, I'm actually linking the data to the owner, actually that's not my analyst level. So yeah, so actually I'm linking my analyst level to the original table.

And I'm linking my data analytics analyst to the table. Yeah, so just something like that, but then basically you just need to make sure you have a table like this.

So you see that I have those two dividing lines there. If you want to have a separation between the job levels, then simply just have a blank roll in between to separate them.

And here it goes. So here is actually the data sets that we need for the chart. So select all of them. So after you organize a clean them, select them and then go to insert.

For the record for the chart, you need to use clustered clustered columns there. So this is actually how the data, how Excel will give you will excel will give you, and then this is what you need to work with. So for two star was I like just as a personal reference preference, I usually get rid of the great line.

And for all the bars here, now we need to organize them into the floating bar chart. So what I would always do is to go to any one of the.

Any one of the data bars theory and then format data theory, click on it, and then you can see the theory overlap and gap with. So this is what I used most often, and I usually for the floating ones, you just need to make sure they overlap.

But then as you increase the overlap, do you see the problem here, like with the chart that we have right now, basically you have the highest one, the max in the front. So when you overlap, it's actually hiding everything else.

So in so in order to prevent that we actually want highest one to be on the back to be in the back and we want the lowest one to be in the front. So what you need to do for that is again, make sure the series selected and then go to select data right click go to select data.

And then basically you have all the data series that you have. So the market max is representing the highest bar, we want to bring it to the back right this is how we do it move up.

So you move it up, make sure you're moving it up.

Yeah, so you see it's kind of slow by it's moving there, so you can see I want to have the max to be on the back, so as you see, I just moved it to the back.

And then the second one I want to want to have is p75 because that's the second highest and again move it back, but move it in front of the max.

And the third one is made wanted to be also in the back, but in front of the p75 and then same for the p25, I wanted to be on the back of me minimum.

So now you see I basically flip the order for all the bars and now let's go okay and then let's go back to formatting.

And then now when you overlap, so now is the time you can go 100% overlap and then you can see you can see the high still see the highest bar on the end.

And if it's like this is my personal preference, but I usually prefer the gaps to be up in narrow like maybe 15% like personally, I just like it because now you can compare them side by side, but you also make sure like between job levels you have some spacing there.

So now let's make this now you have the data let's make it more beautiful so the first thing you need to do is select the minimum data and then make sure you feel it as white so that now it's the floating bar.

What I also want to make it look nicer is to make sure I have a white outline for each of my bars so you can see the division there more clearly.

I would have the white outline for all of them.

And then I want also want to make the color look even better.

So usually I would use the same.

Color Tom, but then I would use them of different.

So for example, for the max, I want to use a lighter blue.

And then for the p75, I want to use a darker blue.

For for the p25, I want to use a darker blue as well.

So that you can easily identify the p750 and then you can also see like the top and bottom are the minimum and the max range.

So this is pretty much how it is going to look like, of course, we can change the title.

To market.

And then you can identify the jobs here and then you can also change the.

The access to see maybe format access and then maybe I don't want it to start from zero I wanted to start from 20 and I wanted to max at one point so that I have a.

Like a longer bar so you can show the differential issue more clearly.

And another thing that you might notice here is that because you have this blank row here.

Your name like the analyst name is actually floating over to the next role, which doesn't look nice right and how do we.

And how do we make sure the analyst just stays.

Besides the data analytics analytics.

Once here's the trick that I found out like basically you just need to make sure you block.

This one and this one with some tax but then you don't want some tax right so what I would actually do is maybe put a star.

When you put a star on the star will be here but the analyst will be right under the data analytics if you think star is too big.

You can also insert a symbol which is like say a very small dot.

It will do the same trick.

But it's almost like when you look at here is almost invisible.

And then if you do the same here and basically.

This is how it's going to look like.

And then of course you can also move it to a different chart to a different tab.

And the last tab I want to do is really just add the data label.

So you can see I have the top on the top.

I select the minimum one which is blank one notice it's not to the gray blue one is actually the blank one add data label.

You see it's on the top and then select the data label and then say format data label and then make sure it's inside.

And so that is actually outside and at the end of it.

The part so it shows like the minimum is 35 or 40 and then you also want to select the p50.

And then also add the label to show that.

It is like a certain amount but then because I have been using our.

Darker color gray darker color here so I actually wanted to use white for.

Notifying the pay level and then you might need to manually.

Like a chest numbers a little bit but then to a place that that's most.

Use it differently but then essentially this is how your market benchmark report would look like.

If you find this video helpful please like and subscribe to my channel if you have any question please leave a comment and I'll be happy to answer it.

Thank you so much for watching and I'll talk to you soon.