MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

Knowing how to select cells, rows, and columns in Excel is fundamental to using the program to its fullest potential. As you will see, there are many more ways to use selection techniques to isolate particular types of data, formats, objects, and even blank cells. Even though some of this information may seem basic, you'll probably encounter a few tips that will make your spreadsheet life a little easier:

• Before you can work with a cell or range, you must select it, and when you do, it becomes active.

• The reference of the active cell appears in the Name box at the left end of the formula bar.

• Only one cell can be active at a time, but you can select ranges of cells, and when you do, the active cell is in the upper-left corner of the selected range.

• Select all cells on a worksheet by clicking the Select All box located in the upper-left corner of your worksheet, where the column and row headings intersect.

For more about selection, read on.

Selecting with the Mouse

To select a range of cells, drag the mouse over the range. Alternatively, you can extend using two diagonal corners of the range you want to select. Click a cell at one corner of the range, and then press and hold the Shift key while clicking the cell diagonal to the first cell. For example, to extend the selection A1:B5 so it becomes A1:C10, hold down the Shift key, and click cell C10. When you need to select a large range, this technique is more efficient than dragging the mouse across the entire selection.

Zooming to Select Large Worksheet Areas

It's impossible to see an entire workbook on the screen. Knowing that, what do you do if you need to select a gigantic range of cells? You can drag the pointer past the window border and wait for the automatic scrolling to get you where you need to go, but this method can be frustrating if you have trouble managing the scrolling speed and keep overshooting the target.

A better method is to use the Zoom slider to get a bird's-eye view of the worksheet, as shown in Figure 6-2. Drag the Zoom slider at the bottom of the screen to the percentage you want. You can also click the Zoom percentage indicator adjacent to the slider to open the Zoom dialog box for more zooming options. The Zoom feature is limited to a range from 10 through 400 percent.

Figure 6-2: Use the Zoom slider or the Zoom dialog box to view large areas of a worksheet for easier selection.

Selecting Columns, Rows, and Multiple Areas

Multiple-area selections (also known as nonadjacent or noncontiguous selections) are selected cell ranges that do not encompass a single rectangular area, as shown in Figure 6-3. To select multiple-area ranges with the mouse, press the Ctrl key, and drag through each range you want to select. The first cell you click in the last range you select becomes the active cell. As you can see in Figure 6-3, cell G6 is the active cell.

Figure 6-3: Hold down the Ctrl key and drag to select multiple-area ranges with the mouse.

To select an entire column or row, click the column or row heading. In other words, to select cells B1 through B1048576, click the heading for column B. The first visible cell in the column becomes the active cell, so if the first row visible on your screen is row 1048557, then cell B1048557 becomes active when you click the heading for column B, even though all the other cells in the column are selected. To select more than one adjacent column or row at a time, drag through the column or row headings, or click the heading at one end of the range, press Shift, and then click the heading at the other end. To select nonadjacent columns or rows, as shown in Figure 6-4, hold down Ctrl, and click each heading or drag through adjacent headings you want to select.

Figure 6-4: Select entire columns and rows by clicking their headings, or hold down the Ctrl key while clicking to select nonadjacent rows and columns.

Use the following methods to select with the keyboard:

• To select an entire column with the keyboard, select any cell in the column, and press Ctrl+Spacebar.

• To select an entire row with the keyboard, select any cell in the row, and press Shift+Spacebar.

• To select several entire adjacent columns or rows with the keyboard, select any cell range that includes cells in each of the columns or rows, and then press Ctrl+Spacebar or Shift+Spacebar, respectively. For example, to select columns B, C, and D, select B4:D4 (or any range that includes cells in these three columns), and then press Ctrl+Spacebar.

• To select the entire worksheet with the keyboard, press Ctrl+Shift+Spacebar.

Selecting Regions

If you hold down the Shift key as you double-click the edge of an active cell's border, Excel selects all the cells from the current selection to the next edge of the region in that direction. The cell from which you start the selection process remains the active cell.

Using the Find & Select Commands

At the right end of the Home tab on the Ribbon, the Find & Select menu displays several helpful selection commands, as shown in Figure 6-5. In the middle of the menu are five commands that used to be buried in dialog boxes and have been promoted in Excel because of their widespread use: Formulas, Comments, Conditional Formatting, Constants, and Data Validation.

Figure 6-5: Use the Find & Select commands to zero in on specific items.

In Figure 6-5, we used the Formulas command to select all the formulas on the worksheet, which are highlighted by multiple selection rectangles. You can use these specialized selection commands for various purposes such as applying specific formatting to formulas and constants or auditing worksheets for errant conditional formatting or data validation cells.

The two Go To commands are also helpful for finding and selecting a variety of worksheet elements. To quickly move to and select a cell or a range of cells, click Go To (or press F5) to open the Go To dialog box; then type a cell reference, range reference, or defined range name in the Reference box, and press Enter. You can also use Go To to extend a selection. For example, to select A1:Z100, you can click A1, open the Go To dialog box, type Z100, and then press Shift+Enter.

For more about selecting, see "Finding and Replacing Stuff" on page 221 and "Selecting and Grouping Objects" on page 386. For more information about defined range names and references, see "Naming Cells and Cell Ranges" on page 441 and "Using Cell References in Formulas" on page 428.

To move to another worksheet in the same workbook, open the Go To dialog box, and type the name of the worksheet, followed by an exclamation point and a cell name or reference. For example, to go to cell D5 on a worksheet called Sheet2, type Sheet2!D5. To move to another worksheet in another open workbook, open the Go To dialog box, and type the name of the workbook in brackets, followed by the name of the worksheet, an exclamation point, and a cell name or reference. For example, to go to cell D5 on a worksheet called Sheet2 in an open workbook called Sales.xlsx, type [Sales.xlsx]Sheet2!D5.

