The Six Most Useful Excel Keyboard Shortcuts

Need to work regularly with spreadsheets in Excel and find yourself constantly reaching for the mouse? A handful of common keyboard shortcuts can make you much more productive.

We recently wrote about End Mode, a relatively obscure Excel shortcut that makes selecting groups of cells easier. That's a good one to know, but there are basic keyboard shortcuts that make powering through Excel much, much simpler. Here are the six I think every Excel user should know about.

I've largely ignored general keyboard shortcuts that work in lots of programs and concentrated on Excel-specific options. I'm not saying that Ctrl-V or Ctrl-A aren't massively useful, but their application is a lot broader than just in Excel.

You can look up 'keyboard shortcuts' in Excel's help for an utterly complete list of options, but that can be overwhelming. These are options you'll use constantly.

The F2 key for editing

Simple but essential: F2 lets you edit the content of a cell. Sure, you can also double-click on it, but navigating there with the keyboard and using F2 is much faster.

Shift+the arrow keys for selection

A generalist tip but one lots of people don't seem to know. To select multiple cells, just hold down shift and move the arrow keys in the direction you want. You can also do the same with the PgUp, PgDn, Home and End keys. Much easier and more accurate than the mouse.

Selecting a whole row or column

Ctrl+Space selects an entire row. Shift+Space selects an entire column. You can then use the shift keys plus the arrow keys as appropriate to select additional rows or columns.

Accessing the Format Cells dialog

Ctrl+1 accesses Format Cells, which lets you change cell formats, fonts, alignments, shading and many other options. Pre-Ribbon, this was the only place to perform many of these tasks. You can now access many of them on the Home tab of the ribbon, but keyboard navigation in the original dialogue can be faster.

Changing the direction of data entry

Not a shortcut in its own right, but still useful to know. By default, when you type a value into a cell and then hit Enter, Excel goes down to the next row. If you'd rather have it go right (or, more obscurely, up or left), you can change that. Go to File --> Options and choose Advanced. Make sure 'After pressing Enter, move selection' is ticked at the top of the list under 'Editing options', then choose your preferred direction and click OK.

The Alt key for the Ribbon

Like it or hate it, the fact the Ribbon has now made its way into Windows Explorer suggests it's not going anywhere. And despite its mouse-centric design, you can access virtually anything on the Ribbon via the keyboard, though you might need several keys to do so.

The basic principle is this: hit the Alt key on its own and a series of highlight letters will appear on the Ribbon showing how you can choose particular tabs, such as H for Home or N for Insert (the File menu is still the old-fashioned Alt-F.) Hit that letter, and you'll see more letters appear on each option on that tab (as in the picture above). Typing that letter or pair of letters will select that option.

So Alt then H then AC will centre the current selection, while Alt then H then V will paste, and Alt then P then B will bring up the Breaks dialog. It's not very obvious or memorable, and in some cases there may be a faster alternative (such as Ctrl-V for paste). But if all else fails, you can commit your most common sequences to memory and virtually never touch a mouse again.

Lifehacker 101 is a weekly feature covering fundamental techniques that Lifehacker constantly refers to, explaining them step-by-step. Hey, we were all newbies once, right?


Comments

    The ribbon sucks. I like a nice menu of options and a long list of favoured functions like older versions for quick access. I never find what I want with the ribbon easily, so I opt for OpenOffice these days.

      I agree to a certain respect in that I used to be able to just move the mouse along different branches of the menu to get what I want, and now I have to click each time to get to what I want.

      And now that I'm more used to the ribbon, I find both difficult.

      Although don't forget you can still customise the ribbon to have your own favourites.

        People need to learn to embrace change. Work just upped us from office 2003 to 2010 and it is way better even after just a day i think i have the hang of it, so much more intuitive, just give it a go.

        It's just like bagging osx lion, i love it, reverse scrolling and all only took a day to get used to

          I love change for the better, but the Ribbon bites.

          You also used to be able to write addins that would bring in a custom menu and icons ( easily ) so you populate the most used functions across the office. Not Now.

          Hate hate spew vomit...... (Ted Bullpit)

            no way is the ribbon more inutitive

            I have word 2007 at home and use word 2003 at work and 2003 is so much faster and quicker for most people to use.

            Everyone hates if their laptops gets upgraded and they get Office 2007.

              Our office has its own custom ribbon. So you can still do it!

    Probably more useful to the masses than changing the direction of data entry is the Tab key - this will move your selected cell to the right each time, and then when you press enter, instead of moving directly down from the current call, it will move down to the cell directly under the one you initially pressed Tab for (providing you don't use any other method to move from cell to cell)

    One of the best ones i learnt at uni was the F4 button when writing a formula to change the absolute reference to a cell (the $ sign in front of the column or row reference).

    If you're writing a lot of formulas this one is invaluable.

    Alt + E -> S -> U

    Paste special; Values and number formats.

    Single most common keyboard shortcut path which I picked up from Excel2003, which thankfully has stuck around into Excel2007, and hopefully 2010 too.

    Why Excel defaults to pasting formatting as standard is beyond me!

      Sam, do yourself a favour and download Pure Text - it strips formatting out on the fly and is one of my must haves!!!

      When I work from home and use Citrix it's one of the tools I miss the most.

        You can also just shortcut the 'remove formatting' command. :)

          ... and you can do that for EVERY application that you want to strip the formatting out for... or you could use Pure Text :)

          DISCLAIMER: It's freeware - I don't make anything from plugging it other than helping out a few fellow LifeHackers.

        @Michael

        I could, but the above key combination is second nature to me now, and I can hit the combo virtually as quickly as a normal Ctrl + V. Plus it works universally where-ever Excel does.

          "Plus it works universally where-ever Excel does" - I can't argue with that.

    I'm not sure how well known this little Excel shortcut is, but instead of always starting a simple formula with the "=" sign (which requires you to move off of the number pad) you can start formulas with the "+" or "-" signs. E.g. "+1+1" will return a result of "2". If you go back to the cell, Excel will have changes the formula input as "+1+1" to "=1+1" automaticall. This works for "-" too.

      ...I like it! Cheers, I knew of all the above ones but not this. I hate having to press the = key.

    The ribbon has dropped the number of support calls I get, because there seem to be less options and staff don't get confused, bypass the basic act of searching and call me directly.

    I was kind of annoyed that there wasn't a bridging option (e.g. switch between new and old menus) but hey, whatever.

    Besides, this is 2011. Why are we still fighting over this menu system?

    As well as Shift + Arrow Key to select, Ctrl + Shift + Arrow key selects all the way to the end of the content in that direction. Saves having to press the arrow key several times.

    1. Alt + Enter , add a new line within the Cell, good for addresses and the like

    2. Control + ; insert current date

    3. Control + : insert current time

      Agreed! Ctrl + ; would easily be my most used Excel keyboard shortcut

    CTRL + " - copy the value from the cell above

    CTRL + ' - copy the formula from the cell above

    Also, if you input data and press TAB to move to the right, no matter how many times you do it, when you press ENTER, you go to cell underneath where you started in the previous row.

    Ctrl + ~ a must for Excel formula users as it allows you to see all forumulas directly in the cell without the need to F2 or view the Formula Bar.

    Ctrl + Tab to "cycle" between workbooks

    F11 to create a default chart

    Double click the Fill Handle to copy down beside a column of values. Great when you have a formula in one cell and want to repeat the same formula for all the cells immediately underneath in the column.

    Those a few of the more obscure I cannot live without.

Join the discussion!

Trending Stories Right Now