Wednesday, November 16, 2005

Letting Go The Mouse - [2] Using the END key to select blocks

In the first post of this series I talked about how to use the SHIFT key with the arrow keys as an alternative to holding down the left mouse button and dragging to select cells.

Today I want to show you how to use the END key as a means to making that work a lot faster.

The END key is a toggle. Click it once and its on, click again and its off. It is easy to see which mode its in (on or off) in Excel because the status bar has an indicator next to the CAPS, NUM and SCRL indicators (bottom right corner).
Note the END key will not work as a toggle if the SCROLL LOCK is on so make sure it is off.

The END key allows you to quickly navigate through blocks of cells that are either empty (blank) or full (have data in them).

Lets assume we have a spreadsheet that has data in every cell in rows 1 through 5 and from column A to column Z and then blank cells through to column AZ then from column BA to BZ it is filled with data again.

If we are in Cell A1 and we want to select the data in the first block (A1:Z5) then we could use F5 and special and select the current region. But it is easier to hold down the SHIFT key, tap the END key and then tap the right arrow. This will select cells A1:Z1. Keep the SHIFT key depressed and tap the END key and the down arrow key. You should end up with the cells A1:Z5 selected. A grand total of 5 keystrokes.

Ok deselect by clicking on Cell A1 again (or using CTRL HOME).

Now lets see the easy way to select the cells BZ1:BZ5.
From cell A1 we know that there is a contiguous line of cells out to column Z then blank to column BA which has data in it.
Simply tap END then the right arrow key (no SHIFT this time). You should end up on Z1. Now do it again- tap END and right arrow. You should end up on Cell BA1. Notice that each time you hit something else when the END key is turned on it turns it off again.

To select this block do the same as for the first block.
Hold down the SHIFT key, tap the END key and then tap the right arrow. This will select cells BA1:BZ1. Keep the SHIFT key depressed and tap the END key and the down arrow key. You should end up with the cells BA1:BZ5 selected.

The END key becomes a powerful modifier to moving around a spreadsheet using the keyboard arrows. It soon becomes much quicker to use the keyboard to navigate around a spreadsheet then to use the mouse to scroll.


Posted by: Jethro | 10:47 PM |