Navigation: Zipping around the worksheet

When I take over someone's machine to help them out with Excel, whether remotely or in person, the first thing that wows them is the speed at which my cursor moves around the screen to accomplish the task at hand.

Below is an explanation of some of the shortcuts you need to zip around a worksheet. With practice, they become second-nature. And they're usually much faster than reaching for the mouse.

CTRL

The CTRL key is the master-key for moving around at speed. Hold it down while using the four direction keys, and your speed increases inordinately.

If you are resting in a cell with nothing in it, CTRL together with a direction key will take you to the next populated cell in the direction you choose. So if you're way off to the right of a table of data, CTRL+Left will bring you back to the rightmost column in that range.

If instead you are sitting in a cell that's populated, CTRL together with a direction will take you to the end of that populated range. So if you're near the top of a column of data, CTRL+Down will take you to the bottommost populated cell in that column. Beware though: If you have blank cells midway down the table, CTRL will stop before each blank cell.

Shift

This one's more straightforward. If you're holding down Shift, Excel will select everything you select, together with everything in between. So if you're in A1 and select D5 with Shift depressed, you'll select the entire rectangle between those two corners.

Shift+CTRL

This is where things get powerful. By using the two functions above, you can quickly and easily select entire ranges. If you're in the top-left corner of a table, then hit Down followed by Right while holding Shift and CTRL down. Suddenly, the entire table is highlighted ready for you to do what you choose.

But I think the most beautiful example of navigation is as follows. Imagine you have a long, contiguous column of data—that is, there are no gaps between entries in column A, starting in row 2—postcodes, let's say. And you write a formula in cell B2 that relates to the data in cell A2—to strip out its first half (or outward code, if you will).

=LEFT(A2,find(" ",A2,1)-1)

And let's say you want to copy that formula down the entire column. Now the novice mouse-user will hover over the bottom-right corner of cell B2 until the cursor becomes a cross-hair, and drag it down as far as is needed. The expert mouse-user will wait for the cross-hair and double-click, achieving the same result.

But the expert keyboard user will

  • Hit CTRL+C to copy the contents of B2
  • Hit Left, taking her to cell A2
  • Hit CTRL+Down, taking her to the bottommost postcode in column A, cell A2000, say
  • Hit Right, taking her to B2000
  • Hit CTRL+Shift+Up, highlighting cells B2:B2000
  • Hit Enter or CTRL+V to paste the formula into the entire column.

Although it's written above as six bullets, it takes under a second to achieve once you've done it half-a-dozen times.

Give the shortcuts a go. Try to accomplish what you need to using them. And before you know it, they'll become your default, and you'll be zipping around the sheet like there's no tomorrow.

And if that's not enough, below are some other useful navigational tools:

  • CTRL+Page Up/Page Down: this moves between the tabs of a workbook
  • Shift+CTRL+Page Up/Page Down: this moves between the table of a workbook, selecting each one in between. Group selection of worksheets is useful if you want to do a single task to multiple sheets. But be careful. It's easy to get into a muddle mistakenly thinking you've only got one sheet highlighted
  • CTRL+Tab: similar to Shift+Tab, this toggles between the worksheets you've got open within the instance of Excel.

Happy navigating!

This entry was posted in How to and tagged , . Bookmark the permalink.

2 Responses to Navigation: Zipping around the worksheet

  1. Felucha says:

    I used your info to choose a table in a worksheet macro. I save this in a Personal file, but when I use in on the next day's daily report, which is larger it does not function. Excel assigns cell names to the rightmost, bottommost corner of my table and because the table is larger the next day, it is out of range. Can I make it generic, having the macro go to the bottom right corner of populated cells without having a cell assignment, so I can use the macros in daily worksheets? Thanks

Comments are closed.