Excel Top Ten Shortcut Keys
Table of Contents
Excel 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.
Ctrl+A Select the 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
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
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 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
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
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
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
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
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) F4means 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
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.