All posts by XLCalibre

Your First 10 Excel Keyboard Shortcuts (aka The Basics)

Never used a keyboard shortcut? Want to save time in Excel? Here’s where to start:

1. Ctrl+Z   Undo

2. Ctrl+Y   Redo

3. Ctrl+C   Copy

4. Ctrl+V   Paste

5. Ctrl+S   Save

[showmyads]

6. Ctrl+N   New Workbook

7. Ctrl+O   Open

8. Ctrl+F   Find

9. Ctrl+P   Print

10. Ctrl+A   Select All

 

Have you got those down now? Good work, you’re on your way. If you’re ready to learn some more, we’ve got plenty more!

Top 5 Excel Keyboard Shortcut Combos

After you start remembering your first few Excel keyboard shortcuts you notice how much time they save you, and also how you start to use some of them together very regularly. Once you’ve gotten used to using shortcuts this is the next step in speeding up your game. Here are our top 5 keyboard shortcuts that play well together:

1. Apply autofilters and freeze panes on top row CTRL+Shift+L,  Alt+WFF

Most times that you have a large array of data you will want to have autofilters and freeze panes. Therefore you may find that you use this alot. To get things just right, here’s a longer list of shortcuts to put in a realistic context…

CTRL+Home (go to cell A1)

CTRL+A (select all – ensure that all of your data is included in the autofilter)

CTRL+Shift+L (autofilters)

Down Arrow (gets you to the right row to apply freeze panes)

CTRL+Space (selects the row)

Alt+WFF (freeze panes)

CTRL+Home (takes you back to cell A1, ready to work on your data)

 

2. Unmerge all cells – CTRL+A,  Alt+HMC

Merged cells are bad. Ask anyone.

So once you know this you won’t use them ever again, so you won’t need to deal with them right? Well, unfortunately not everyone knows this fact, and so its quite likely that you’ll have to deal with other people’s merged cells. Or in my case, a work on various system-generated reports that contain merged cells. So I use a couple of quick shortcuts to remove them all.

CTRL+A (select all)

Sometimes this will only select the current region (the surrounding cells that are being used), which in many cases will be sufficient. But in my quest to eradicate merged cells from the world, I want to be extra sure. So I usually press CTRL+A twice to make sure the entire worksheet is selected.

Then I press Alt+HMC. This is a toggle, so if there are no merged cells at all in your worksheet, it will merge them all! Don’t worry though, if this is the case you’ll see this message:

If that happens just press cancel and be happy that you have a worksheet with no merged cells.

[showmyads]

 

3. Add a new worksheet and name it – Shift+F11,  Alt+HOR

Sheet4 is not the most helpful name. It could be anything. So when you need to add a new worksheet, this will save you time. Its that simple.

 

4. Close the current workbook and re-open itAlt+FC, Alt+F,1 (or in 2010 its Alt+FR1)

Why would I want to close the worksheet and reopen it? Its true, you may never need this. For me its useful for two reasons:

1. I’ve made some changes that I don’t want to keep, but I want to keep on working on the workbook. If this is the case, you’ll probably have to tell Excel that you don’t want to save changes after Alt+FC.

2. I’m working on a macro that is always on (using Worksheet_Calculate()). I will use these shortcuts to either open the workbook again and enable macros for testing (in this case I’ll start with CTRL+S to save the changes I’ve made), or to stop testing and open the workbook with macros disabled to make further changes.

5. Select column and remove/highlight duplicates CTRL+Space, Remove:Alt+AM or Highlight: Alt+HLHD

CTRL Space selects the whole column and then you can choose whether you want to remove or highlight the duplicates. If you want to do this in a row instead you should start with Shift+Space.

 

So there you have it, some quick keyboard combos. Clearly this is not an exhaustive list, and you may not use them all yourself depending on what you use Excel for.

If you like Excel tips and keyboard shortcuts, you may want to follow @XLCalibre on Twitter, Like our page on Facebook, or join our group on LinkedIn.

There is another post here with more shortcut combos.

And of course, we’ve got loads more keyboard shortcuts here…

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…

[showmyads]

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.

[showmyads]

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.

XL FORMULA FOCUS: VLOOKUP for currency conversion

VLOOKUP is an essential Excel tool. Even if it’s the first function you learn, don’t worry, it’s actually pretty straight forward, and is really useful.

The purpose of the VLOOKUP function is to find your chosen value in a table of data and tell you what the corresponding value is in another part of that table. If that doesn’t make sense, follow this example and you’ll see what it means and why many Excel users consider it one of the most useful functions in their arsenal.

