Mar 06, 2015

OK, I may be exaggerating slightly with the title because the ‘best’ keyboard shortcuts are really the ones we each individually use the most, and you may do different things in Excel to me. However this is a really good keyboard shortcut to know. To me, it is the best Excel keyboard shortcut!

If you want to select a large block of data, like a list. Click on a single cell in the list and press Ctrl – Shift – and tap the 8 key on top of the keyboard.

This will select the entire block of information.

However the keyboard shortcut is easy to remember, the top half of the 8 key is the * (star/asterisk) key so I remember this as Ctrl *

How does it work? Basically when you do a Ctrl *, Excel expands the selection until it hits a gap: a blank row, blank column or the edge of the sheet. So it’s a really good idea to NOT have blank rows or column is your data because Ctrl * will stop at the gap.

Partially blank rows or column are OK, because that is kind of like having an open ‘gate’ between to the two areas of data and the Ctrl * can go through the ‘gate’.

Delete the blank rows or columns inside your block of data, it will help Excel which will in turn help you a lot. Another thing to be careful of is having extra data right next to the block of information. If you do, Excel thinks it part of the block and select it as well, leave a gap of at least one blank row and column around your data.

Often when I do totals at the bottom of a list I’ll leave a gap of one row between the list and the total, that way I can still use Ctrl * to choose the list but not the total.

Notice here row 30 is a gap between the data and the total.

Ctrl * is really good because if you choose a single cell in the data before you try to Sort, Filter, Subtotal, make a Pivot Table, Remove Duplicates, format as a Table, or other things I've forgotten, Excel will do a Ctrl * for you! So you don’t have to choose the area just a single cell, great!

Here I didn't choose the whole area before I clicked on the Sort button, but Excel selected the whole are for me.

There’s only one limit to Ctrl * is that it doesn't work inside formulas, fortunately if you keep reading I’ll explain how to choose a block of data inside a formula.

I fibbed with the title of this blog I’m not going just show one keyboard shortcut, here’s an extra one that is just as good as the first one.

Ctrl * doesn't work inside formulas, but there’s a keyboard shortcut that does let you choose a big area of data, and do it inside formulas.

Select the cell at the top left corner of your block/list of data and go Ctrl – Shift – Down Arrow and then go Ctrl – Shift – Right Arrow

Notice how the selection sweeps down to the bottom and then across to the right.

How does it work? Shift is a little like holding down the mouse button, it lets you select an area rather than just move through it. Go to A1 then hold down Shift and use the arrow keys. See how it expands the selected area rather than just moving the active cell around?

Ctrl Arrows move you to the beginning or end of the next block of data. If you are on a blank cell, it takes you to the next non-blank cell in that direction (or the end of the sheet if there aren’t any non-blank cells in that direction). If you are on a non-blank cell it takes you to the other end of the non-blank cells. So basically to the other end of the data, assuming that there aren’t any blank cells in the middle of the data.

So when we were at the top left, Ctrl took us to the bottom and Shift selected the area rather than just moving through it. The same when we selected to the right.

This will always work as long as your top row always has data in it (it should have your column headings) and your left column doesn't have any blanks in it. Otherwise you may have to tap the arrow key a couple of times to get to the bottom or right.

So there you have it, the best Excel keyboard shortcut(s). For more information, have a look at our Excel training courses.