HR Dashboard Gantt Chart / Traffic Light Report

So you created your HR Dashboard or Balanced Scorecard, and it looks OK. Its got a graph here and a table there. It covers the basics that everyone says you should have, and you’ve put on your company branding to make it look official. But somehow it’s not enough. You want to make it interactive!


This spreadsheet helps you to show a Gantt chart on your dashboard. Because you’ll have limited space, it’s got scroll bars so that you can move around the chart to see whatever (or whenever) you want. Whatsmore, you can choose the scale of the chart, from day by day, to week or month of the year (not week or month into the project).

You enter the information about your project in the Data tab, and enter the letter R,A or G to indicate the status of each step (a RAG report, or Traffic Light report).

So without further ado, here it is:

HR Dashboard Gantt Chart / Traffic Light Report
HR Dashboard Gantt Chart / Traffic Light Report

All feedback welcomed; there’s always another way of doing things in Excel, so if you can see a better / simpler / easier way, leave me a comment!


UPDATE: This spreadsheet isĀ based around using the INDEX and MATCH functions together. If you’d like to learn how to do this, there is a walkthrough here that goes through it step by step in the context of calculating UK statutory redundancy repayments. Or, if you’re looking for something else for your HR Dashboard you may want to check out this Hierarchy Chart.

ANOTHER UPDATE: I got feedback that the date format used originally (dd-mmm-yy) caused problems for those using Excel in a language other than English. This was a bit short-sighted of me, so I decided to update accordingly – you can now adjust the date format yourself without editing any formulas.

AND ANOTHER UPDATE! : 1. Before if the month or week number went across the end of the year (past 12 or 52 respectively), the numbers would keep moving up and up, month 13, 14 etc etc. Now the numbers loop back to one. 2. Before if you wanted to add another column in the Data tab it would stop the Gantt chart working (the INDEX MATCH formula used a static column reference to find the start and finish dates and the status), this is now fixed (by using the COLUMNS function to make the reference dynamic). So now you can add as many columns as you like!

4 thoughts on “HR Dashboard Gantt Chart / Traffic Light Report”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>