Currency conversion is essential when calculating international reward programs, like bonuses or salary review. You’ll usually need to work from a budget in a single currency to show the overall cost. So we’re going to use currency conversion to demonstrate the VLOOKUP function.

Here’s our spreadsheet:

It’ll be easier for you to follow this if you have your own spreadsheet open to work through it with me. I’ve copied and pasted the latest exchange rates for pounds sterling (my local currency here in England) from Reuters’ website in columns A and B (but you’ll probably want to be consistent with the rates your finance department is using).

In the BLUE cell, D1, you enter your currency, in the format shown in the table. You may want to use data validation to put a dropdown for this, but how to do that will be the subject of another post. For our example, enter “USD” in this cell (don’t enter the quotation marks).

In the ORANGE, D3, cell enter an amount, let’s say 100. This is the number of United States Dollars (USD) that you will convert into pounds sterling.

In the GREEN cell, D5, we’re going to do our VLOOKUP. You start by typing in “=vlookup(” like I have here:

The grey box will appear below showing how this function works. You’ll notice the first thing it says is lookup_value. In this case we’re going to look for “USD” and return the conversion rate next to it. We’ve already entered this in cell D1, so we’ll just point our VLOOKUP to that, like so:

=vlookup(D1,

The comma after D1 is to show Excel that we’ve moved on to the next part of the function, which they described as table_array in their helpful grey box. This is our table of currencies and conversion rates, held in cells A1:B5. Note that you always need to make the first column of this array the place where your lookup values are. In this case, we’re looking for “USD”, and we start the range in column A. So we add this to our formula thus:

=vlookup(D1,A1:B5,

Again, a comma moves us on to the next part, col_index_number. We need to tell Excel how many columns across our table we’re going to look. There are two columns in our table, and we’re looking for the value (the currency conversion rate) in the second column, so we enter the number 2:

=vlookup(D1,A1:B5,2,

Again, remember the comma. This is the last part now. VLOOKUP gives you the option of whether you want to find an exact match for your lookup_value (in our case “USD”) or not. You will almost exclusively want to find an exact match (approximate matches might not always be what you would expect), so you need to enter FALSE here. Try not to worry about this part too much. For the time being always FALSE. As you develop your skills you may find a problem that requires TRUE, but this doesn’t happen so much in common uses an HR practitioner might have. So we end up with this:

=vlookup(D1,A1:B5,2,FALSE)

Notice that I’ve closed the brackets because this is the last part of the formula. I’ve also pressed enter, and you should too. So, what result does this formula give you? If you’ve followed everything so far, the answer should be 1.5875, the exchange rate for USD shown in our table. All you need to do now to make our example useful is multiply this by the value we entered in the orange cell, D3.

So select the green cell again, D5, and click at the end of your formula in the formula bar at the top of the screen. All we’re going to do is add “D3/” to the beginning of our formula, immediately after the =, meaning that we divide D3 by the result of our VLOOKUP. So your formula will now look like this:

=D3/VLOOKUP(D1,A1:B5,2,FALSE)

[showmyads]

The answer should now say 62.99 (and probably a fair few more decimal places), which is how many pounds you would get for $100. You can format this number to get rid of the extraneous decimal places – the shortcut for the formatting dialogue box is CTRL+1.

There is a copy of the finished spreadsheet here.

Was this helpful? Did it work for you? If you have any questions or comments, you can tweet @xlcalibre, join our group on LinkedIn, or contact us on Facebook or Google+.

 

XL FORMULA FOCUS: Find end of probation period using EDATE

So you’ve got a new employee, and you want to know when their probation period is over? The EDATE function is really straight forward.

=EDATE(Start_date,Months)

So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then close your brackets “)” and press enter.

Done.

RELATED: If you need to calculate length of service, you may want to look here.

Learning to Learn Excel

Where do I start?

If you’ve decided to learn Excel that’s probably what you’re asking yourself. In some ways Excel is very simple – its just a big grid on a screen right? But the more you use it the more you realise it can do. So here are some tips on how I approached learning Excel that I think worked out and might be helpful:

Use Excel

This one might seem obvious, but if you’re an Excel beginner you probably don’t use it all that much at the moment. So make excuses to use it. Taking notes in a meeting on Word? Use Excel instead. Doing some simple calculations on a calculator? See if you can do it on Excel. Making a shopping list? … … You get the idea.

You need to get comfortable with the interface. I’m guessing that a lot of people are put off Excel because of preconceptions – it’s too complicated, I’m not a computer person, I’m not good at maths (don’t worry it kind of does the maths for you). But if you get used to the basics of moving around in Excel, you won’t find it so intimidating, and that’s when you can start to…

Explore!

See that tab on the menu that you’ve never used? Curious? Well click it and have a look! If you see something and think “I don’t know how to do that”, well this is your chance to learn. See the button next to the formula bar at the top of the screen, it’s got “fx” on it? Click it and explore the functions that Excel offers. Pick one that you like and try to learn it. The application guides you through it. And if you don’t find what you need there…

Look online

Your favourite search engine will probably be able to find you the answer to your question. Excel has been around for a long time, and it has a really big following. If you’re patient and use online searches, you can do much more ambitious stuff than you might expect.

Keyboard Shortcuts

It’s not until you learn a few keyboard shortcuts that you learn how much more efficient they make you. To remember them it helps to focus on one or two at a time and use the frequently, then adding one here or there as you go. We keep a regular stream of tips on our Twitter and Facebook feeds, which can drip feed you new things to try out. If you don’t use it you’ll lose. But if you’d never use it what was the point in learning it in the first place? Which is why you should…

Be Selective

If you’re not an analyst that’s does statistic-type things, don’t learn the statistical functions! Be curious by all means but start by spending your time on the stuff that you will probably use. Autofilters, freeze panes, print areas – everyday things that will be useful. Whatever those things are for you, focus on them. Be patient, once you have the basics you’ll start to pick more difficult, obscure things much easier if you want to.

Network

Are there other people in your office that use Excel? They might be able to teach you some tricks or help out with a problem. Most Excel-type-people I’ve met are quite keen to share (I reckon this is because Excel’s versatility and power can excite people and they may not have anyone to share this with in person). Or you can network online. Our LinkedIn group is full or HR, reward and payroll professionals that use Microsoft Excel. If you have a question or want to network with like-minded people it’s a good place to be (of course I’m biased). And there are hundreds of other groups, forums and networks out there to choose from.

Persistence

There are two sides to this. One is a bit like Use Excel above – use it every day if you can, and try to repeat newly learned skills often until they’re second nature. The second side is to do with patience. That long formula you’ve built yourself up to might look impossible to finish, but its not. If you keep going with it and Look online and Network then you will come out of the other side a better Excel user.

Share Your Journey

So now that you’ve learned the ropes and know your array formulas from your VBA, what would you tell aspiring Excellers? Do you agree? Have we missed something? Or got it wrong? Share the Excel love by leaving a comment with some advice from your personal experience.

Splitting names

Today on LinkedIn somebody posed the following question (for anonymity I have changed any names given):

Sorting by Last Name

I have a list of 2,000 names by First, Middle, Last, Suffix (Jr or I,II, or III) I need to be able to Sort by last name. I know how to use “Text to Column” but unfortunately there is not a consistancy in the names, I have some that have just First and Last name, I have some that may have a space in their last name such as “De Souza” or “John Dave Jones Jr II” any suggestions.

The issue wasn’t really about sorting, which would be easy with autofilters (CTRL+Shift+L to apply autofilters). The problem was separating out the parts of the names, and lining them up in the right columns. Usually text to columns would fix this, but doing it with the different formats mentioned in the post would have left last names in various different columns.

I find this is a common predicament in HR, where lists of names pulled from various sources are full of inconsistencies, and therefore hard to manipulate or ‘plug in’ to other systems or spreadsheets.

My solution was not particularly elegant. You are rarely afforded the time to create the perfect single formula answer that covers all angles. But it does show you some text-manipulation functions at work.

I e-mailed the file to the poster. They mentioned 5 exceptions that it had not worked for, and they all included initials (I hadn’t thought of this possibility). This leads to my next point: testing. If at all possible you should use your spreadsheets from beginning to end with real data to check if they’re working before they go to your customer. Even better, get someone else to do this for you, as its easy to miss something when you’re buried in details.

Here you can access the spreadsheet that I created. See if you can understand how it works. How would you do it differently? Can you adjust it to work for with initials? Feel free to send your spreadsheets in to admin@xlcalibre.com.

If you’d be interested in similar discussions, come join the brand new XLCalibre Group on LinkedIn.

New XLCalibre Group on LinkedIn

If you an HR professional and use Excel and LinkedIn, come join our group on LinkedIn.

Its a forum for you to ask questions and share tips with other HR professionals on how you can best use Excel in your work. We’ll need to authorize your request to join, but only to minimize spam.

So come join the XLCalibre group and learn something new about Excel!