Collaborate on Excel Invoice Software for Human Resources with Ease Using airSlate SignNow

Excel invoice software for Human Resources

welcome to the data sheet in this data sheet we see app ID their name their surname age how long they've been at the company now we're going to use this data to analyze the different age brackets that our employees come in hello there and welcome to this tutorial in which you'll learn how to build a people analytics dashboard in Excel best part it's an interactive people analytics dashboard you'll also learn how to do some actual people analytics you work with employee data with salary data with performance data and with much much more so if you're interested buckle up and let's Dive Right In imagine your HR Director comes to you and asks you three questions the first is how many employees do we have and what does the age distribution in our organization look like second do we really pay for performance so do we pay top performers better than average and bottom performers and three is there a salary gap between Mill and female employees can you answer this question here's the data set I'm going to help you how to do it let's Dive Right In now before we start off don't forget to like this video hit the Subscribe button and click the notification Bell so that you get an update whenever we post a new [Music] video Welcome to the data sheet in this data sheet we see app ID their name their surname a age how long they've been at the company that gender the region and department they work in whether they're a manager yes or no the hours that they work the salary band that they have the full-time equivalent salary their actual salary so if they work less they get paid less and their performance bracket which can be bottom top or average now we're going to use this data to analyze the different age brackets that our employees come in so how do we do that first of all we want to format this beautiful table that goes all the way to row 836 we want to format this as a table so I click a random cell in the table I hit insert and then table and then it selects the range automatically I click my table has headers and I hit okay and all of a sudden we have a beautiful table this is very helpful because this helps us to create pivot tables and it helps us to calculate things much faster because structured data in a table is processed by Excel much faster which is very helpful in case you have a slower computer so now I first want to see the H distribution how do I do that well I could potentially sort everything and then go through it and tally it one by one that's not a very efficient way of doing it so the way I do this is I go to insert and I hit pivot table it selects table one that is the table I just made and I want to have the pivot table in a new worksheet I hit okay and here is my pivot table area so what do I do with this well first of all we want to see the different ages I put age here in the rows and we see the overview of all the different ages that we have in the company starting at 19 all the way to 65 now I want to know how many of each of these individual ages we have so I take AG again and I put it in the values and now you see it automatically jumps to the sum of age I don't want that I want the count so I click it I click value field setting and I go to count and if I hit okay you see that there are two people who are 19 and the sum is 38 but the count is actually two so we want the count not the sum and here we have a great overview where we know that two people are 19 four are 20 6 are 21 Etc this is a good overview but this is much too granular so how can we make this more actionable we do this by right clicking anywhere in the pivot table and hitting group when we hit group we say well we want to start at a nice round number let's say 20 we want to end at 65 that's fine and then we want incremental five we hit okay and there we go we see that we have two people below 20 we have 33 people from 20 to 24 25 to 29 is about 92 people Etc this provides a great overview and we can easily visualize this by putting our cursor anywhere in the pivot table hitting insert going to the bar chart and creating a bar chart over here like this beautiful I think this is a great first visualization that answers our h director's question how many people do we actually have and we can visualize this in an even better way by clicking on the table going to design saying we want to add a chart element and we want to uh add the numbers inside the bars so we have a great overview of how many people are actually in there and I don't like that the bars are quite thin so I can rightclick them I can say format the data series and then I can Ed the Gap width to about 100% I hit okay and we see that the bars fit much nicer this is a table that I enjoy much more here we say the distribution of employees by age I can remove this total because that's just one series and here we go we have a great overview of the distribution of employees by H bracket so let's clean this up and let's make an actual interactive HR dashboard I'm going to select some of my cells zoom out a little bit I'm going to merge it I'm going to make it a nice big font and I'm going to say this is my interactive HR dashboard so let's make it truly interactive I'm going to select all my information give it a nice blue color make it fat and add a line at the bottom just because it looks slightly nicer there we go the big line at the bottom and there is the beginning of our dashboard let's move some elements around and let's start to build the dashboard that will help us answer or HR dirce questions so I'm going to move this table this pivot table I click it I go to pivot table analyze I hit move and I'm going to move it towards c25 somewhere around there so this is the overview of our age groups I can call this groups I can move this table down here and we have a first idea of what a dashboard could look like now there's much more information that I want to add for example let's start to think about that initial question that I asked what is the average uh salary between male and female employees and can we get you know a good understanding of that so let's Dive Right In I'm going to create a nice click clear box with again some borders around it the outside and I'm going to visualize this in a nice way by merging some cells and saying hey here we want actually the average salary of employees and here we want the average age of employees so let's now create the statistics that we can put in these fields in order to answer this question so how do we draw the average age into our interactive dashboard and I'll show you how so what we want is we want to create yet another pivot table so I'm going to go to the main data set I click insert pivot table I want it in a new worksheet and there I'm going to put the age straight into values because we're talking about the entire data set and I'm going to hit average so this is the average a age I'm going to do the same I'm going to stand here I say insert pivot table the table is again table one in that cell and this is the average salary so again we go to full-time equivalent salary we make that the average and here we are we have the average age and the average salary so now I go back to my dashboard I'll call this sheet dashboard and I want to pull up the average salary I do it by hitting equal and now I'm going to link back to that average salary there we go as you can see the formula now says equal and then it goes to um this sheet and it pulls out this data I hit enter and here's the average salary now we can format this in a much nicer way we can make it dollars we can format it in the middle and we can make it a much bigger number we can remove some of the decimal we don't have to know it um all the way behind the decimal and well that already looks much better I'll give this some color I'll give this some color let's now also look at the average age again I open it I click equal I go to the pivot table I hit the average age again I hit enter and there it pulls up the average age 41.93 something again I get rid of all the decimals and I'm going to format this the same way so I'm going to make this size 18 give this a nice blue color I'm going to make it fat and or bolt and there we go so that already looks much better so we now know the average salary and the average age across our data set but we want to know the difference between males and females how are we going to figure it out that is the next question so let's add some slices that allow us to slice and D the data I go to any pivot table in my sheet I hit pivot table analyze and I say insert slices I want to add four slices which are people's gender the region they work in that department and whether they're a manager yes or no I hit okay and now you see that there are different slices that if I make a selection now I only want to look at managers I see that the age distribution of managers is different from the total H distribution and again I can just look at the finance department and I see that that H distribution changes again what doesn't change are that average salary and average age I can make any selection but that age Remains the Same some reason because the column width changes average salary is hidden but average salary is still at 103,000 now the way I fix this is going to this slicer and I don't just want to work with this pivot table I also wanted to work with these two pivot tables so the way I do this is I hit gender I go to slicer here at the top I say report connections and I don't just want it to link to pivot table one but also to pivot table two and pivot table three I hit okay and now when I just select Mills let me make this slightly smaller Mills you see that there's a salary of8 8,000 but for females there's an average salary of 96,000 which is a quite big discrepancy now I don't know why this is maybe females work much more part-time and there's some compensation conflicts with that or they work in less scor functions but there is a big difference and we need to look into that and potentially do a pay Gap analysis to figure out where the difference is coming from so it's at least the first indication that something is not right here so so let me just order this in a nice way and put this together in our dashboard where I start with the regions then I add the gender of people it's a binary system um in this data set um and here is the department there we go so now we have quite a nice overview of all the different slices that we have and it starts to look like an actual dashboard of course in the region you know the regions are not connected yet so I also want to connect these other slices again hit that report connection and connect it to all the different pivot tables that we've made and now our interactive dashboard is starting to take shape so now I've connected all these slices and I can see that in finance the average salary is quite High well in human resources that salary is much lower so we're not doing something you know we're not we're doing something wrong in HR we need to be paid more and again I can clear the filter and there we go so that is the second part of our analysis we have the feeling that if we just look at genders we see that between the two genders there's quite a big pay Gap and that is something that we should analyze some more in order to figure that out the final question that CH director asked us was do we see a difference in pay when it comes to Performance so are top performers paid better than average and bottom performance let's figure that one out as well I'm going to create one more pivot table and in that pivot table I want to look at salaries and at performance so the way I do this is I hit insert I go to the pivot table I again take the data from table one and it says I don't have enough space to make the dashboard so I was too excited about making everything look pretty already so I'm going to move this down a little bit and now I should be able with the same actions to create a pivot table so Excel is afraid that the tables start running into each other and that is not a good thing so here I look performance I put performance in the columns so we see average bottom and top performance brackets and I want to compare with salaries so I'm going to put the salaries here in the values I'm going to make that an average because I don't want to look at the sum of everyone I want to look at their average and I now see that on average our average performers get paid about 103,000 our bottom performers about 95,000 and our top performers about $11,000 I can format this in a nice way by going to home again hitting this currency button clicking the US dollar notation and removing the decimal places and now it becomes much clearer one more thing I can change is I want bottom to be on the left and then average in the middle and then top on the right the way I do this is I right click bottom I say move I say move bottom up and then bottom is first then we have average and then we have top and this allows us to make a very nice comparison now this is not all because we saw in our data set that we have different salary bands and I want to know if a top performer in the first the lowest salary band T1 kind of your Junior bracket if top performing Juniors get paid more than bottom performer Juniors but also for the mid level and also the senior level salary bands I want to know if that difference between top performers and bottom performers holds consistent across different salary bands because theoretically it could be the case that a lot of top performers are in our lower salary bands and bottom performers are in higher salary bands and that skews the average so I go back to the dashboard I put our salary band here in the rows and now I see for the five different salary bands that we have I see that bottom pay is indeed lower than top pay in terms of performance across all the five salary pants so this gives me an indication that it actually looks quite balanced looks quite good I can easily visualize this by again creating a bar chart I put that bar chart here smack in the middle and what do we see we see that bottom performers across the five salary bands are paid less than top performers and with that I can start to pull my dashboard together and create some visualizations because essentially we're done we've now answered all the questions that our HR director asked of us and we can start to visualize some of our dashboards so I can give this a nice color I can add to this dashboard a title where I go to design add chart elements I go to the Chart title I say hey this is performance per salary band again I can nicely visualize this give this an attractive color make it bold and what we see here is an interactive dashboard doesn't look that good right yet um in order to make those grid lines go away I hit the view button I click grid lines and here we go we have a fully interactive dashboard the only thing that I haven't done yet is you know I can make a selection between the different departments I haven't collected or connected this new pivot table to the different slicers you remember how to do that you again go to slicer vort connection and I add that fifth pivot table to all these slicers and now my entire dashboard should be or all the slices should be fully connected with all the data in my dashboard and with that I have a 100% interactive dashboard that I can go to my HR manager to say if we look at you know people are not in managerial roles we see an average salary of 103 people who are in managerial roles oh they actually have the same salary that is something that we maybe want to look into potentially when we look at females we see that the salary is much lower and we can also look through the different salary bends and we actually see that the difference between bottom and top performers when it comes to um females is not as strong as when it comes to males so it could almost be could actually be that top performing Mills are getting a better award but we don't have have the same system in place or it's not as effective for females and that is something that we should definitely look into when it comes to that pay Gap analysis that requires a little bit more data so this is a great way of how you can uh build a dashboard uh create that visualization and pull all of that data together there we go this is our interactive HR dashboard that we can now go to the HR Director to and that we can use to answer all of our HR questions with I hope you enjoyed this video I think this is a fun way to build an interactive and very useful HR dashboard and we actually did some people analytics along the way also known as HR analytics because we did an actual analysis of our bottom performers paid less than top performers and in general the answer is yes fortunately that's the case and we can go back to RI director and Report those answers using this fantastic interactive dashboard that's it for me in this video If you enjoyed it check out our courses at the Academy to innovate HR we have a full course on how to build dashboards in Excel in powerbi and how to do actual people analytics even more much more advanced analyses using Excel so check that out if you're interested and of course don't forget to like this video subscribe to our Channel and hit that notification button have a good day bye-bye [Music] oh