HR KPI Dashboard Template

HR KPI Dashboard Template Excel

Learn how to create a dynamic HR KPI Dashboard Template in Excel using grid layout, multiple KPIs, and advanced chart types.

This tutorial will show you how to build a Human resource KPI Dashboard Template from the ground up. We’ll use custom charts, infographics, and a drop-down list to make the template dynamic.

Build a Human Resource KPI Dashboard using Excel

Before we dive deeply into the details, you can download the practice files. You’ll get two spreadsheets, the light and the dark version.

Download the spreadsheet.

Prepare Data and do some calculations

In the example, we’ll use the following data set. The first 10 rows show various key performance indicators, from the Absence rate to the Turnover rate.

We use progress charts to visualize four highlighted indicators:

  • 90-day Quit Rate (%)
  • Training Effectiveness (%)
  • Acceptance Ratio (%)
  • Head Count (%)
data set for the dashboard

We follow the common dashboard design rules, so our initial data set is in the ‘Data’ Worksheet, and we perform calculations and insert a new Worksheet, ‘Calculations’. Let us calculate the variance between the selected and the last month here. Furthermore, -to make our HR Dashboard dynamic- we link the drop-down list result from the Dashboard sheet back to the ‘Calculations’ sheet.

Use the following formula to get the actual value based on the drop-down list selection. Type in cell V6:

=IF($F$5=$T$4,F6,IF($G$5=$T$4,G6,IF($H$5=$T$4,H6,IF($I$5=$T$4,I6,IF($J$5=$T$4,J6,IF($K$5=$T$4,K6,IF($L$5=$T$4,L6,IF($M$5=$T$4,M6,IF($N$5=$T$4,N6,IF($O$5=$T$4,O6,IF($P$5=$T$4,P6,Q6)))))))))))
calculation

Use the formula in cell X6 to get the T-1 value:

=(IF($T$4=$F$5,F6, IF($T$4=$G$5,F6, IF($T$4=$H$5,G6, IF($T$4=$I$5, H6,IF($T$4=$J$5, I6, IF($T$4=$K$5, J6,IF($T$4=$L$5,K6,IF($T$4=$M$5,L6,IF($T$4=$N$5,M6,IF($T$4=$O$5,N6,IF($T$4=$P$5,O6,IF($T$4=$Q$5,P6,"")))))))))))))

Finally, get the Month-by-Month result:

=(V6/X6)-100%

Insert a Drop-down list

Take a look at the main dashboard sheet. We strongly recommend using slicers or drop-down lists to make your HR KPI Dashboard dynamic. First, create a list that contains months. Then, select the cell where you want to insert the drop-down menu.

insert a drop-down list

Click the Data Tab on the ribbon, select Data Validation, then choose the Data validation command. Under the validation criteria tab, choose the ‘List’ option. Under the ‘Source” Group, browse cells containing the month’s names.

Create a Card layout

A card is a key element of a dashboard. It uses small spaces and effectively shows trends, line charts, and the actual status of the KPI you want to track.

Steps to create a card:

First, add a data label for the card. Insert a text box, type an equal sign, and connect the KPI name from the ‘Data’ worksheet. The label is dynamic since we have a link between the data and the ‘Dashboard’ worksheets.

HR KPI Dashboard card layout

Next, select the monthly data on the ‘Data’ sheet and insert a line chart. The next step is to add the actual value to the card. Select the cell the link contains the actual value and link it to the ‘Dashboard’ Worksheet. In the last step, we will display the variance between the selected and the previous month’s data. Use the ‘Calculation’ Worksheet to link the data to the card.

HR KPI Dashboard – Metrics

It is important to pick the proper key performance indicators. As usual, we have limited space on the dashboard, so focus on the relevant metrics.

We’ll display the following HR KPIs on the card:

  • Absence rate
  • Absence cost
  • Benefits Satisfaction
  • Productivity rate
  • Satisfaction index
  • Engagement index
  • Internal promotion rate
  • Net Promoter Score
  • Quality of hire
  • Turnover rate

To highlight the key metrics, like ’90-day quit rate’, ‘Training effectiveness’, ‘Acceptance Ratio’, and ‘Head Count’, apply a progress circle chart.

progress circle chart

Additional resources: