All posts by xlc

300 Excel Examples

“Are you looking for Excel examples? Are you looking for clear explanations that help you master many more Excel features quickly and easily?”

That’s the question they ask on www.excel-easy.com – and they answer those questions with 300 examples. You can check out the full list here:

http://www.excel-easy.com/examples.html

More Essential Excel Keyboard Shortcut Combos

When you get used to using keyboard shortcuts with Excel, you start to realise that you use some shortcuts together all the time. We talked through my Top 5 Excel Keyboard Shortcut Combos before, but what other combos are there? What narrowly missed the (short)cut?

1. Change font type and font sizeAlt+HFF [type font name], Alt+HFS [type font size]

Sometimes I forget to paste formats, or notice that my worksheet looks messy due to different font types and font sizes that may have been pasted in over time. I often find myself using the shortcuts above (usually with something like Arial and then 10), to quickly tidy things up.

2. Ungroup all grouped rows and columnsCTRL+A, Alt+AJ

Every now and then I inherit a spreadsheet from someone else that has clearly had a lot of work go into it. They’ve spent a lot of time building something really in depth – so much so that they’ve made it more concise by grouping columns and rows together so you just see the key information. But I need to understand how it works, so I have to go through and unhide all of the grouped columns, group by group. This shortcut unhides all of these groups in one fell swoop (N.B. it’s not for unhiding hidden columns).

3.  See what part of a formula works out toF2, [optionally select part of the formula] F9, Escape

Big formulas can be hard to get your head around, and if they’re not working you need to break them down to see where the problem lies. F2 dives straight into the formula – you may then choose to highlight a particular element of it that may not be working. Pressing F9 will calculate just that one part of the formula. Often I will have each part calculated so I can see the big picture and check that my logic is correct. Pressing Escape is important as it means you exit the formula without changing it – otherwise it stays with all of its parts calculated and is no longer dynamic.

4. All borders, with a thick border around the outsideAlt+HBA, Alt+HBT

I think small tables of data look tidy with thin borders all over, with thick border around the outside. This combo does that really quickly.

5. Close a workbook and re-open itAlt+FC, Alt+FR1

Sometimes if I make a mistake and want to go back to the latest version quickly without saving, I’ll want to quickly close and re-open the workbook I’m in. Alt+FC closes the workbook (if you’ve made changes press N at this point to quickly tell Excel not to save them – be sure that you really want to do this), and Alt+FR1 opens the most recently opened file. Clearly if it was the second last workbook that you opened, you’ll need to press Alt+FR2.

So there you have it – five more easy but useful shortcut combos.

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.

[showmyads]

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!

[showmyads]

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” )

[showmyads]

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

[showmyads]

Calculate Blackjack Odds in Microsoft Excel
XLCalibre Blackjack in Excel

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

[showmyads]

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

[showmyads]

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:

Excel Alt Ribbon Keyboard Shortcuts
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 2010 Ribbon pressing Alt 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:

Alt HAL to Left Align in Excel
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.

[showmyads]

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

Excel Align Text Left, Centre, Right
Keyboard Shortcuts to Align Text Left, Centre, Right

Excel Align Text Top, Middle, Bottom

Keyboard Shortcuts to Align Text Top, Middle, Bottom

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

Excel Formulas to Round Numbers
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.

[showmyads]

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)

[showmyads]

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.

[showmyads]

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.

[showmyads]

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!