Ditch the mouse

  • Tagged with:
  • Design,
  • Excel,
  • Shortcuts,

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.

5 Comments so far Join the Discussion

  1. Jon Peltier

    20th November 2009 at about 1pm

    Guess I’m just a novice :)

  2. Justin

    20th November 2009 at about 2pm

    Alt ‘ in Excel 2003 to change styles. This shortcut is sorely missed in 2007/2010, and I find myself using Ctrl 1 more often than I should to change the style to Percent, Currency, etc because it’s faster than Alt + H + J + arrow keys to desired style.
    F2 of course although I find myself hitting F1 5% of the time. Nothing a little VBA can’t fix!

  3. Justin

    20th November 2009 at about 2pm

    P.S. I dig the new blog/website design!

  4. Divya Gaur

    21st January 2010 at about 10pm

    I can share few of them which I personally use -

    For Tracing / Auditing-
    1)Trace Arrows Alt T U, followed by:
    For Precedents T
    For Dependants D
    For Erase Arrows A
    For Highlight Preced. F2

    2)Row Difference Ctrl + \ , then shade

    3)Quick Graph Select range, F11

    For Moving Around-
    1)Up / Down Sheets PgUp / PgDn

    2)Between Sheets Ctrl + PgUp / PgDn

    For Editing-
    1)Copy Ctrl + C

    2)Paste Enter or Ctrl + V

    3)Paste Special Alt E S, followed by:
    For Formats T
    For Formulas F
    For Values V

    4)Undo Ctrl + Z

    5)Repeat F4 or Ctrl + Y

    6)Sum above Alt + =

    Regards,
    Divya Gaur

  5. Darren Miller

    22nd January 2010 at about 4pm

    Thanks for sharing these shortcuts Divya. I didn’t know of the Ctrl+/ shortcut for row differences – great tip, as checking rows for differences is something I do a lot of. It made me wonder whether there is a similar shortcut for finding column differences. Searching the interweb did not reveal anything, so I played around in Excel and discovered that Ctrl+| does this – so now I can check my rows AND columns for consistency.

    On another note, it is funny how you get used to doing things a certain way. I always define new range names via Alt I N D (4 separate keys) whereas Ctrl F3 does the same thing in 2 (in combo). However, I find myself doing this the long way round and wasting valuable milliseconds.

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.