Once you start using these top 10 Excel keyboard shortcuts, you will never look at your Excel spreadsheets the same way again. If you want to become more efficient with Excel, shortcuts are a good way to speed up your data entry, find your way around your spreadsheet quickly and… impress your boss. Who’s got time to drag a mouse around the screen anymore?
1. Select entire row or column
Selecting an entire row or column can be useful when you need to make quick formatting changes to headings on your spreadsheet or applying number formats to specific columns of data.
To select an entire row or column, you would normally click the row number or column letter – or click and drag to extend the selection to adjoining rows and columns.
Use these shortcuts instead with one or more cells selected to select as many rows or columns.[SHIFT] + [Spacebar] = select entire row [CTRL] + [Spacebar] = Select entire column
2. Navigate to the next or previous sheet in your workbook
When you work with large workbooks and multiple spreadsheets, you probably need to switch between sheets a lot of the time; you may have to compare figures, return to a sheet to enter a formula, copy and paste across sheets. If you do that, you keep moving the cursor/mouse around the screen and ‘losing’ your place.[CTRL] + [Page Up] takes you to the next sheet in the workbook [CTRL] + [Page Down] takes you to the previous sheet in the workbook
Keep in mind, on some keyboards, these keys may be labelled PgUp and PgDn or to access them you may have to press extra keys, e.g. the Fn key.
3. Insert or delete a row, column or range
I always disliked the number of steps it takes to do something as simple as inserting or deleting a column in Excel spreadsheets. One way is, of course, to right-click a row or column heading and select insert or delete; another way, in the current version of Office, is to locate the Insert or the Delete command in the Cells group of the Home tab.
Much quicker are these shortcuts[CTRL] + [+] Insert a row, column or range before the current selection [CTRL] + [-] Delete a row, column or range before the current selection
4. Zoom in and out
Sometimes you need to navigate around a large spreadsheet quickly, have an overview of its layout and page breaks before printing it or you try to select a large area of the sheet without scrolling endlessly. For all those times, you can use the Zoom options that are available throughout Excel i.e. the zoom slider and zoom percentage indicator at the bottom of the screen or one of the commands in the Zoom group of the View tab.
Use this keyboard shortcut to zoom in and out of your large spreadsheets effortlessly 15% at a time.
Zoom in with [CTRL] + [ALT] + [=]
Zoom out with [CTRL] + [ALT] + [-]
5. Edit the active cell
To edit a cell, you usually double-click the cell or select the cell and edit it in the formula bar. It’s all quite straightforward… unless your mouse is somewhere else on the screen (maybe you were using to switch sheets, scroll or to choose a command in the ribbon) and in that case you need to move the cursor on the screen to locate it before you can do anything else.
These shortcuts make basic cell editing operations so much faster:
Press [F2] to edit the active cell
Press [ENTER] or [RETURN] to confirm the change
Press [ESC] to cancel the change
6. Edit multiple cell entries
While text and number entries are less likely to repeat in your spreadsheet, if set up correctly, formulas will. There are many ways to apply the same formula (or text!) to multiple cells; the most common is to copy the formula (or text) and then paste it into a range; a close second is to use the autofill handle.
I prefer to do it this way: select the range that will take the same content; the selected cells are shown in grey and the active cell, where you type the content, is the one with a thick black border. When you type, the formula (or text!) goes into the active cell. Instead of pressing [ENTER] press this shortcut combination:[CTRL]+[ENTER] to confirm entry and apply to all selected cell. [ENTER] if you only want to make the change to the active cell and not the rest of the selection.
7. Jump around a table or sheet
Some tables can be very large which means moving around a spreadsheet takes a lot of scrolling (and more patience or time than I have). What if you could just tell your Excel spreadsheet: “take me to the bottom of this column (or row, or sheet).” Well, you can.
In fact, this shortcut works in all directions – down, up, left and right – just choose the right arrow.
It will move the active cell to the next blank in your data or to the next non-blank cell in a range; hit the same key combination again if you need to continue moving in that direction.[CTRL] + [Down, Up, Left or Right Arrow] to jump to the end of the range, repeat to reach the end of the sheet. [CTRL]+ [Page Up or Page Down] to jump to the top or bottom of the sheet [CTRL]+[Home] to jump to the top left of the sheet (cell A1) [CTRL]+[End] to jump to the last used row and column.
8. Extend selection in a table
Sometimes you don’t want to just jump around your table and you need to copy or extend formatting to the rest of a table. So, you click the first cell, hold the primary mouse button down and drag down (or left or up or down) and if that selection is very long you wait for the sheet to scroll in front of you.
These shortcuts are like the ‘jump around’ shortcuts with the [SHIFT] key thrown in. They will extend your selections to the next blank in your data or to the next non-blank cell in a range; if that’s the case and it’s too soon, just hit the same key combination again.[CTRL] + [SHIFT]+ [Down, Up, Left or Right Arrow] to [CTRL]+[A] to select the range of adjoining cells in all directions
I probably use =SUM() a hundred times a week and until a few years ago it never dawned on me to look for a shortcut, I thought to press the AutoSum ∑ command was good enough, itself a short way of inserting the SUM function, its equal sign, brackets and adjoining cell references. But there are always quicker ways to do things in Excel and this one must have same me whole days since I started using it.[ALT]+ [=] to insert the Sum function and select the adjoining range (but check that Excel selects the correct range)
10. Show/hide formulas
Speaking of SUM and formulas in general, few people know that they can do this at all but it’s essential when you need to troubleshoot or audit an existing spreadsheet, when you want a bird’s eye view of how it’s built and check that formulas are consistent. This command is found in the Formula Auditing Group of the Formulas tab in Excel.[CTRL] + [`] to show formulas and again to hide formulas.
You can usually locate the [`] key just above the [TAB] key – it’s not the same as [‘] that you use all the time.
There are many ways to accomplish the same tasks in an Excel spreadsheet. Shortcuts are a great way to minimise the amount of clicking and selecting that you would have to do when working with large spreadsheets. It is worth spending a little time to learn the shortcuts on this page, they are the ones people ask me about the most, and to continue discovering more of your own. Which are your favourite time-saving Excel shortcuts? Which ones should be on this list?
Here is the list in a format you can print or clip. What are your favourite Excel shortcuts when working with large spreadsheets?
|Top 10 Excel Shortcuts for Large Spreadsheets|
|[CTRL] + [Page Up]|
Activate the next sheet in the workbook
|[CTRL] + [Page Down]|
Activate the previous sheet in the workbook
|[CTRL] + [+]|
Insert a row, column or range before the current selection
|[CTRL] + [-]|
Delete a row, column or range before the current selection
|[CTRL] + [ALT] + [=]|
Zoom in by 15%
|[CTRL] + [ALT] + [-]|
Zoom out by 15%
Edit the active cell
|[ENTER] or [RETURN]|
Confirm a cell edit
Cancel a cell edit
Apply cell edit to multiple selection
|[CTRL] + [Arrow]|
Jump to the end of the range
|[CTRL]+[Page Up or Page Down]|
Jump to the top or bottom of the sheet
Jump to the top left of the sheet (cell A1)
Jump to the last used row and column.
|[CTRL] + [SHIFT]+ [Arrow]|
Select adjoining cells in the direction chosen
select adjoining cells in all directions
|[SHIFT] + [Spacebar]|
Select entire row
|[CTRL] + [Spacebar]|
Select entire column
Apply AutoSum to adjoining cells
The best shortcut to Excel productivity.