The Excel shortcuts you really need

Jump on the internet and start looking for Excel shortcuts and you'll start finding hundreds. That could be a little bit overwhelming: it's going to take ages to learn them all. Here's some good news though. You don't need to know hundreds. You can do most of your modelling and look like a shorcut ace with just a few dozen max (if nothing else you need to know about the power of the mighty "Alt" key).

Why shortcuts are great

Apart from making you look like an Excel shortcut wizard, here's a reason why you should bother learning a few shortcuts. They make your work easier. When you get to the stage of having automated a few things in Excel you free up a bit of brain-space to think about something more interesting (like where your model's going next, or what you're going to have for lunch). Shortcuts make your work easier and more satisfying and stop you having to think about where your mouse pointer is.

How to be a shortcut ace

Here's a relatively succinct list of Excel shortcuts that was pinned up on someone's desk here. It's a good list but what we did with that is go through it together to work out which ones we really make the most use of. What we're left with is a list of the Excel shorcuts we think you need to know if you want to make rapid process with automating some of your routine work in Excel.

The basics

Do I need to mention these? Most Excel users would stumble across these ones pretty quickly. They're here for completeness really.

Copy, cut, paste shortcuts:

• Ctrl C = copy
• Ctrl V = paste
• Ctrl Alt V = paste special (can be machine or keyboard specific)
• Ctrl X = cut.

Undo, redo:

• Ctrl Z = undo
• Ctrl Y = redo.

Here are a couple of formatting shortcuts not everyone knows about and some find useful:

• "Ctrl Shift 5" = format a number as a percentage
• "Ctrl Shift 1" = format a number?with Excel's general formatting.

Some?other basic shortcuts:

• "Ctrl S" to save. Get into the habit of pressing it every few minutes after you've completed a short block of work. Don't rely on Excel's autosave feature I would say - because it's not wired up to where your brain is at. Pressing "Ctrl S" after a few minutes when you've finished a block of work will, I reckon, subconsciously 'tell' you where to go back to when you get a machine crash. It makes it easier to pick up again rather than relying on the specific time interval that autosave works to
• "F12" for file save-as
• "Ctrl P" to print.

The mighty "Alt" key

With most regular shortcuts like the ones above you have to press all they keys one after the other but keeping everying pressed down. So for example with "Ctrl C" you press "Ctrl" and, keeping that key depressed then press "C" at the same time.

With Excel open now, just try pressing "Alt" and lift your finger off. A whole world is opening up before you and you're discovering how people who look like shortcut aces do it. Probably the biggest thing they know about shortcut-wise is the almighty "Alt" key.

Notice the little letters above. If you spend a lot of your time on the Home tab ("H") you could pretty quickly get into the habit of pressing "Alt" then (one at a time now folks) "H". Unlike other shortcuts, you don't want to keep all the buttons pressed down with these ones. What you have to do is press "Alt" then lift off. Then "H".

Getting going with the "Alt" key

Do you see how this could work? The first few times you have to look where you're going but if you spend a lot of time creating borders soon you'll find that you're in the habit of pressing "Alt" then "H" then "B" for borders and looking on your screen from there. That's how you learn on these ones. Take the first stept with "Alt" and look from there but soon you'll find yourself memorising all sorts of regular combinations such as:

• "Alt" "H" "0" to incrase the decimal
• "Alt" "A" "R" to align right.

Just by knowing about the "Alt" key you can start acting like a shortcut maestro.

Basic cell selection

Another top tip for becoming a keyboard basher rather than a mouse-reacher is to start using your keyboard for simple navigation. These shortcuts are easy to master and will have you whacking your keys in no time:

• Use your up down left right keyboard arrows to move around
• Keep "Shift" pressed down and try your arrows to select cells
• "Ctrl arrows" to jump to the end of a block of text (try it)
• "Ctrl A" to select a block of data. Press it once more and you will have selected the whole tab
• "Ctrl Home" to get to the top left hand corner of your spreadsheet.

Insert or delete a column or row

While we're on the theme of selecting cells:

• "Shift Spacebar" to select a row
• "Ctrl Spacebar" to select a column.

Now try selecting a row or a column and press "Ctrl" and the "minus" (i.e. the "-") key at the same time. You will have deleted a row or a column.

You can insert a row or column too. The shortcut is similar to "Ctrl -" for delete and the logic is there, but it does vary keyboard to keyboard and machine to machine. It's essentially "Ctrl -" for delete and "Ctrl +" for insert but to get to the "+" key on most machines you have to press "Shift" first. So to add or remove a column or row, select the row or column first then:

• "Ctrl -" to delete a row or a column
• "Ctrl Shift +" to insert a row or a column.

People have told me that these shortcuts have changed their lives!

Fill right, fill down

Here's two more shortcuts that other people have told me have been life changing (like me they may have small lives that are often lived inside Excel):

