# Let your spreadsheet do the talking!

Spreadsheets speak to me. It’s true. Every now and then I hear a voice from Excel. But it’s ok, it’s not all in my head, I made it speak with a very simple macro.

Here’s the spreadsheet.

And this is the simple macro that makes it work:

Sub Talknow()

Range(“a1″).Speak

End Sub

If you want to see a more developed talking spreadsheet then check out this post.

# 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.

# Just for Fun… Blackjack / 21 Probability in Excel

Using Microsoft Excel to predict 21 (Blackjack) Odds

This spreadsheet uses macros and formulas to predict odds in the card game Blackjack (aka 21). It deals you random cards one at a time and shows you the probability of your hand value if you hit again.

XLCalibre Blackjack in Excel

Have a look and let me know what you think (don’t forget to enable macros).

By the way, if you like this you probably want to check out our Excel slide puzzle game or some of our other Just for Fun posts

# Text Alignment Keyboard Shortcuts

We’re going to show you how to quickly use keyboard shortcuts to align text both vertically and horizontally within cells in Microsoft Excel (the screenshots used are in Excel 2010, but the relevant sections of the Ribbon and how keyboard shortcuts work with them are exactly the same as in Excel 2007).

You may have noticed that if you press the Alt key (Alt stands for Alternate) lots of letters appear over the Ribbon (the standard toolbar at the top of Excel). Here’s what it looks like:

What the Excel 2010 Ribbon looks like when you press the Alt key

Our text alignment shortcuts are in the Home tab of the Ribbon, so after pressing Alt we press H:

Excel Ribbon: Press Alt then H

Once you’ve pressed H you’ll see a whole new batch of letters appear above parts of the Home tab (if you weren’t on the Home tab to start with it will appear). In this example we’re going to Left Align, so you’ll now need to press AL:

Press Alt HAL to left align

And that’s it! You’ve learned to left align by pressing Alt HAL.

All the other text alignment shortcuts follow the same pattern – they start Alt HA and then the letter for where you want to align.

I will leave you with a quick summary of all of the basic alignment keyboard shortcuts, may they serve you well:

Keyboard Shortcuts to Align Text Left, Centre, Right

Keyboard Shortcuts to Align Text Top, Middle, Bottom

# XL FORMULA FOCUS: ROUND, ROUNDUP, ROUNDDOWN, MROUND (an introduction to Excel rounding functions)

ROUND, ROUNDUP, ROUNDDOWN, MROUND – Excel Formulas to Round Numbers

Here is a quick introduction to four straight-forward rounding functions in Excel. We’ll look at each formula in the picture above and how they help you to round numbers.

ROUND(number, num_digits)

This is probably what you think of instinctively when you hear the word “rounding”, and so this might be the formula you use most often. In the example above we’re rounding to two decimal places, so if the third decimal place is 5 or above we round the second decimal place up, and if the third decimal place is below 5 we round the second decimal place down. If num_digits is 0 then it rounds to the nearest integer, and if num_digits is negative the number is rounded to the left of the decimal point.

ROUNDUP(number, num_digits)

You’ll start to see a theme emerging here – all of these rounding functions follow the same syntax (number, num-digits). So if you understand ROUND then ROUNDUP should be fairly intuitive for you. But just to spell it out, in the example above we’re rounding to two decimal places, so if the third decimal place is above 0 it rounds the second decimal place up, otherwise it stays the same.

ROUNDDOWN(number, num_digits)

Again the same format, only this time we’re rounding down. In the example above we’re rounding to two decimal places, so regardless of what the third decimal place is, we’re chopping it off and the second decimal place stays as it is.

MROUND(number, num_digits)

MROUND, or Multiple Round, rounds your number to the nearest multiple of your choice (guess what – same syntax!). So above we decide to round to the nearest 5, and it uses “standard” rounding and returns the figure up or down to the nearest multiple of 5.

That was your whistle-stop tour of Excel’s most straightforward rounding functions. Thanks for listening!

