Read an Excerpt
[Figures are not included in this sample chapter]
Special Edition Using Microsoft Excel 2000
- 3 -
Selecting and Naming Cells and Ranges
In this chapter
Selecting Columns and Rows
Selecting Noncontiguous Ranges
Selecting Ranges on Grouped Worksheets
Naming Ranges for Fast Access
Excel in Practice
Afterdata entry, selecting worksheet content is probably the task most frequentlyperformed in Excel. Selecting cells and ranges tells Excel where you are andwhat you're doing, and is an integral step in most formatting, calculation, andediting procedures. To select an individual cell, click on it or use any of thekeyboard shortcuts shown in Table 3.1 for worksheet navigation to move to thedesired cell.
TABLE 3.1. Keyboard Shortcuts for Selecting Individual Cells
The cell below the current cell
The cell to the right of the current cell
The cell to the left of the current cell
The first cell in the current row
When a cell is active (the current cell), it appears with athick border around it, as shown in Figure 3.1. This border, called the cellpointer, indicates the active cell. The Name box in the Formula bardisplays the address of the active cell.
FIGURE 3.1 Select a cell in preparation for entering or editing content, or to format the existing content of the cell.
To select a range of cells, click and drag through them, dragging up, down,left, right, or a combination of these directions to select the desired rangeof cells. Release the mouse when the desired block of cells (two or more) ishighlighted. Figure 3.2 shows a selected range of cells. The range in thisexample would have the address C7:H17--the colon representing the wordthrough. The active cell is cell C7, which indicates that the range wasselected from upper-left to lower-right; the cell pointer remains in the firstcell selected. Notice that Excel highlights the row and column headings for theselected range, boldfacing the heading letter(s) or number(s) and displayingthe headings as if they were buttons, with a "pushed-out" appearance.
As you select a contiguous range--a range in which all the cells areadjacent to one another--Excel displays information in the Name box to matchthe selection. In this case, the designation 11R x 6C indicates thatthe selection is eleven rows long and six columns wide. As soon as you completethe selection, the Name box goes back to displaying the address of the currentcell.
FIGURE 3.2 The starting cell remains white. Other cells in the selected range show a transparent color (you can see the selected cells through the color).
TIP: While this chapter's title lists them as two different things, both single cells and ranges of cells are considered ranges by Excel. You'll find this interchangeable terminology and concept in many areas, such as named ranges, covered later in this chapter.
Selected cells are vulnerable. If you accidentally press the spacebar ortype a character while a cell or range of cells is selected, the content of theselection (in the one white cell of the range) will be deleted, replaced with aspace or the character you typed. If this occurs, press Esc to cancel the entryif the cell is still selected. If you already accepted the entry, use the Undofe ature to restore the original entry.
When a range of blank cells is selected, enter content by typing in thefirst cell and then pressing Enter. You automatically are moved to the nextcell in the range. Continue typing content in each cell, and pressing Enterafter each one. This procedure changes the normal effect of pressing the Enterkey--normally the Enter key takes you to the cell below the current cell--andallows you to confine your edits to one area. If you select a block or range ofcells for entry, using this method also will enable you to type entries withoutlooking back at the worksheet to see where you are while typing. This isespecially useful if you're referring to a printed document or written notes asa source for the entries.
TIP: If you have selected a range that includes multiple rows, each press of the Enter key moves the cell pointer down until you reach the last row within the selection. Pressing Enter again moves the active cell to the top cell in the next column to the right. Use Shift+Enter to move backward in a selection.
Alternatively, you can move left to right in a selection by pressing the Tab key. Use Shift+Tab to move right to left.
You also can select a range of cells by using the keyboard. To select ablock of cells, follow these steps:
1. Use the most appropriate keyboard shortcut or click in the first cellin the desired range.
2. Press and hold down the Shift key.
3. Use the arrow keys to select cells to the right of, left of, above,or below the starting cell. To select larger blocks of text, use PgUp/PgDn orAlt+PgUp/Alt+PgDn.
4. When the desired block of cells is selected, release the Shiftkey.
TIP: It may be easier to use the Shift key and arrow keys to select a range when you're in the middle of a long data entry session--it saves you the time of using the mouse and dragging through cells when your hands are already on the keyboard.
Selectinga range requires highlighting a block of cells. You can drag to select all theentries in a column or row, but in many cases it's more efficient to select theentire row (all 256 cells) or column (all 65,536 cells), whether you're usingthe entire range of cells or not. To drag through an entire row or column couldtake several minutes, just to scroll to the ends of the worksheet. It's mucheasier to click the column letter or row number for the desired column or row,as shown in Figure 3.3.
FIGURE 3.3 Click the column letter to select all 65,536 cells in the column.
Notice that the color of the column or row heading is reversed when theentire column is selected. (This behavior may affect or be affected bybackground fills or patterns in the cells, however.)
If your worksheet includes background fills or patterns, the selectioncolor may look unusual. See "Color Selection Looks Odd" in the Troubleshootingsection at the end of this chapter.
You can select multiple columns or rows by dragging through the columnletters or row numbers, as shown in Figure 3.4.
Formatting an entire column or row tends to bloat the file size of theworkbook because Excel must st ore the formatting information for every cell youselected, regardless of whether you ever enter any content in that cell. Applyglobal formatting only when necessary.
FIGURE 3.4 Select a series of columns or rows to copy, move, format, or enter content for the entire selection.
Table 3.2 lists keyboard shortcuts you can use to select rows or columns ina worksheet. The Result column in the table assumes that the active cell is inthe row or column you want to select before you use these shortcuts.
TABLE 3.2. Row and Column Selection with the Keyboard
Selects the entire column.
Selects the entire row.
Selects the entire worksheet.
Youcan select the entire worksheet by clicking the Select All button (theblank gray button at the intersection of the column letters and row numbers)just below the Name box.
Whenedits, formatting changes, or deletions you want to make are in multipleseparate areas of the worksheet, you can select these noncontiguousranges by adding the Ctrl key to the selection procedure.
To select multiple ranges that don't share adjacent cells, follow thesesteps:
1. Select the first cell or range of cells.
2. Release the mouse, and press and hold down the Ctrl key.
3. Using the mouse again, select the next cell or range of cells.
4. Repeat steps 1 through 3 until you've selected all the ranges youneed. Figure 3.5 shows a worksheet with three noncontiguous ranges selected:B6:B17, B6:H6, and C18:H18.
FIGURE 3.5 Select the column and row labels, as well as the row of totals, so that you can apply formatting to all the selected cells at once.
After making these noncontiguous selections, you can delete or format allthe selected areas at once.
You can copy noncontiguous selections to the Clipboard only if all theselections are the same size and shape. For example, in Figure 3.5, you canselect B7:E7, B11:E11, and B14:E14, and then copy that entire noncontiguousselection.
You'll notice that when you select the second range of cells, the activecell (starting cell) switches from the starting cell in the first range to thestarting cell in the second range. The current cell will continue to move toeach subsequent range you add to the group of selected ranges, ending up in thefirst cell of the last selected range.
Inaddition to being able to select contiguous or noncontiguous ranges on a singleworksheet, you might occasionally want to select the same ranges on multipleworksheets within a workbook. Why might you want to select cells/ranges fromseveral sheets at once? The following list offers some suggestions:
To speed up and simplify formatting changes. Want to make alltitles on all sheets look the same? Select them all at once and then apply theformatting.
To expedite editing a series of cells. You know that a selectedrange of cells can be edited, cell by cell, with the Enter key. What if youwant to make changes to cell content on several sheets? Go through the workbookand select all the ranges, so that as you edit and press Enter, you're takenautomatically to the next cell in the series to be edited. Keep in mind,though, that grouped worksheets have the same selections on every sheet; youcan't select one set of ranges on one worksheet and a different set on anotherworksheet.
To make a quick deletion. Assume that your worksheets includemultiple references to a sales rep whom you no longer employ or a product youno longer sell. Select the cells and ranges that refer to this person orproduct, and then press Delete. All cell content in those cells--in allworksheets included in the group--will be removed.
Theprocess of selecting worksheets is called grouping. It's important tonote that you should group worksheets for formatting, data entry, or deletionsonly if all the worksheets are structured identically. For example, if yougroup Sheet1 and Sheet2, select the range B2:B7 on Sheet1, and then switch t oSheet2, you'll find that the same cell coordinates are selected on Sheet2. Ifyou add a range to the selection while on Sheet2, the added selection will beechoed on Sheet1. For this reason, if the sheets aren't structuredidentically (everything in the same place on all sheets), restrict use of thisfeature to editing content--you can always press Enter to skip extra cells inthe selection.
When working with grouped worksheets, you can select the ranges first andthen group the worksheets, or group the worksheets and then make yourselections. After all ranges are selected and the worksheets are grouped, youcan make content changes, apply formatting, and so on.
FIGURE 3.6 Grouped sheets remain grouped until you ungroup them. Any changes are applied to all the grouped sheets.
To group worksheets, start by activating the first worksheet you want in thegroup. Then hold down the Ctrl key while clicking the sheet tab for eachadditional worksheet you want to include in the group. (If you want to includea set of contiguous sheets, you can display the first sheet, hold down theShift key, and click the sheet tab for the last sheet.) The sheet tabs forgrouped worksheets turn white, and a [Group] indicator appears in the title barof the workbook window. Grouped worksheets remain grouped until you click thesheet tab for an ungrouped sheet or right-click the sheet tab for one of thegrouped sheets and select Ungroup Sheets from the resulting contextmenu.
After sheets are grouped, you can switch freely between the grouped sheetsby clicking the sheet tab for the sheet you want to see. Notice that if youselect a new range on the active sheet, it's simultaneously selec ted on allother sheets in the group (see Figure 3.7).
FIGURE 3.7 Selecting cells and ranges on grouped sheets makes it easy to edit and format cells within identical sheets.
If you group a sheet you didn't want, simply Ctrl+click the sheet tab toremove it from the group.
Afteryou enter some or all of a worksheet's content, you may find it easier to referto cells and blocks of selected cells by name rather than cell address. Just asit's easier to find an employee by asking the receptionist for him or her byname than to comb the building on foot, looking in each cube or office, sofinding cells and ranges by name is faster and easier than trying to find andremember cell addresses. Excel gives you the capacity to name individual cellsor cell ranges so that you can find data by a logical name, and refer to thecells ranges in formulas, as described in Chapter 12, "Working with NamedRanges."
It's a good idea to go through large or complex worksheets and name all thekey cells or ranges to which you think you'll go often. Don't wait until youwish you had created a name for an often-accessed cell!
To name a cell or range of cells, follow these steps:
1. Select the cell(s), and choose Insert, Name,Define. This opens the Define Name dialog box, as shown in Figure3.8.
FIGURE 3.8 Type the range name and click Add to insert several ranges in one session, or OK if you want to add only one range name.
2. In the Names in Workbook text box, type a name for theselected cell(s).
3. Click OK.
You can type a range of cells or a singlecell address into the Refers To box. This technique enables you to enterseveral ranges, clicking Add after naming each one, without having torepeatedly close and then reopen the dialog box after selecting cells or rangeswith the mouse.
A quick method for creating named ranges is to select the cell/range, and, using the Name box on the Formula bar, highlight the displayed cell address and replace it by typing a name for the cell/range. Press Enter to create the name
Range names cannot begin with a number, resemble a cell address (such asFQ1999), or contain spaces or punctuation. You can use underscores in lieu ofspaces, such as First_Quarter_1999. Although Excel allows range names tocontain hundreds of characters, sticking to short names makes them easier toremember and use in formulas.
Getting an error message when you type a range name? See "Range NamingErrors" in the Troubleshooting section at the end of this chapter.
When selecting cells to be named, they needn't be a contiguous range--use the Ctrl key to select several individual noncontiguous cells or ranges, and then give the group of selected cells a single name (see Figure 3.9). This could be used to find and sum various sales totals throughout the worksheet, for example, giving them one name, such as PAC Q's First Month.
FIGURE 3.9 Select single cells or random ranges throughout the worksheet and then apply one name to the entire selection.
After you've added a named range, you can access it by opening the Name box on the Formula bar, or by pressing F3 t o open the Paste Name dialog box. Figure 3.10 shows a series of named ranges.
FIGURE 3.10 Select a range name from the list, and the cell or range it represents is immediately selected.
To remove a named range, choose Insert, Name, Define,and select the named range from the list. Click the Delete button, andthen click OK.
WhenI select cells or ranges, the background color of my worksheet seems to gohaywire.
If your worksheet includes colored background fills or patterns, a rangeselection may display in an odd color. The displayed color change is merely thetemporary result of selecting the cells. Clicking away from the selected rangeto deselect it will display the cells with their normal formatting.
Afterselecting several noncontiguous ranges, I try to copy them to the Clipboard butget the message That command cannot be used on multipleselections.
Excel can copy noncontiguous ranges only if they're the same size and shape.If you need to copy all the information in the various selections but nothingbetween, and you can't arrange the worksheet to make that data contiguous, trythis trick: Apply a background fill or special format to the cells you don'twant (a bright yellow or green fill works nicely). Then copy a contiguous blockcontaining all the cells you do want as well as those you don't, and paste thecopy into place in its target position. The cells that then need to be clearedare easily visible because of the fill or special format . Select those cellsand choose Edit, Clear, All.
WhenI type the range name I want to create in the Name box, Excel displays themessage You must enter a valid reference you want to go to, or type avalid name for the selection.
You're probably attempting to use a space in the range name or begin the range name with a number. Unfortunately, names such as 1999 Disbursements are not allowed. For this type of name, put the year at the end, and replace the space with an underscore: Disbursements_1999.
Excel's tools for selecting and naming ranges can be used to significantly reduce your setup time. Figure 3.11 shows a set of identical worksheets, created by grouping the sheets and selecting sections of their content. Simple formats were applied once, and that one action formatted all the sheets. This type of uniformity decreases the learning curve for users who have to work with the worksheets and makes it easier for you to build worksheets quickly and consistently.
FIGURE 3.11 These sheets were grouped, and selections in all three sheets were simultaneously formatted to achieve a consistent look throughout the workbook.