Keyboard shortcuts in Excel

If you have a lot of rows and/or columns in your spreadsheet it can be a real pain navigating from one spot to another. Selecting many rows of data is also a tedious operation. The trick is to learn some of the keyboard shortcuts, key-combinations that can save you a lot of time and effort. Note that these only work for Windows versions of Excel.

All cells have a row and column reference, which is shown in a box (the Name Box) to the left of the formula bar. You can type a cell reference in this box to jump directly to a cell. This works fine if you know the column and row number of the cell you want – of course A1 will always take you to the top of your worksheet.

If you select one or more cells and type a name in the Name Box you assign a name to those cells (the cells do not have to be adjacent to one another). Later, you can type the name to jump to the location defined by the name. You can also use the drop-down icon and select a name. If your name “points” to several cells they are all selected.

The Name Box is not quite a keyboard shortcut, but it links the Named Ranges to the keyboard shortcuts you’ll see next.

There are two important “key modifiers” that you need in conjunction with the arrow keys:

• Ctrl
• Shift

The control (Ctrl) key allows you to jump rapidly from one end of a block to another. The Shift key allows you to select cells.

If you hold the Ctrl key and press the arrows you’ll jump in the direction of the arrow. The distance of the jump depends on the data, if there are data in the row or column you’ll go to the end of the current block of data. If you press Ctrl+Arrow again you’ll jump to the start of the next block, press Ctrl+Arrow again and you’ll jump to the end of that block. If there are no data you’ll travel to the extreme edge of the worksheet, which can be hundreds or thousands of rows/columns, depending on your version of Excel.

The Shift key allows you to select cells. If you click in a cell, then press Shift+Arrow you’ll select cells in the direction of the arrow.

Using Ctrl+Shift+Arrow allows you to select a block of data in a row or column. Thus you can highlight many rows and/or columns of data quickly and easily.

You can select everything using the space between the row and column headers, which contains a triangle. Clicking in the box selects the entire worksheet, including empty cells. This is not always what you want so you can use Ctrl+A to select all the data.

However, Ctrl+A does not work how you might expect. If you click in a block of data then press Ctrl+A the block of data will be selected, if there are other data cells they will not be selected if there are empty cells between the blocks. The selection is rectangular so if one row or column is bigger than the others, the selection area is expanded to incorporate the cells.

If you click a cell adjacent to a block of data and type Ctrl+A the block of cells next to the insertion point will become selected. You can position the insertion point so that several blocks of data become selected, essentially Excel looks at the cells surrounding the insertion point and expands the selection to include any non-empty cells. This is how you can get a chart or a Pivot Table without having to select any data. Knowing this behaviour also allows you to insert blank charts, by ensuring that the insertion point is not in, or adjacent to, any data.

The Ctrl and Shift keys can be used with the mouse. The Ctrl key allows you to select non-adjacent cells or cells in a non-rectangular block. The Shift key “fills in” the selection between the first block you select and subsequent blocks. This behaviour extends to entire rows or columns, so you can click in the headers to select several rows or columns.

The shortcuts can be used in conjunction with various menu windows. For example, if you are selecting data for a chart using the Select Data button. You can click the topmost cell in a column for example and extend down the entire block using Ctrl+Shift+Down Arrow. This is helpful when you have many rows of data.