# Excel Employee Timesheet – from DataScopic.net

Thank you to XLCalibre for the generous opportunity to Guest Blog. I like what’s happening on this website with the interesting real world questions, challenges and solutions. Very different from my site www.DataScopic.net where I focus on concepts and simpler tips.

Today, I’m going to share a recent challenge.

Problem:

Client is a small business and the Office Manager is taking a lot of time processing payroll. She receives papers from the employees … a lot of the writing is illegible. Their math doesn’t add up. She has to hunt them down to get details. It was a long, painful process.

Request:

A timesheet that will have restrictions, required fields and is easy to import into Access.

Result:

Payroll processing dropped from 6 hours to 90 easy minutes.

Specific Need in the document: Detect Overlapping Times

Let’s say

• Line 3 says that I was on break from noon to 1PM, and
• Line 6 says that I was working with Dr. Phibes from 12:45PM to 3PM

OOPS! The spreadsheet needs to point this out before it’s submitted to the Office Manager.

Let’s dig into the solution

In building features like this, it helps to

• Anticipate problems, exceptions, pitfalls, etc. from the user’s perspective.
• Anticipate problems, exceptions, pitfalls, etc in the solution I create.
• See if there are already solutions in the world

Answers

• Anticipation of needs
• If a user keys in an overlapping time, how will they know?
• How will they know not only that the new time overlaps but which previous entry does it overlap with?
• If a user forgot to enter something that happened earlier in the day, can they add it to the bottom of the timesheet or must it be entered in proper sequence?
• If the user has to put the late entry in sequence, and move a lot of data out of the way, they are going to hate my guts!
• Anticipate problems, exceptions, pitfalls, etc in the solution I create.
• VBA Code? I minimize use of VBA code because a lot of people still don’t know VBA exists or they don’t understand it or they’re scared of it. So, in the event that I am hit by a bus or retire and join a rock band, it should be easy to find someone to troubleshoot a non-VBA solution.
• Did I find existing solutions?
• I found solutions that would detect overlapping times only if they’re entered sequentially. That’s the Hate Oz’s Guts Solution

Key Detail:

The timesheet has room for 45 entries. Thus, I had to generate a solution such that every line is checking itself against the 44 other lines.

First, use columns Q and R as helper columns

NOTE: troubleshooting this formula lead me to using Word because there were so many pieces to manage. See my blog entry on using Word to troubleshoot a formula.

=IF(O7=”",”",IF(AND(OR(R7<=Q8,Q7>=R8),OR(R7<=Q9,Q7>=R9),OR(Q7>=R10,R7

What does the formula say?

1. Formula trigger: IF O7 is empty, don’t do anything
2. Test: Comparing line 3 against line 1

Is the Start Time in Line 3 Greater than the End Time on Line 1? 6:01PM is greater than 5:19PM YES

Is the End Time on Line 3 greater than the Start Time on Line 1? 6:06PM is greater than 5:08PM YES

1. If all is well, “EXCELLENT!” If not, “NOPE”

There are 4 possibilities:

 NO OVERLAP OVERLAP YES YES NO YES NO NO YES NO

Once the formula is created, for the first entry, just copy it on down the column

Notice: entries 3 and 5 overlap

There is still one problem.

Remember that every line has to check itself against every other line?

When you copy the formula down the column, Entry 2 isn’t checking itself against Entry 1. Entry 3 isn’t checking itself against 1 and 2, so on sand so on until Entry 45 isn’t checking itself against anything.

How do we modify this formula? WE DON’T!

This is already complicated enough. Let’s just copy the times down such that

Q57 = Q7 since Q7 is the Start Time of the first entry. Thus, Entry #2 is checking itself against Entry #1 via Q57:R57

There it is! The client is very happy with the document and other features within, and they’ve got a lot more time to focus on their core business.

If you have other solutions, please do share.

A big thank you to Datascopic.net for agreeing to guest blog on XLCalibre.com. We hope you enjoyed it!

# 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:

Employee Relations data source

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:

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

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!