Excel keeps track of the last four locations from which you used the Go To command and lists them in the Go To dialog box. You can use this list to move among these locations in your worksheet. This is handy when you're working on a large worksheet or jumping around among multiple locations and worksheets in a workbook. Figure 6-6 shows the Go To dialog box displaying four previous locations.

Figure 6-6: The Go To and Go To Special dialog boxes are your selection transporters.

Selecting with Go To Special

When you click the Special button in the Go To dialog box (or the Go To Special command on the Find & Select menu), the dialog box shown on the right of Figure 6-6 opens, presenting additional selection options. You can think of the Go To Special dialog box as "Select Special," because you can use it to quickly find and select cells that meet certain specifications.

After you specify one of the Go To Special options and click OK, Excel highlights the cell or cells that match the criteria. With a few exceptions, if you select a range of cells before you open the Go To Special dialog box, Excel searches only the selected range; if the current selection is a single cell or one or more graphic objects, Excel searches the entire active worksheet. The following are guidelines for using the Go To Special options:

• Constants refers to any cell containing static data such as numbers or text, but not formulas.

• Current Region is handy when you're working in a large, complex worksheet and need to select blocks of cells. (Recall that a region is defined as a rectangular block of cells bounded by blank rows, blank columns, or worksheet borders.)

• Current Array selects all the cells in an array if the selected cell is part of an array range.

• Last Cell selects the cell in the lower-right corner of the range that encompasses all the cells that contain data, comments, or formats. When you select East Cell, Excel finds the last cell in the active area of the worksheet, not the lower-right corner of the current selection.

• Visible Cells Only excludes from the current selection any cells in hidden rows or columns.

• Objects selects all graphic objects in your worksheet, regardless of the current selection.

• Conditional Formats selects only those cells that have conditional formatting applied. Or you can click the Home tab, and click the Conditional Formatting command on the Find & Select menu.

• Data Validation using the All option selects all cells to which data validation has been applied; Data Validation using the Same option selects only cells with the same validation settings as the currently selected cell. You can also click the Home tab, and click the Data Validation command on the Find & Select menu, which uses the All option.

For more information about graphic objects, see Chapter 10, "Creating Spiffy Graphics." For more information about conditional formatting, see "Formatting Conditionally" on page 284.

Some of the Go To Special options-such as Formulas, Comments, Precedents, and Dependents-might cause Excel to select multiple nonadjacent cell ranges. After you make the selection, you might want to change the active cell without losing the multi-selection. Or you might want to type entries into multiple ranges you select so you don't have to reach for the mouse. Either way, you can move between selected cells. For example, the worksheet shown here has multiple ranges selected:

To move the active cell through these ranges without losing the selection, press Enter to move down or to the right one cell at a time; press Shift+Enter to move up or to the left one cell at a time. Or press Tab to move to the right or down; press Shift+Tab to move to the left or up. Pretty cool, eh? You might not think so until you have a lot of noncontiguous, noncolumnar data entry to do, but trust us. So, in the previous worksheet, if you press Enter until cell A17 is selected, the next time you press Enter the selection jumps to the beginning of the next selected region, in this case cell A1. Subsequently pressing Enter selects A2, then B1, then B2, and so on, until the end of the region-cell F2. Then the selection jumps to the next region, cell B4.

Selecting Precedents and Dependents

The Precedents and Dependents options in the Go To Special dialog box let you find cells that are used by a formula or to find cells on which a formula depends. To use the Precedents and Dependents options, first select the cell whose precedents or dependents you want to select. When searching for precedents or dependents, Excel always searches the entire worksheet. When you select the Precedents or Dependents option, Excel activates the Direct Only and All Levels options:

• Direct Only finds only those cells that directly refer to or that directly depend on the active cell.

• All Levels locates direct precedents and dependents plus those cells indirectly related to the active cell.

Depending on the task, you might find the built-in auditing features of Excel to be just the trick. On the Formulas tab on the Ribbon, the Formula Auditing group offers the Track Precedents and Track Dependents buttons. Rather than selecting all such cells like the Go To Special command, clicking these buttons draws arrows showing path and direction in relation to the selected cell. For more information, see "Auditing and Documenting Worksheets" on page 241.

If you do a lot of "going to," you'll want to learn a few of these keyboard shortcuts, which will speed things up considerably:

• Press Ctrl+Shift+* to select the current region.

• Press Ctrl+/ to select the current array.

• Press Alt+; to select the visible cells only.

• Press Ctrl+[ to select the direct precedents.

• Press Ctrl+Shift+{ to select all the precedents.

• Press Ctrl+] to select the direct dependents.

• Press Ctrl+Shift+} to select all the dependents.

• Press Ctrl+\ to select row differences.

• Press Ctrl+Shift+| to select column differences.

Selecting Row or Column Differences

The Row Differences and Column Differences options in the Go To Special dialog box compare the entries in a range of cells to spot potential inconsistencies. To use these debugging options, select the range before displaying the Go To Special dialog box. The position of the active cell in your selection determines which cells Excel uses to make its comparisons. When searching for row differences, Excel compares the cells in the selection with the cells in the same column as the active cell. When searching for column differences, Excel compares the cells in the selection with the cells in the same row as the active cell.

In addition to other variations, the Row Differences and Column Differences options look for differences in references and select those cells that don't conform to the comparison cell. They also verify that all the cells in the selected range contain the same type of entries. For example, if the comparison cell contains a SUM function, Excel flags any cells that contain a function, formula, or value other than SUM. If the comparison cell contains a constant text or numeric value, Excel flags any cells in the selected range that don't match the comparison value. The options, however, are not case-sensitive.