Ditch the mouse
One of the things that distinguishes a power Excel user from a novice (or even intermediate) user, is the ability to get things done without using the mouse.
There are a bunch of sites and blogs which show you all of the available keyboard shortcuts in your chosen version of Excel, but becoming a better Excel user is not simply a matter of memorising all the keyboard shortcuts — it’s as much about knowing which shortcuts are the most valuable (in terms of time-saving, efficiency, design and accuracy), and when to use them.
Once you know how certain keyboard actions behave it is easy to start to design your spreadsheets in a way that lets you take advantage of them. For example, you may know that holding down Ctrl and then hitting an arrow key on the keyboard will jump to the next “block” in the selected direction (see right). When you hold Ctrl and hit the down arrow key the selection moves to the end of the current block or the start of the next block — a block being a group of non-blank contiguous cells.
This technique can be used to give you a better way to navigate through a complex model. For example, if you have a model with rows in sections, sub-sections and sub-sub-sections, you might want to set up your row labels in various columns, rather than just having these in just one column. That way you can more easily navigate to the next section or sub-section. The example below demonstrates this. In the example on the left we’ve put all the row labels in column A, wheras in the example on the right we’ve split out the structure of our rows across columns A, B and C. You can see that starting at the Income Statement row it takes 6 steps to get to the Balance Sheet row (left), whereas it takes just 1 step to get there if our model is structured as per the right.
This is a very simple example, but if you’ve worked with large complex models before you’ll recognise how this could help you navigate up and down through the rows in a much easier way.
Some of my other favourite keyboard shortcuts (for actually getting things done) include:
- Shift arrows and Ctrl Shift arrows … to select multiple cells.
- Ctrl [ … to select the dependent cells of the active cell, then tab to toggle between them, and “F5 Enter” to return to where you came from.
- Ctrl ˜ … to toggle between normal view and formula view.
- F4 … to repeat the last action – especially helpful for repetitive formatting.
- F5 … goto and to access the goto special menu.
- Alt F11 … to access the VBA editor.
- Ctrl R and Ctrl D … to fill right and fill down the active cell – often much better than copy and paste.
- Ctrl . (Ctrl dot) … to toggle to the corners of a selected block of cells – click and drag to select a range of cells and then try this.
This list just scratches the surface, but for me they probably represent the most useful keyboard shortcuts, purely in terms of time-saving and efficiency. If you can master navigating around your worksheet using just the keyboard, you’re well on your way to becoming a power user.
What are your favourite keyboard shortcuts? We’d love to know.