• "Ctrl R" to fill to the right. Select a cell that has a value in it. Then "Shift [right arrow]" to extend that selection out to the right. Then "Ctrl R" and you will have filled the value out to the right. I use that one all day every day and, along with "Ctrl Spacebar" followed by "Ctrl Shift +" to insert a row, I wouldn't want to be without the ability to fill right
• "Ctrl D" is the shortcut to fill down.

I know?someone's thinking there should be a "Ctrl L(eft)" and a "Ctrl U(p)" but you won't find any joy there. Just be happy?you have "Ctrl R" and "Ctrl D"!

Shortcuts for working with formulas

Here are a few essential shortcuts that help you work with formulas:

• "F2" to enter and edit the active cell, followed by "Escape" or "Enter" to exit
• When you're editing a formula ("F2") and you're inside a cell press "F4" to add the $ signs for cell locking or "absolute referencing" in Excel speak. Press "F4" again and you'll find you cycle through the different row and column locking options before you're back at the beginning
• "Alt =" as the shortcut for creating a sum formula. Position your cursor at the bottom of a list of data (using your arrows now please) and press "Alt =". That will create a sum formula as quick as a flash.

Shortcuts to navigate around tabs and applications

These shortcuts help you move around and between spreadsheets:

• "Ctrl Pagedown" to cycle?to the next tab (doesn't work on all machines - sometimes you need "Ctrl Fn Pagedown")
• "Ctrl Pageup" to cycle to the previous tab
• "Shift F11" to insert a new tab
• "Alt Tab" to switch application
• "Alt F4" to close application
• The windows key and your down arrow (at the same time) to restore your application down
• The windows key and your left or right arrow to get your application showing in one half of the screen.

Formula auditing

Remember the mighty "Alt" key? That was the one that turned all your menu items into letters and allowed you to navigate the whole of Excel from your keyboard looking like a shortcut maestro.

"Alt" has something to offer us when you want to use Excel's formula auditing to trace cell references. Remember when using "Alt" you need to press keys one at time not all at the same time:

• "Alt" "M" "D" to trace dependents (the cells that depend on the formula you are looking at)
• "Alt" "M" "P" to trace precedents (the cells that lead into the formula you are looking at)
• "Alt" "M" "A" "A" to remove formula auditing arrows.

Here are a couple of other handy tracing shortcuts:

• "Ctrl [" (that's "Ctrl" together with the left square bracket) to jump to the first cell reference in the formula. That one usually works and could probably qualify as a shortcut on the "Alt" "M" "D" shortcut!
• "F5" then (don't look at what pops up on the screen, it's boring) followed by "Enter", without thinking about it too much, will get you back to where you were in your spreadsheet previously. Note with this one you press "F5" and "Enter" one after the other - not at the same time.

These two shortcuts are really handy for finding your way around a model. You could spend half your day (and I often do) pressing "Ctrl [" to trace something back followed by "F5" and "Enter"!

The opposite mouse button

If you're a fan of using your opposite mouse button you might like this one. The opposite mouse button allows you to access great sample of Excel's best features. On most keyboards you can access those same features using "Shift F10".

Then the underlined hot keys tell you where to go next. So what I'd often do to hide columns is select the columns ("Ctrl Spacebar" then "Ctrl Shift Right-arrow" to go out to the far right). Then "Shift F10" and then the underlined hot key "H".

Finding and replacing

Perhaps from time to time you use Excel's 'replace' feature. "Ctrl F" followed by "Ctrl Shift Tab" will get that up for you.

Working with dialog boxes

With Excel open press "Ctrl 1". That will bring up the format cells dialog box.

If you want to use shortcuts for navigating dialog boxes like this here's how:

• "Tab" to cycle down the various elements of the dialog box (hit "Enter" when you find something interesting)
• "Shift Tab" to cyle back up
• "Ctrl Tab" to cycle to the next page or tab within the dialog box
• "Ctrl Shift Tab" (are you spotting the logic?) to cycle back up.

You can get there on shortcuts

I know it's hard getting up the curve on shortcuts but it's not as hard as you first think. You don't have to know hundreds. I know the list above is reasonably deep but it's still just a few 10s and you could do the vast majority of your modelling just with those.

Do you have a?favourite shortcut?

Even without trying to learn the whole list, you could probably make your life easier just by adding a few extras. What's your favourite? Personally I wouldn't want to be without:

• "Ctrl S" - it has saved my life
• "Ctrl Shift Right-arrow" followed by "Ctrl R" to fill right - I use it all day
• "Ctrl Spacebar" followed by "Ctrl Shift +" to insert a row - allows me to insert without thinking about it
• "Alt" - turns me and anyone else into a shortcut king.

Mastering Excel financial modelling with just 10 formulas

This post is part of my "Excel Top 10 formulas" series. Is it possible to master Excel modelling in as few as 10 formulas? Maybe. Almost.