Back
Excel

Charlie’s List, or “214 Keyboard Shortcuts”

One of the program managers on my team (Charlie) has put together a list of 214 keyboard shortcuts available in Excel.  While it isn’t exhaustive (for example, it doesn’t cover shortcuts available in dialogs), I find it a useful presentation of the shortcuts that are available when performing basic editing in Excel, so I thought I would share it with you.  If you click the picture below, you will access a PDF file which you can download and print.  (The PDF was generated by a recent build of Microsoft Excel 2007).  The items in orange are new to Excel 2007 (there may be a few more coming too).


(Click to save)

The other thing I wanted to do today was to evangelize a few of my favourites, some of which are more esoteric than others.  Here are some pretty self-explanatory ones that come in pretty handy once you know them (mostly shortcuts to dialogs).  There are many others in this category in the list.

  • Ctrl + 1 – format cells dialog
  • Shift + F3 – Function Wizard
  • Ctrl + F3 – Define Names Dialog

Next, there are a lot of commands to do common things you do all the time in Excel.  Here are some I use frequently.

  • Ctrl + Page Up and Ctrl + Page Down – previous/next worksheet
  • Shift + Space – select row
  • Ctrl + Space – select column
  • Ctrl + 0 – hide column
  • Shift + Ctrl + 0 – show column
  • Ctrl + 9 – hide row
  • Shift + Ctrl + 9 – show row
  • Ctrl + ‘ – Copy above cell and edit
  • Alt + Down – Open drop-down (auto-complete, filter, pick from list, or validation)
  • Ctrl + / – Select Array
  • F9 – Calculate
  • Shift + F9 – Calculate sheet (in the active workbook)

Then, there are a whole group that relate to selection in Excel – selecting ranges, navigating selection, etc.  By way of background, Excel has a few selection “modes†which you can turn on and off.  “Extend selection modeâ€, when turned on, simply expands selection from the active cell when you move around using the mouse or keyboard.  “Add selection mode†adds to existing selection when you select cells (using either the mouse or toggling on “Extent selection modeâ€).  These are handy for keyboard-intensive users.  Here are some examples.

  • F8 – Extend selection mode
  • Shift F8 – Add selection mode
  • Shift + Backspace – Collapse selection to the active cell
  • Ctrl+ Backspace – Show active cell (particularly handy if you have a large range selected and the active cell is off the screen)
  • Ctrl + Alt + Left Arrow – Move active cell to previous non-adjacent area within selection (when you have discontinuous ranges selected)
  • Ctrl + Alt + Right Arrow – Move active cell to next non-adjacent area within selection (again, when you have discontinuous ranges selected)
  • Ctrl + . – rotates the active cell through the corners of a selection
  • Enter – move active cell down in a selection
  • Shift + Enter – move active cell up in a selection

On to the more esoteric items.  Here is one that is less-frequently-used since we added formula tooltips (the tooltips that show the arguments of a formula as you type), but it can still come in handy.

  • Shift + Ctrl + A – Insert arguments in formula

And here are a couple of my true favourites which allow you to quickly navigate the “calculation chain†in Excel.

  • Ctrl + [ – Select direct precedent cells 
  • Shift + Ctrl + [ - Select all precedent cells
  • Ctrl + ] – Select directly dependent cells
  • Shift + Ctrl + ] – Select all dependent cells

Finally, here are some that allow you to quickly select unequal values when working with rows of data.

  • Ctrl + – Select cells in row/col of selection unequal to active cell
  • Shift + Ctrl + – Select cells in other rows unequal to cells the row of the active cell

For folks that are looking for an even more exhaustive list, you might want to check out the Office Online list of Excel Keyboard Shortcuts, or a list maintained by David McRitchie.  There are lots of other lists available too; those are just two others that I am familiar with.

PS Improved the list based on some reader feedback