Excel Top Ten shortcut keys

Top Ten Shortcut Keys

Ask ten people what their favourite Excel shortcut keys are and you'll get ten different answers. In no particular order, here's my personal choice of the ones that I can't do without. I'm not going to list ALL the Excel shortcut keys here because there are hundreds of them.

You can find a full listing of all the Microsoft Excel 2010 Shortcut Keys in our newsletter.

Ctrl+A  Select the Current Region

Select Current Region

Click a cell, press Ctrl+A and Excel expands your selection to select the Current Region. Press Ctrl+A again and the entire worksheet is selected.

The Current Region is defined as the current area of continuous data bounded by blank cells. In older versions of Excel you may have to press the key combination Ctrl+* to achieve the same effect (Ctrl+SHIFT+* if you don't have a dedicated * key)

Ctrl+Spacebar  Select the Current Column

Select Current Column

What, pick up my mouse and click the column letter. No way! Ctrl+Spacebar selects your current column and then to extend the selection to the left or right you hold down the SHIFT key and use the Left or Right arrow key.

SHIFT+Spacebar  Select the Current Row

Select Current Row

Well, I did say that it's a personal choice. Lazy-bones here selects the current row with SHIFT+Spacebar. Then keep the SHIFT key held down and press the Up or Down arrow keys to extend the selection up or down.

Ctrl+D and Ctrl+R  Fill Down and Fill Right

Fill Down

Fill formulas or data down the column by selecting down with the SHIFT and Down arrow key. Then Ctrl+D fills down the selection. Ctrl+R to fill across to the right.

Ctrl+`  Show/Hide Formulas

Show/Hide Formulas

You can't check that you've got your formulas entered correctly if you can't see them.

Switch between displaying the returned results of your formulas and the actual formula itself by pressing Ctrl+` (that's  usually the key under the ESC key in the top left hand corner of the keyboard)

ALT+=  AutoSum

AutoSum

Instead of clicking the AutoSum control every five minutes ALT+= enters a sum formula into the active cell. But it only does SUM and does not give you a choice of other functions.

See my article AutoSum Revisited for a few ideas on getting the best out of AutoSum.

Ctrl+ENTER  Range Entry

Enter into a Range

Pressing the ENTER key makes an entry into your current active cell. Ctrl+ENTER makes an entry into every single cell in your current selection. Make the selection, type in your entry and then press Ctrl+ENTER.

Constants (text or numbers) and formulas can be entered into a range in a single step. Formulas give you a relative reference for each cell, so if you get the first one right then all the others will adjust accordingly.

To enter data into a discontinuous range, make a multiple selection first. Select a cell or a range of cells then hold down the Ctrl key as you add to your selection by clicking on other cells or ranges.

ALT+ENTER  Insert New Line

Force New Line

Rather than wrapping the text in the cell whenever you want to force a new line press ALT+ENTER.

This is very handy for creating Excel source data for Tables or Pivot Tables where you maybe need to have multiple lines of descriptive text for a heading but retain the integrity of the single physical header row.

F4  Absolute Reference and Repeat

Absolute Reference

When you need Absolute References (dollar signs) in your formulas press F4. Succeeding presses of the F4 key return all the permutations of absolute and relative reference: $A$1, A$1, $A1, A1.

To make a single cell reference in your formula absolute, click somewhere on the reference then press F4. For a range reference or a series of references, drag across the references first before pressing F4.

F4 has a double life, when you are in Ready mode (i.e. not editing your formulas) F4 means Repeat. Say you have the tedious job of going through a worksheet and deleting some of the rows, delete the first one as you usually do and then select the next row to be deleted and press F4 to repeat the row deletion.

F11  Chart

Plot Chart from current selection

Pressing F11 plots a default chart on a separate chart sheet based on your current cell selection. Press ALT+F11 for an embedded chart on the active sheet.

Excel will automatically execute a current region selection if you start with a single cell selected in your chart data range.

To plot discontinuous ranges, make a multiple selection using your Ctrl key before pressing F11.





What's your favourite Excel shortcut key?

Download Excel 2010 Shortcut Keys as pdf

Related Posts

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-Calculating age from date of birth

Excel-Drop-down lists

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.