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
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:
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.
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:
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!
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…
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…).
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.