The Best Excel Keyboard Shortcut

 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 CtrlShift – and tap the 8 key on top of the keyboard.

This will select the entire block of information.

The Best Excel Keyboard Shortcut

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.

The Best Excel Keyboard Shortcut

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’.

 

The Best Excel Keyboard Shortcut

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.

The Best Excel Keyboard Shortcut

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.

The Best Excel Keyboard Shortcut

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!

The Best Excel Keyboard Shortcut

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. 

How do your Excel skills stack up?   

Test Now  

About the Author:

Matthew Goodall  

Matthew is a qualified Microsoft Office Specialist, Microsoft Certified Applications Specialist and a Microsoft Certified Trainer with over 11 years of hands-on experience in a training facilitation role. He is one of New Horizons most dynamic instructors who consistently receives high feedback scores from students. Matt enjoys helping students achieve real professional and personal growth through the courses he delivers. He is best known for creating “fans” of students, who regularly request him as an instructor for any future courses they undertake at New Horizons.

Read full bio
top