Excel – Keyboard Shortcuts
Within Excel there are literally hundreds of keyboard shortcuts, from accessing all the buttons on the Ribbon, saving, editing or selecting data. Having said that, unless you have an amazing memory and are a touch typist, it is highly unlikely you will ever remember or even need to use all of these shortcuts.
Here are a handful of shortcuts that I have found useful over the years. This is by no means a definitive list of “must know” shortcuts as everyone has different needs, but I think these are a useful selection for most users of Excel.
The conventions I use here are as follows;
Plus Symbol +
If the plus symbol is shown between two or more keys then the keys have to be pressed at the same time. E.g. Alt + = means press Alt and = at the same time to insert the AUTOSUM function in a cell.
Greater Than Symbol >
If this symbol is used between keys then this represents a sequence of keys. E.g. Alt > D > P means press Alt first, release the key and then press D. Again, release the key and then press P which in this example opens the Pivot table wizard.
Forward Slash Symbol /
If this symbol is used between keys then this represents “or”. E.g. Ctrl + →/←/↑/↓ means press Ctrl and right/left/up or down arrows.
Everything on the Ribbon can be accessed via a keyboard shortcut. To view the shortcuts press Alt.
A letter will appear for each tab. For example, if you want to access the FORMULAS tab you need to press M.
After pressing the letter that corresponds to the tab you want to get to, that will activate the tab and because you pressed Alt the letters you need for that tab are visible.
Again, press the relevant key to activate the function in the Ribbon. For example, if you wanted to activate the CALCULATION OPTIONS, you would press Alt > M > X and in this case this activates a further drop down set of options. Press the key you need when this is displayed – here we have the options of A, E or M.
I won’t go through every possible set of key combinations as each person will have their own favourite actions or buttons that they need to access on a frequent basis, but I will try to split the shortcuts into categories where possible…
|Alt > H > AC||Align text centre in a cell|
|Alt > H > AL||Align text left in a cell|
|Alt > H > AR||Align text right in a cell|
|Alt > H > AT||Align text top of a cell|
|Alt > H > AM||Align text middle of a cell|
|Alt > H > AB||Align text bottom of a cell|
At least these are fairly obvious in terms of which letters represent a position within a cell.
|Ctrl + 1 (one)||Open FORMAT cells dialog window|
|Ctrl + B||Make cell contents BOLD|
|Ctrl + I||Make cell contents ITALIC|
|Ctrl + U||UNDERLINE contents of a cell|
Simple ones but very useful, very often.
Selecting cells quickly and moving around a worksheet at speed are great time savers and well worth knowing.
|Ctrl + Home||Returns you to cell A1 from anywhere in a sheet|
|Ctrl + End||Goes to last cell containing test/data in your worksheet|
|Ctrl + Page Down||Move to next worksheet to the right|
|Ctrl + Page Up||Move to next worksheet to the left|
The next set of shortcuts allow you to move quickly to the end of a contiguous set of cells containing data. The cursor will stop as soon as it hits an empty row or column.
|Ctrl + →||Go to last cell to the right (with data) in the row|
|Ctrl + ←||Go to last cell to the left (with data) in the row|
|Ctrl + ↓||Go to last cell (with data) in the column|
|Ctrl + ↑||Go to first cell (with data) in the column|
If you hit a blank row or column and there is data beyond it, simply repeat the key combination until you reach the end of the data table. If you go too far in any direction (i.e. to the far right or bottom of the sheet), just use the opposite arrow with the Ctrl button.
To select cells between your current location and the end of a row/column or the entire table combine Ctrl with Shift and the arrows.
|Ctrl + Shift + →||Select all cells up to last cell to the right (with data) in the row|
|Ctrl + Shift + ←||Select all cells up to last cell to the left (with data) in the row|
|Ctrl + Shift + ↓||Select all cells up to last cell (with data) in the column|
|Ctrl + Shift + ↑||Select all cells up to first cell (with data) in the column|
|Ctrl + Shift + End||Select all cells to the last cell in the data sheet (with data)|
If you select too many rows or columns, leave the cells selected and press Shift + ←/→/↑/↓ to deselect one row/column at a time. The same can be used on a single highlighted row or column of cells.
|Ctrl + G||Opens the Go To dialog box|
|Alt + S||Opens the Go To Special dialog box from the Go To window|
|Ctrl + F||Opens the FIND dialog box|
|Ctrl + H||Opens the REPLACE dialog box|
Note you can only use Alt + S after opening the Go To dialog box.
Go To is useful to get to cells or named ranges quickly. The Go To Special dialog box gives you the ability to navigate to or highlight certain types of cells in your worksheets such as any cells with conditional formatting applied to them.
One thing you find yourself doing often is deleting or adding new rows and columns or wanting to hide some. So you end up right clicking and choosing from the options there or start searching for a button that can do it for you. I have found these to be quite useful little time savers;
|Shift + Spacebar||Selects the entire row of the current selected cell|
|Ctrl + Spacebar||Selects the entire column of the current selected cell|
|Ctrl + +||Inserts cell(s) or a new row/column|
|Ctrl + –||Deletes cell(s) or a row/column|
|Ctrl + 9||Hides the row(s) of the selected cell(s)|
|Ctrl + 0 (zero)||Hides the column(s) of the selected cell(s)|
|Ctrl + Shift + 9||Un-hides the row(s) of the selected cell(s)|
|Ctrl + Shift + 0 (zero)||Un-hides the column(s) of the selected cell(s)|
A few notes about this last set of shortcuts:
If you use Ctrl + +/- without selecting an entire row or column first then you get a dialog box asking you whether you want to insert or delete the selected cell(s) or the entire row or column.
Also note that if you are using a laptop without a full numerical keypad, you need to use Ctrl + Shift + +/-. If these dialog boxes appear then press the letter key that matches your choice e.g. R to delete or insert an entire row.
So if you do want to use these keyboard shortcuts you are likely to do the following;
Ctrl + Spacebar > Ctrl + + which will insert a new column to the left of the selected column.
Using Ctrl + 9/0 will hide the row or column of the selected cell and if you IMMEDIATELY press Ctrl + Shift + 9/0 it will unhide whatever you just hid. However, it is more likely that you will want to unhide something at a later stage, so you will need to select the rows/columns either side of the hidden ones before you use this shortcut.
The final section is just a selection of other keyboard shortcuts I use, including some very basic ones that you can use in any application;
|Ctrl + S||Saves the current document/workbook|
|F12||Opens the SaveAs dialog box|
|Ctrl + N||Creates a new document/workbook|
|Ctrl + C||Copies the selected cell(s) or object|
|Ctrl + V||Pastes the copied cells(s) or object to another location|
|Ctrl + X||Cuts the selected cell(s) to the clipboard|
|Ctrl + Z||Undo the last action (repeat for multiple undo actions)|
|Ctrl + Y||Redo the last action (repeat for multiple undo actions)|
|Ctrl + Shift + L||Applies AUTOFILTER to the selected cells/row|
|Ctrl + F1||Minimise/Maximise the Ribbon|
|F11||Creates a chat in a new chart sheet (uses default chart type)|
|Alt + F1||Creates an embedded chart in the same sheet (uses default chart type)|
|Shift + F2||Insert cell comment|
|F3||Opens the PASTE NAME dialog box to show a list of any named ranges you have in the current workbook|
|Ctrl + Shift + F3||Opens CREATE NAMES FROM SELECTION dialog box to create named ranges using existing column/row headings|
|F7||Runs spell check|
|Alt + F11||Opens the VISUAL BASIC EDITOR|
|Ctrl + `||Toggles between showing/hiding formulas in the worksheet (Note this is the combination on a UK QWERTY keyboard)|
|Shift + F11||Insert new worksheet to left of current worksheet|
|Alt + =||Insert the AUTOSUM function|
|F4||Toggle between relative and absolute cell references or repeat last action (limited use). See below for more information on the use of F4|
After entering a cell reference in a formula press F4 to add in the “dollar” characters. Continue pressing F4 to get the required level of absolute/partial referencing.
And there you have 56 keyboard shortcuts that hopefully you will find a use for. As I said at the beginning this is not a definitive list. You still have about another 200 to learn beyond this list, but personally, these are shortcuts that I use regularly and cover what I think are the day to day actions that you will use in just about every spreadsheet you open.