If you jump on the internet and start looking for Excel keyboard shortcuts you’ll find out pretty quickly that there are hundreds. That can be slightly intimidating because you’re likely to start thinking that it’s going to take a massive rote-learning exercise to get on top of them all. Learning the best Excel modelling shortcuts could really make your brain hurt.
You only need a handful of Excel keyboard shortcuts
We think we’ve got some good news for you though. We think we can get you most of the way there on Excel shortcuts with a few tens, not hundreds. You don’t need a brain the size of a planet to get on top of shortcuts.
So most of today’s challenge is just to list out the shortcuts that we use most often on our courses. The ones that get us most of the way there most of the time. The ones that will get your fingers dancing and smoke rising from your keyboard in no time at all.
Mouse vs. keyboard shortcuts: can the mouse win?
At the end we also take a look at a few advanced mouse movements that the shortcut aficionados are going to struggle to repeat efficiently using the keyboard. You’re taking a risk if you show them this stuff though. They won’t like it if you suggest that the mouse could ever be better than keyboard shortcuts. You’re likely to get a reaction. The same kind of reaction you’d get if you walked into a Scottish transport cafe and ordered a full English breakfast.
The best Excel keyboard shortcuts
But before we look at the extra hidden abilities the mouse has, let’s rattle through the keyboard shortcuts that we can quickly and easily employ to maximum effect.
Navigating and moving around your spreadsheet
You can avoid stretching for the mouse a lot of the time if you just use your keyboard arrows to move around and select cells. Just using your keyboard arrows instead of reaching for the mouse will get you a good chunk of the way there to start.
If you want to practise this yourself, open an Excel spreadsheet and create a small block of random data (e.g. four rows deep by ten columns wide). Position your mouse in the top left hand corner of your new data set each time and practise these ace moves:
- “Ctrl →” Jump to the far right hand side of the data set. This can be really handy when you’re working with a model that’s a zillion columns wide;
- “Ctrl shift →” Select to the end of the data set. Again this is super handy if you’ve started in the left hand corner of a big model and you want to select out to the far right hand side.
(We are not giving away any prizes to the person who works out that the same moves will work with your other ←, ↑, ↓ arrows as well. So by now you should be skipping around inside that data set.)
Now that you’re using your arrows to move around and select rather than using your mouse, and now that you’re bashing your keyboard furiously, you’re already starting to look like more of a pro. Here are a few other shortcuts that will help you move around your sheet and select data:
- “Ctrl A” for selecting a block of data. Position yourself (using your arrows!) inside e.g a 4x10 data set. Press “Ctrl A” once and you will have selected the block of data. Press “Ctrl A” again and you’ll select the whole sheet;
- Press “Ctrl Home” to get to cell A1. Press these keys at the same time and you’ll get yourself straight to the top left hand corner of your spreadsheet.
Filling right: “Ctrl R”
We’re guessing you mastered “Ctrl shift →” so go to the top left hand corner of a dummy data set. From that top corner press “Ctrl shift →”. Now press “Ctrl R”. The top left hand piece of data will be filled (copied and pasted) all the way across. We love “Ctrl R”! We use it all day every day and it’s probably our favourite and most used shortcut. It’s the shortcut we’d least like to be without.
You may be glad to know there’s a “Ctrl D” for filling down. You can practise that one now as well. Unfortunately as yet there’s no “Ctrl L” or “Ctrl U” so if you try those you’re destined for disappointment. “Ctrl R” for the right fill and “Ctrl D” for filling down are fabulous though. We use them all the time and if you haven’t used them before we may just have changed your life right there. They’re the best!
Selecting a row and a column
So often in Excel we want to select a row or a column e.g. ahead of inserting or deleting. Try:
- “Shift spacebar” to select a row. Then press “Shift ↓” to select a few more rows;
- Click in a cell (or use your arrows!) and press “Ctrl spacebar” to select a column. Then press “Shift →” to select a few more columns out to the right.
Inserting or deleting a row or column
Once you’ve selected a row or column try:
- “Ctrl -” (that is Ctrl and the minus key at the same time) to delete a row or column;
- “Ctrl shift +” to insert a row or column. Although this shortcut seems a bit different than the first (with “shift” in there) it kind-of makes sense when you start to think about it. On most keyboards you have to press “shift” to access the “+” key. So whereas deleting is “Ctrl -” inserting is really “Ctrl” with “+”. Because you need to press the “shift” key to access “+” the shortcuts are similar and inserting becomes “Ctrl shift +”. There is some logic!
“Ctrl -” to delete a row or column in Excel
“Ctrl shift +” to insert a row or column
Now that you’re using your keyboard to select, fill, insert and delete (which is a lot of what you do all day in Excel) you’re already starting to feel like a shortcut champion. Next up though we’ve got probably the most powerful shortcut of all for you…
The pathway to shortcut Nirvana: the “Alt” key
If you haven’t worked much with shortcuts before you’ve probably been a little bit impressed by other modellers who can hammer away at the keyboard bashing keys happily. Although they look like real shortcut experts what you may not have realised is that:
- Most of the time they’re probably just using their arrows and “shift arrow” to move around and select;
- Much of the rest of the time they’re probably just hitting the “Alt” key and looking at the screen to see what happens.
Try it for yourself (with Excel open of course!). Hit the “Alt” key and some letters appear at the top of your Excel menu items. If you hit one of those letters (“H” will take you to the home tab) you can follow your nose from there. Knowing the “Alt” key is going to turn you into a shortcut genius straight away!
Once you get used to pressing “Alt” you’ll quickly learn that “H” takes you to the home tab for example. So even if you’re not sure of where to go next, “Alt H” gets you part of the way and from there you can look on screen. Then you’ll eventually start to learn the keyboard combinations you use most often e.g.:
- “Alt” “H” “A” “R” for align right (note these keyboard combinations you have to press one at a time rather than all at once);
- “Alt” “H” “0” for adding an extra decimal place in your number formatting;
- “Alt” “H” “9” for reducing the decimal;
- “Alt” “H” “B” “O” for adding a bottom border.
And so it goes on. You don’t need to remember the above keyboard sequences. All you need to know is the “Alt” key and you can look on screen from there. But eventually, because you use particular keyboard combinations often, you’ll start to learn those by heart.
The very best keyboard shortcuts?
So there you have it: our all time favourite list of best-ever shortcuts – the few that will get you keyboard bashing quickly without using too much brain capacity:
- “Arrows” and “shift arrows” for moving around and selecting;
- “Ctrl R” to fill right and “Ctrl D” to fill down;
- “Shift spacebar” and “Ctrl spacebar” to select rows and columns;
- “Ctrl -” and “Ctrl shift +” to delete or insert;
- “Alt” to do absolutely anything else on your keyboard in Excel.
Becoming an Excel keyboard shortcut maestro
Of course there are lots of other shortcuts that are helpful as well but the list above contains some of our absolute all-time favourites – the ones we use all day every day for maximum impact. Here are a few more of our favourites but what we’re saying is still true. You don’t need to memorise hundreds of shortcuts. You can do most of your modelling with a few tens. Here are some more Excel keyboard shortcuts we like best and make the most use of. We never regularly use much more than these:
- “Ctrl S” for save. Press it every five minutes or so after you’ve completed a small block of work;
- “F12” for save as;
- “Ctrl O” to open a workbook;
- “Ctrl N” to create a brand new workbook;
- “Alt F4” to close application;
- “Alt Tab” to switch between open applications;
- “Windows key” at the same time as “→” or “←” to resize a window to the right or left halves of the screen;
- “Ctrl page-up” “Ctrl page-down” to cycle Excel tab;
- “Shift F11” to insert a new Excel tab;
- “Ctrl C” to copy followed by “Ctrl V” for paste or “Ctrl Alt V” for paste special. “Ctrl X” to cut;
- “Ctrl Z” for undo “Ctrl Y” for redo;
- “Ctrl B” for bold, “Ctrl I” italics, “Ctrl U” underline;
- “F2” to enter the active cell (you can use your arrows, ctrl arrows and shift arrows to move around inside the cell). Press “Escape” on your keyboard to exit the active cell;
- “F4” to insert the $ signs for cell locking (your cursor needs to be positioned next to a cell reference e.g. by pressing “F2” first);
- “Alt =” for autosum (your cursor needs to be under the block of data you want to add up);
- “Shift F3” to bring up the insert new formula dialog box;
- “Shift F10” (on many keyboards but not all) to get the right mouse click menu up;
- “Ctrl 1” to bring up the format cells dialog box;
- “Tab” and “Shift tab” to cycle up and down a dialog box. “Ctrl tab” and “Ctrl shift tab” to change tabs within a dialog box;
- “Alt ↓” to cycle through a drop down box;
- “Ctrl shift 5” (= the % key) to apply % number formatting, probably followed by “Alt” “H” “0” (one at a time) to add an extra decimal place;
- “F5” followed by “Enter” after you’ve used your formula auditing (“Alt” “M” “P” to trace precedents) allowing you to jump back and forward in your model;
- “F1” for Excel help;
- “F9” to force calculation (when calculation has been set to manual in Excel options “Alt” “F” “T”);
- “Ctrl P” for print;
- “Ctrl F” for find followed by “Ctrl shift tab” for replace.
Yes it’s true you don’t need to know hundreds of keyboard moves to master shortcuts – at most all you need is a few dozen (plus the mighty “Alt” key). Even the longer list above is still well under 30 and it’s enough to do the majority of your day to day modelling. Really it’s all we ever use.
Advanced power mouse moves
Could the mouse ever be better than keyboard shortcuts though? Please don’t go around suggesting to the shortcut fanatics that the mouse could sometimes be more efficient than keyboard shortcuts. They won’t like it. It’s against their religion. On the other hand, if you’re bored in the office one day and want to mess with the shortcut-crazed mouse-haters you’re surrounded by, show them this list and just enjoy the reaction. It could be reasonably entertaining:
- We call this one the ‘Ctrl drag’. Select a bock of data with your mouse. Position your mouse right at the edge of that data so that the cursor changes and start to drag the data (that’s one click). Now before you do anything else press “Ctrl” (that’s a second click) and you should notice a tiny little cross appear. Now having ‘Ctrl dragged’ your data to a new location, before you do anything else at all lift off your mouse i.e. release your mouse before you release “Ctrl”. That’s the fastest copy and paste known to mankind! It beats “Ctrl C” (two clicks) and “Ctrl V” (another two clicks) into a cocked hat! The mouse rules!
- The ‘Ctrl drag’ has a great application when it comes to copying tabs. Start using your mouse to drag a tab into a new location but before you finish press “Ctrl”. Lift off your mouse before lifting off “Ctrl”. You’ve discovered the quickest way to copy tabs. What was that? Oh yes: two clicks. If you’re using your mouse to work with tabs, positioning your mouse on top of a tab and right clicking is a really quick way to rename or hide a tab (with just one click folks);
- This one’s the ‘opposite mouse fill’. Like you would use your mouse to fill start filling but this time use your opposite (right) mouse button. It’s been one click. Lift off and you’ll have discovered you’ve got access to a couple of your favourite paste special options. The humble little mouse is winning on that one. The opposite mouse fill really does beat “Ctrl C” (two clicks) followed by “Ctrl alt V” for paste special (a further three clicks).
And yes, in case you’re wondering, you can do an opposite mouse ctrl drag as well (two clicks). These two above are both essentially the same as paste special values but with reduced clicks!
- The ‘Ctrl fill’. Type the number 1 in an Excel cell. Now start to do a regular mouse fill. If you press “Ctrl” before doing anything else you should start to realise Excel is giving you some choices about how you fill. By pressing “Ctrl” before you’ve finished your fill you can force Excel to escalate those numbers up for you, or not if you would prefer. It cost you a regular fill plus pressing “Ctrl” before you had finished. Just for fun try asking the shortcut guys to fill and escalate numbers up at the same time (that little mouse rocks).
The humble mouse vs. mighty Excel keyboard shortcuts
Now we’re not really sure it’s a good idea to pick a fight with your resident keyboard shortcut experts and go around suggesting that sometimes the mouse could be better. You’ll just wind them up. But whether you’re currently a shortcuts person or currently a mouse person we’re hoping you are appreciating that you can do most of your modelling with surprisingly few shortcuts. Honestly we do love (a few) shortcuts. Shortcuts can make your work in Excel that much more satisfying. Lewis Hamilton probably isn’t thinking about how he makes every gear change as he roars around the F1 track. He’s probably thinking about where he is in the race and how he’s going to get past the next car. In the same way learning shortcuts to help you automate any of what you regularly do in Excel is a good idea. Shortcuts don’t just speed up your work, they can make your work more satisfying. You have to think less about the routine stuff and you can think at a higher level while you’re modelling (my tummy’s rumbling, when’s lunch?).
But still, to those of us who have been around long enough to remember having to operate university computers when there really was no alternative to memorising keyboard combinations, the mouse will always remain a pretty brilliant invention!
About Financial Training Associates Ltd
At Financial Training Associates we like a few Excel keyboard shortcuts. That doesn’t make us mouse haters though.
You’re reading stories from the world of financial modelling.
Brought to you with love from our company: Financial Training Associates Ltd of London – purveyor of the very best Excel modelling courses (keyboard shortcuts included).