In this series we will be looking at the Excel tools and techniques that help you accomplish a range of day-to-day Excel tasks more efficiently and effectively. As part of each article, we will be scouring the extensive Excel Community archive to provide links to additional details and ideas. This is the story of the series so far:
Speed up formula entry
- Excel how to: speed up entering formulae part 1 - using the dollar signs to fix all or part of a reference
- Excel how to: speed up entering formulae part 2 - using Range Names to make formulae easier to enter and understand
- Excel how to: speed up formulae - using Excel tables – creating dynamic references by referring to Table columns and using Table structured references to make formulae easier to understand
- Excel how to: speed up formulae - using Excel tables part 2 – adding calculated columns to an Excel Table
- Excel how to: speed up entering formulae – Dynamic Arrays – how the recent introduction of Dynamic Arrays can dramatically reduce the number of formulae you need to enter in the first place
Speed up formatting
- Excel how to: speed up formatting using Excel Styles. Part 1 – using Excel Styles to format Excel cells with a single click and to help implement the ICAEW 20 Principles for Good Spreadsheet Practice.
- Excel how to: speed up formatting using Excel Styles. Part 2 – using the three number styles to set up your own number formats that can be applied directly from the commands in the Number group of the Home Ribbon tab.
- Excel how to: speed up data entry – making data entry as quick and efficient as possible by knowing the effect of some key Excel options and features, together with some useful keyboard shortcuts.
Introduction
One section of the previous post in this series was devoted to the use of keyboard shortcuts to help with data entry. In this article, we will look at other keyboard shortcuts that can help speed up further aspects of Excel. Given that lots of comprehensive lists of keyboard shortcuts are available, this will be a personal selection of some of the keyboard shortcuts and techniques that I find particularly useful. Many people will use many more keyboard shortcuts and others fewer. How much difference using keyboard shortcuts makes will depend on how you use Excel, your keyboard skills and how comfortable you are using a mouse or a trackpad. The benefits of using shortcuts can also change depending on where you are using Excel – for example, when sitting at a desk with plenty of room to use a mouse, compared to using an over-sensitive trackpad on a cramped and busy train.
Discovering keyboard shortcuts
If you do find keyboard shortcuts useful then there are several ways to discover the shortcuts that are available. For a comprehensive list of keyboard shortcuts, organised by category, click on the Help Ribbon tab, then select Help from the Help group. The Help pane should appear and you can type ‘keyboard shortcuts’ into the search box. Alternatively, you can type ‘Keyboard shortcuts’ directly in the Search box in the Title Bar and select the ‘Keyboard shortcuts in Excel’ option:
Standard operations
Possibly, the shortcuts I use most are those for cut, copy and paste which, remembered in that order, correspond to three adjacent keys:
| Ctrl+X | (open scissors for cut) |
| Ctrl+C | (Copy) |
| Ctrl+V | paste with very sticky glue |
Undo (Ctrl+Z) and Redo (Ctrl +Y) would come a close second for frequency of use. Although it doesn’t always work as you would expect, the F4 key will often repeat the last action you performed. For example, if you were to apply a highlight to the selected cell, you could then select any cell, or set of cells, and use F4 to apply the same highlight. Note that this only applies to the last action so, if you make two or more format changes, only the most recent will be repeated by using F4.
File operations
Particularly since the main file operations were relegated to the File Ribbon tab, using keyboard shortcuts rather than the mouse or trackpad to access these commands can save a large number of small amounts of time:
| Ctrl+S | (Save) |
| F12 | (open save as dialog) |
| Ctrl+O | (open file Open dialog) |
| Ctrl+N | (New workbook from default template) |
| Ctrl+W | (close current Workbook) |
| Ctrl+P | (Print) |
Navigation and selection
There are some general techniques when using keyboard shortcuts to navigate through workbooks or to select content. Using the Ctrl key with a navigation key will extend the navigation to the end of the current area (which will vary depending on context) and holding down the Shift key at the same time will change the navigation to a selection. For example, if you select the top, left-hand corner cell of the data area within an Excel Table, Ctrl+Shift+Right arrow will select the entire top row of data and, with that row still selected, Ctrl+Shift+Down arrow will select the rest of the data area of the Table. Note however that, as we shall see shortly, there is an even quicker shortcut option available to select just the data area of a Table:
The effect of using the Ctrl and Shift modifiers is not limited to using the keyboard arrow keys: they can also be used with the Home and End keys to move/select to the beginning/end of the used area of the worksheet. When editing cell contents, Ctrl+Shift+Home will select from the current insertion point to the start of the formula, and Ctrl+Shift+End will select from the current insertion point to the end of the formula.
Ctrl+A (for All) is also very useful for selecting everything. So, in a formula, Ctrl+A will select the whole formula. Outside of cell editing, Ctrl+A will potentially select all the cells in a worksheet but, where the selected cell or cells are part of a block of data, just the block of data will be selected. For a Table, if any cell or cells in the heading row are selected Ctrl+A will select the entire Table, but if the selected cell or cells are wholly within the data area, then Ctrl+A will select just the data area.
Ribbon commands
If you really want to replace the mouse or trackpad with the use of the keyboard, then the Alt key allows you to access all of the Ribbon and Quick Access Toolbar commands. Pressing Alt will show a letter, or letter combination for each Ribbon tab and some Title Bar options, together with numbers for Quick Access Toolbar items:
Alt,H,V,V
Other shortcuts
It’s worth mentioning a few other useful shortcuts:| F4 | when used within, or adjacent to, a cell reference to cycle through the absolute/relative variations |
| Alt+= | to use AutoSum |
| Ctrl+Enter | to enter the value being entered or edited in all the selected cells in one go. |