Chapter 6: How to Work a Worksheet

In this chapter, we'll cover the basics, including moving around within the massive worksheet grid, entering and selecting data, and working with multiple worksheets and protecting their contents. You probably already know many of these techniques, but here we'll also present alternative methods. You might find a better, or faster, way to do something you do frequently. You'll find that Microsoft Office Excel 2007 offers a lot of alternatives.

Moving Around Regions

You already know how to use scroll bars and the Page Up and Page Down keys. Office Excel 2007 offers many other ways to get around, including some unique tricks you'll find only in Excel 2007.

A region is a range of cell entries bounded by blank cells or column and row headings. In Figure 6-1, the range A3:E7 is a region, as are the ranges G3:H7, A9:E10, and G9:H10. (Strictly speaking, cell A1 is also a one-cell region because no adjoining cells contain entries.) For example, cell H10 is within a region, even though it's empty. The active area of the worksheet is the selection rectangle that encompasses all regions-that is, all the filled cells in the active worksheet-which in Figure 6-1 is A1:H10.

Figure 6-1: The four blocks of cells on this worksheet are separate regions.

The techniques used to navigate regions are especially helpful if you typically work with large tables of data. Getting to the bottom row of a 500-row table is easier when you don't have to use the scroll bars. Read on to find out how.

Note 

The small square in the lower-right corner of the active cell is the fill handle. If the fill handle isn't visible on your screen, it means it isn't turned on. To turn it on, click the Microsoft Office Button, click Excel Options, click the Advanced category, and select the Enable Fill Handle And Cell Drag-And-Drop check box.

Navigating Regions with the Keyboard

To move between the edges of regions, hold down the Ctrl key, and then press any of the arrow keys. For example, in Figure 6-1, cell A3 is the active cell; press Ctrl+Right Arrow to activate cell E3.

If a blank cell is active when you press Ctrl and an arrow key, Excel moves to the first filled cell in that direction or to the last available cell on the worksheet if it doesn't find any filled cells in that direction. In Figure 6-1, for example, if cell F3 is active when you press Ctrl+Right Arrow, the selection moves to cell G3; if Cell H3 is active, pressing Ctrl+Right Arrow activates cell XFD3-the last available cell in row A3. Just press Ctrl+Left Arrow to return to cell H3.

Navigating Regions with the Mouse

When you move the pointer over the edge of the active cell's border, the pointer changes from a plus sign to an arrow. With the arrow pointer visible, you can double-click any edge of the border to change the active cell to the cell on the edge of the current region in that direction-it is the same as pressing Ctrl and an arrow key in that direction. For example, if you double-click the bottom edge of the active cell in Figure 6-1, Excel selects cell A7.

The left side of the status bar displays the mode indicators in Table 6-1 when the corresponding keyboard mode is active.

Table 6-1: Keyboard Modes
Open table as spreadsheet

Mode	Description
Extend Selection	Press F8 to turn on this mode, which you use to extend the current selection using the keyboard. (Make sure Scroll Lock is off.) This is the keyboard equivalent of selecting cells by dragging the mouse. Furthermore, unlike holding down the Shift key and pressing an arrow key, you can extend the range by pressing only one key at a time. Press F8 again to turn off Extend Selection mode.
Add To Selection	Press Shift+F8 to add more cells to the current selection using the keyboard. The cells need not be adjacent; after pressing Shift+F8, click any cell or drag through any range to add it to the selection. This is the keyboard equivalent of holding down Ctrl and selecting additional cells with the mouse.
Num Lock	Keeps your keypad in numeric entry mode. This is turned on by default, but its status is not usually displayed in the status bar. However, you can make it so by right-clicking the status bar anywhere and clicking Num Lock.
Fixed Decimal	To add a decimal point to the numeric entries in the current selection, click the Microsoft Office Button, click Excel Options, select the Advanced category, and select the Automatically Insert A Decimal Point check box in the Editing Options group. Excel places the decimal point in the location you specify in the Places box. For example, when you turn on Fixed Decimal mode, specify two decimal places, and type the number 12345 in a cell, the value 123.45 appears in the cell after you press Enter. Existing cell entries are not affected unless you edit them. To turn off Fixed Decimal mode, return to the Advanced category in the Excel Options dialog box, and clear the Automatically Insert A Decimal Point check box.
Caps Lock	Press the Caps Lock key to type text in capital letters. (This does not affect number and symbol keys.) To turn off Caps Lock mode, press the Caps Lock key again. The status of this mode does not usually display in the status bar, but you can make it so. Right-click the status bar anywhere, and click Caps Lock.
Scroll Lock	Press Scroll Lock to use the Page Up, Page Down, and arrow keys to move the viewed portion of the window without moving the active cell. When Scroll Lock mode is off, the active cell moves one page at a time when you press Page Up or Page Down and moves one cell at a time when you press one of the arrow keys. To turn off Scroll Lock mode, press the Scroll Lock key again.
End Mode	Press the End key, and then press an arrow key to move the selection to the edge of the region in that direction or to the last worksheet cell in that direction. This mode functions like holding down Ctrl and pressing an arrow key, except you need to press only one key at a time. To turn off End mode, press the End key again. End mode is also turned off after you press one of the arrow keys.
Overtype Mode	Click the formula bar or double-click a cell and press the Insert key to turn on Overtype mode (formerly known as Overwrite mode). Usually, new characters you type in the formula bar are inserted between existing characters. With Overtype mode turned on, the characters you type replace any existing characters to the right of the insertion point. Overtype mode turns off when you press Insert again or when you press Enter or one of the arrow keys to lock in the cell entry.

Navigating with Special Keys

Table 6-2 shows how you can use the Home and End keys alone and in conjunction with other keys to make selections and to move around a worksheet.

Table 6-2: Keyboard Shortcuts for Navigation
Open table as spreadsheet

Press	To
Home	Move to the first cell in the current row.
Ctrl + Home	Move to cell A1.
Ctrl + End	Move to the last cell in the last column in the active area. For example, in Figure 6-1, pressing Ctrl + End selects cell H10.
End	Start End mode. Then press an arrow key to move around by cell region.
Scroll Lock+Home	Move to the first cell within the current window.
Scroll Lock+End	Move to the last cell within the current window.