It is not a customary task to plan and develop a HR Analytics Dashboard. It’s time for us to show you how to build very quickly a custom HR template. It is very useful for an HR leader so much so that it is rather an expectation to keep an eye on the human resources of the company.
These departments can be found in all major corporations.
The conception haven’t changed, we keep ourselves to the one-page-dashboard rule, however much is the data, we will display it on one single excel page.
HR Analytics Dashboard – Metrics and Visualization
In connection with the HR project we will display the following key performance indicators: Total Salary, Total Bonus, Total Overtime, Total Sick Days and average related to these indicators.
As an addition we will evaluate the average performance on a scale from 1 to 5.
Let’s start to analyze the displayed data from the top!
We can choose the summed up values for a department with a help of a drop-down menu.
We can find the departments on the “data” worksheet.
In general we can say that the resource-data of the hr analytics dashboard is always placed on a separate worksheet so they completely separated from the end-result dashboard.
We realize the connection between the two tables with the help of the VLOOKUP and the SUMIF functions.
After we have choosen a department, we use the well-tried (and detailed in our previous article) formula REPT and the Webdings font and display the size of the department.
The number of displayed figures determines the number of co-workers in the given department. If you making a different nature of dashboard then you can use other special characters of the Webdings fonts, for example you can chose from the symbols below.
As you can see variety / selection is large. We have marked the special character on the picture that we have used in this excel dashboard.
You can insert the symbols with the following method:
The symbol button on the Insert tab launches the Symbol screen, which includes two tabs. The Symbols tab has lots of features. You can pick both a Font and a Subset of unique characters.
Subsets provide groups of related symbols.
For example, use the Subset menu to switch to people symbols. When you locate the symbol you like to use, click Insert. Finally double-click to embed a symbol to complete hr analytics dashboard.
HR Analytics Dashboard – Using Shapes
The excel shape is such a vector object that we can use freely to make our dashboard presentation more spectacular. There is no ‘magic’ here, we have placed 1-1 different colored (blue / white) circles on top of each other and they also partly cover each other.
With a help of a simple text box we can assign arbitrary values to each and every vector object.
We only have to choose fonts (in this case the Eras Bold ITC) and the perfect headings and labels are done.
Excel lend us hand in many problems, it didn’t let us down today either.
Okay, what’s next?
Now we will design an excel hr dashboard applying some unique and original technique.
Our principal is just like before. We will make a simple dashboard that is suitable for managerial goals and allows a quick analysis.
When you understand the principals of dashboard making than in a short time you can be professional at it at your area of specialization. And this is our primary objective.
The drop-down menu list is known for those who work with Excel. We have talked about it before we wouldn’t detail it now.
Components of HR Dashboard
The component of the base board is as follows: in the first column there are the names, in the next four we can find the quarterly data. The sixth column contains the average performance, and the seventh is the qualifications.
You may convert or replace the qualification base settings as you wish (you can find these in the Final column). The Plan column shows the planned values.
Let’s see how the chart works! On the chart we will always view the chosen co-worker’s achievements in a plan / fact breakdown.
The blue line shows the quarter year achievements, the green shows the average achievements.
The red line represents the expected (planned) values. We can see, that in this case the chosen co-worker has accomplished under expectation.
Using a profile picture in HR Dashboard
To apply a profile picture in Excel? We have not seen this before, although the opportunity is given, we only need a little creativity.
To view the picture we have applied a new technique (picture-link), for details see the calc worksheet.
It is simple but a great working method.
In general we can also say that when creating an Excel dashboard a lot of times it is enough to use 1-1 small new idea which is brightens up the presentation.
You should keep that in mind.
Download this free excel dashboard template!