Category Archives: HR Dashboard

The Best Excel Gantt Chart Ever?

A while ago we did a post about Gantt chart / RAG report. It explored some ideas and functionality for using Excel to track project progress and visualize it.


I started using it for various projects, and making improvements bit by bit. Here are the key changes:

1. Dates entered on the same worksheet as the Gantt Chart (much more practical)
2. Action Categories are colour coded automatically (up to 10 categories)
3. Weeks and Months were shown as numbers and are now dates (easier to understand)
4. Now able to view by Quarter (a high-level overview)
5. Choose the month your year starts for Quarter view (most likely useful to change between calendar year and financial year)
6. Optionally display non-workdays in Date view (UK Bank Holidays are shown as an example)
7. Sorting and filtering now available

So without further ado, here it is – The Best Excel Gantt Chart Ever from XLCalibre.

Is there anything you would change?

XL FORMULA FOCUS: COUNTIF to Calculate Headcount


No HR Dashboard is complete without a headcount summary, and Excel has given us the COUNTIF function to make it easy!


It’s a pretty straightforward formula – this is how Excel describes it: “Counts the number of cells within a range that meet the given criteria”. So all you need is a range of cells and the value that you are looking for within that range. For example if you had a list of staff and their department, you could count how many are in your Finance team.

Here’s what it looks like:

COUNTIF ( range , criteria )

So if your table has a column with departments listed, that’s your range – let’s say it’s C2:C14. If you want to count how many of those say Finance, you can put “finance” as your criteria (don’t worry, it’s not case sensitive”). Your formula would look like this:

COUNTIF ( C2:C14, “finance” )

If you’re using this formula for a headcount summary on a dashboard, you’ll want to show more than one department, in a table something like this:

If that’s the case you won’t want to keep changing the criteria part of the formula in each cell, like this:

COUNTIF ( C2:C14, “finance” )

COUNTIF ( C2:C14, “HR” )

COUNTIF ( C2:C14, “IT” )

COUNTIF ( C2:C14, “Marketing” )

COUNTIF ( C2:C14, “Sales” )


Instead, you can just reference the cell to the left, in the Department column, and make the range part of the formula a static range by adding $ signs (you can just press F4 as a shortcut) like this:

COUNTIF ( $C$2:$C$14, E2)

Note that the we haven’t made the criteria reference static as we want this to follow down column by column to change the department.

Here’s what it would look like:

Of course on this small table you can quickly count things yourself, but when you have a table with dozens or even hundreds of rows the COUNTIF function can save you an awful lot of time.

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.

HR Dashboard Tip: Copy & paste chart formatting in Excel 2007

Sometimes you want to show several graphs with the same formatting or brand colours in a single HR management dashboard. After fiddling around selecting the exact design for your first chart you realize you have to remember it all and click through all the same options again. But there is another way!
First you need to click on your first, formatted graph and press CTRL+C to copy it. Then select the new, unformatted graph and press Alt H,V,S,T and then Enter. It may seem a bit long for a shortcut, but if you deal with dashboards regularly it can save a lot of time and ensure that your presentation is consistent. If you really can’t remember the shortcut keys, on the left side of the Home tab you’ll find the Paste dropdown. Select paste special and then formats. You’ll still save time.