Tag Archives: balanced scorecard

HR Dashboard Interactive Employee Relations Summary

On an HR Dashboard or Balanced Scorecard it can be useful to show a summary of recent employee relations case work, things like disciplinaries, grievances, long term sickness, redundancies, maternity leave etc. But often a manager only wants to see information for a specific department. If they had all the data that would filter or sort it to their heart’s content, but on an HR dashboard this is just one of many summaries that you are delivering in a small space. So you want to give them a way of selecting the department themselves and seeing only the relevant data, without needing to look in detail at the source data.


Here are two spreadsheets to help:

XLCalibre HR Dashboard Employee Relations Source
Employee Relations data source
XLCalibre HR Dashboard Employee Relations Summary
Employee Relations summary

How to get set up:

1. Save both files onto your hard drive or network

2. Open the file called XLCalibre HR Dashboard Employee Relations Summary.

3. Enable macros where prompted.

4. Use the Browse button to select the file called XLCalibre HR Dashboard Employee Relations Source.


Now you’re all set up. Select cell A1 and type a department name (try something like Sales, Finance, IT, Marketing, R&D) – note that it is case sensitive. The list of employee relations cases will change.

And there you have it, an interactive summary for your HR Dashboard. Clearly this could be adapted for any number of uses in HR (reasons for absense, roles recruited, new starters or leavers etc) or other areas like sales (new orders by client, new clients by business development manager etc).

Let me know what you think, it’s great to have feedback. And if you like HR Dashboards, we have other HR Dashboard posts here!

Or, if you’d like to see some wacky macros check out the Just for Fun section!

HR Dashboard Organisation Heirarchy Pyramid Chart

This chart will quickly become a regular feature on your HR dashboard or Balanced Scorecard:


The pyramid shape is useful to illustrate the relative numbers of employees at each level in your organisation. I’ve seen HR Manager’s use it before to compare different departments and come up with organisational development plans.

So here it is:

HR Dashboard Organisation Heirarchy Pyramid Chart

I’ve seen colleagues struggle to format the chart to look like this, and I had previously used helper cells to include formulas to get the ranges for the chart right. However this version relies upon named ranges to do the work (using OFFSET to find the size of your table), so you only have to look at the real data, and its got conditional formatting to keep the table formatted in our snazzy XLCalibre colours. Formatting the data as a table (which was my initial intention) would make things expand automatically and keep the colours, but it kept throwing up error messages so I went with the OFFSET approach instead.


If you’re looking for more to put on your HR Dashboard you may want to check out this Gantt Chart / Traffic Light report.

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!

HR Dashboard Tip: Dealing with Charts, Pictures & Shapes in Excel


A good HR dashboard or Balanced Scorecard is clear and well presented conveying a snapshot of key data quickly. Charts are one way of doing this, and you may want to use pictures or objects to be consistent with corporate branding. And if you’re using lots of pictures, charts or other objects in Excel (and especially if you’re hiding them or they’re overlapping) things can get a bit fiddly.

Handling Lots of Objects
That’s why Excel have provided us with the Selection and Visibility pane. It lists each of these objects and allows you to quickly select them or hide / unhide them. It also works for grouped objects…


Grouping Objects
Say you had several objects that work together – a chart, surrounded by a background picture, with an explanatory text box and topped off with a word art title. Once they’re all positioned correctly in relation to each other, you can group them so that are easy to move around all at the same time. To do this, select the first one with your mouse, then keep holding down control and select all the other objects that you want to group together. Once you’ve selected them all you can let go of CTRL. Right click one of your selected objects and select Group then Group again.


A Handy Shortcut…
Remember that Selection and Visibility box we mentioned earlier that you can use to handle lots of pictures? To bring it up quickly the shortcut is Alt PAP. How do I remember this? Who takes lots of pictures? Paparazzi – also known as PAPs! So if you need pictures use Alt PAP (there’s plenty more Keyboard Shortcuts where that came from…).

Hopefully these tips will help save you time, but don’t forget you can get more tips on this site, or by liking XLCalibre on Facebook or following @XLCalibre on Twitter.