Tag Archives: excel

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.

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!

Just for Fun… Roll the Dice

Picture this…

You’re settling down for a game of monopoly… everyone is ready around the board… you’ve exchanged banter and already started to forge alliances… and you realise you don’t have any dice! Somewhere along the last 20 years that you owned the board, the dice got separated from it, eaten by the dog maybe, who knows.

So what now?

Use Excel.

[showmyads]

Here it is, your dice-rolling spreadsheet. Just select from the dropdown how many dice you want to roll (up to 6 at a time) and press the button (make sure you have macros enabled first).

Roll the Dice
Roll the Dice

If you’re learning VBA, this is a relatively short piece of code for you to investigate. See if you can understand how it works, and think whether you would do it the same way. If not, let me know what your approach would be!

 
[showmyads]

UPDATE!:

We took some good advice and changed our VBA to avoid selecting cells. It simplifies things and with a larger file is more efficient – basically it’s the better approach. Also, if you turn your volume on that it now reads you the numbers as they come up and tells you the total at the end. And another thing, it has a Roll again? dialogue box at the end.

XL FORMULA FOCUS: SUM, AVERAGE, PRODUCT, POWER (an introduction to Excel math functions)

SUM AVERAGE PRODUCT & POWER by XLCalibre
SUM AVERAGE PRODUCT & POWER by XLCalibre

The picture above gives you a step by step encounter with 4 of Excel calculation functions. The numbers being used are in cells A1, B1 and C1. To make it easier to follow we’ve used the numbers 1, 2 and 3.

[showmyads]

The blue cells contain the names of the functions we are using. Below each blue box is what you need to enter in the Formula Bar to make things happen. The next line shows the formula using the actual numbers instead of cell references. The line below that is the calculation that’s taking place, and last of all is the answer produced.

Just for Fun… Countdown

Last week I wrote a London 2012 Olympics-themed post showing how to use Excel to flip a coin. Keeping in the Olympic spirit I thought I’d share a spreadsheet that helps you count down the number of days, hours, minutes and seconds between now and the opening ceremony.

[showmyads]

It uses a simple macro to calculate each second, and you can adapt it to any deadline date and time, and to show either just workdays or all days. Beyond that I’ll let you explore the tool yourself and understand how it works.

When you open it you’ll need to enable macros, and then click the Countdown button on the bottom right of the Display tab.

Enjoy!