IN THIS CHAPTER
Most of the work you do in Excel involves cells and ranges. Understanding how best to manipulate cells and ranges will save you time and effort. This chapter discusses a variety of techniques that are essential for Excel users.
A cell is a single element in a worksheet that can hold a value, some text, or a formula. A cell is identified by its address, which consists of its column letter and row number. For example, cell D9 is the cell in the fourth column and the ninth row.
A group of cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon.
Here are some examples of range addresses:
C24 |
A range that consists of a single cell. |
A1:B1 |
Two cells that occupy one row and two columns. |
A1:A100 |
100 cells in column A. |
A1:D4 |
16 cells (four rows by four columns). |
C1:C1048576 |
An entire column of cells; this range also can be expressed as C:C. |
A6:XFD6 |
An entire row of cells; this range also can be expressed as 6:6. |
A1:XFD1048576 |
All cells in a worksheet. This range also can be expressed as either A:XFD or 1:1048576. |
To perform an operation on a range of cells in a worksheet, you must first select the range. For example, if you want to make the text bold for a range of cells, you must select the range and then choose Home Font Bold (or press Ctrl+B).
When you select a range, the cells appear highlighted. The exception is the active cell, which remains its normal color. Figure 4.1 shows an example of a selected range (A5:D7) in a worksheet. Cell A5, the active cell, is selected but not highlighted.
You can select a range in several ways:
Often, you'll need to select an entire row or column. For example, you may want to apply the same numeric format or the same alignment options to an entire row or column. You can select entire rows and columns in much the same manner as you select ranges:
Most of the time, the ranges that you select are contiguous — a single rectangle of cells. Excel also enables you to work with noncontiguous ranges, which consist of two or more ranges (or single cells) that aren't necessarily adjacent to each other. Selecting noncontiguous ranges is also known as a multiple selection. If you want to apply the same formatting to cells in different areas of your worksheet, one approach is to make a multiple selection. When the appropriate cells or ranges are selected, the formatting that you select is applied to them all. Figure 4.2 shows a noncontiguous range selected in a worksheet. Three ranges are selected: A6:F6. A13:F14, and A17:F19.
You can select a noncontiguous range in several ways:
In addition to two-dimensional ranges on a single worksheet, ranges can extend across multiple worksheets to be three-dimensional ranges.
Suppose that you have a workbook set up to track budgets. One approach is to use a separate worksheet for each department, making it easy to organize the data. You can click a sheet tab to view the information for a particular department.
Figure 4.3 shows a simplified example. The workbook has four sheets: Totals, Operations, Marketing, and Manufacturing. The sheets are laid out identically. The only difference is the values. The Totals sheet contains formulas that compute the sum of the corresponding items in the three departmental worksheets.
Assume that you want to apply formatting to the sheets — for example, make the column headings bold with background shading. One (albeit not-so-efficient) approach is to format the cells in each worksheet separately. A better technique is to select a multisheet range and format the cells in all the sheets simultaneously. The following is a step-by-step example of multisheet formatting using the workbook shown in Figure 4.3:
[Group]
to remind you that you've selected a group of sheets and that you're in Group mode.
[Group]
is no longer displayed in the title bar.When a workbook is in Group mode, any changes that you make to cells in one worksheet also apply to the corresponding cells in all the other grouped worksheets. You can use this to your advantage when you want to set up a group of identical worksheets because any labels, data, formatting, or formulas you enter are automatically added to the same cells in all the grouped worksheets.
In general, selecting a multisheet range is a simple two-step process: select the range in one sheet, and then select the worksheets to include in the range. To select a group of contiguous worksheets, you can press Shift and click the sheet tab of the last worksheet that you want to include in the selection. To select individual worksheets, press Ctrl and click the sheet tab of each worksheet that you want to select. If all the worksheets in a workbook aren't laid out the same, you can skip the sheets that you don't want to format. When you make the selection, the sheet tabs of the selected sheets display in bold with underlined text, and Excel displays [Group]
in the title bar.
As you use Excel, you may need to locate specific types of cells in your worksheets. For example, wouldn't it be handy to be able to locate every cell that contains a formula — or perhaps all the formula cells that depend on the active cell? Excel provides an easy way to locate these and many other special types of cells: select a range, and choose Home Editing Find & Select Go to Special to display the Go to Special dialog box, shown in Figure 4.5.
After you make your choice in the dialog box, Excel selects the qualifying subset of cells in the current selection. Often, this subset of cells is a multiple selection. If no cells qualify, Excel lets you know with the message No cells were found
.
Table 4.1 offers a description of the options available in the Go to Special dialog box. Some of the options are very useful.
Table 4.1 Go to Special Options
Option | What it does |
Comments | Selects the cells that contain a cell comment. |
Constants | Selects all nonempty cells that don't contain formulas. Use the check boxes under the Formulas option to choose which types of nonformula cells to include. |
Formulas | Selects cells that contain formulas. Qualify this by selecting the type of result: numbers, text, logical values (TRUE or FALSE ), or errors. |
Blanks | Selects all empty cells. If a single cell is selected when the dialog box displays, this option selects the empty cells in the used area of the worksheet. |
Current Region | Selects a rectangular range of cells around the active cell. This range is determined by surrounding blank rows and columns. You can also press Ctrl+Shift+*. |
Current Array | Selects the entire array. (See Chapter 17, “Introducing Array Formulas,” for more on arrays.) |
Objects | Selects all embedded objects on the worksheet, including charts and graphics. |
Row Differences | Analyzes the selection and selects cells that are different from other cells in each row. |
Column Differences | Analyzes the selection and selects the cells that are different from other cells in each column. |
Precedents | Selects cells that are referred to in the formulas in the active cell or selection (limited to the active sheet). You can select either direct precedents or precedents at any level. (See Chapter 31, “Making Your Worksheets Error Free,” for more information.) |
Dependents | Selects cells with formulas that refer to the active cell or selection (limited to the active sheet). You can select either direct dependents or dependents at any level. (See Chapter 31 for more information.) |
Last Cell | Selects the bottom-right cell in the worksheet that contains data or formatting. For this option, the entire worksheet is examined, even if a range is selected when the dialog box displays. |
Visible Cells Only | Selects only visible cells in the selection. This option is useful when dealing with a filtered list or a table. |
Conditional Formats | Selects cells that have a conditional format applied (by choosing Home Styles Conditional Formatting). The All option selects all such cells. The Same option selects only the cells that have the same conditional formatting as the active cell. |
Data Validation | Selects cells that are set up for data entry validation (by choosing Data Date Tools Data Validation). The All option selects all such cells. The Same option selects only the cells that have the same validation rules as the active cell. |
Another way to select cells is to choose Home Editing Find & Select Find (or press Ctrl+F), which allows you to select cells by their contents. The Find and Replace dialog box is shown in Figure 4.6. This figure illustrates additional options that are available when you click the Options button.
Enter the text that you're looking for; then click Find All. The dialog box expands to display all the cells that match your search criteria. For example, Figure 4.7 shows the dialog box after Excel has located all cells that contain the text widget. You can click an item in the list, and the screen will scroll so that you can view the cell in context. To select all the cells in the list, first select any single item in the list. Then press Ctrl+A to select them all.
The Find and Replace dialog box supports two wildcard characters:
? |
Matches any single character |
* |
Matches any number of characters |
Wildcard characters also work with values when the Match Entire Cell Contents option is selected. For example, searching for 3*
locates all cells that contain a value that begins with 3. Searching for 1?9
locates all three-digit entries that begin with 1 and end with 9. Searching for *00 locates values that end with two zeros.
If your searches don't seem to be working correctly, double-check these three options (which sometimes have a way of changing on their own):
smith
does not locate Smith
.Excel
doesn't locate a cell that contains Microsoft Excel
. When using wildcard characters, an exact match is not required.900
doesn't find a cell that contains 900
if that value is generated by a formula (unless the formula itself contains 900
).As you create a worksheet, you may find it necessary to copy or move information from one location to another. Excel makes copying or moving ranges of cells easy. Here are some common things you might do:
The primary difference between copying and moving a range is the effect of the operation on the source range. When you copy a range, the source range is unaffected. When you move a range, the contents are removed from the source range.
Copying or moving consists of two steps (although shortcut methods are available):
Because copying (or moving) is used so often, Excel provides many different methods. I discuss each method in the following sections. Copying and moving are similar operations, so I point out only important differences between the two.
Choosing Home Clipboard Copy transfers a copy of the selected cell or range to the Windows Clipboard and the Office Clipboard. After performing the copy part of this operation, select the cell that will hold the copy and choose Home Clipboard Paste.
Instead of choosing Home Clipboard Paste, you can just activate the destination cell and press Enter. If you use this technique, Excel removes the copied information from the Clipboard so that it can't be pasted again.
If you're copying a range, you don't need to select an entire same-sized range before you click the Paste button. You only need to activate the upper-left cell in the destination range.
If you prefer, you can use the following shortcut menu commands for copying and pasting:
For more control over how the pasted information appears, right-click the destination cell and use one of the paste icons in the shortcut menu (see Figure 4.8).
Instead of using Paste, you can just activate the destination cell and press Enter. If you use this technique, Excel removes the copied information from the Clipboard so that it can't be pasted again.
The copy and paste operations also have shortcut keys associated with them:
Excel also enables you to copy or move a cell or range by dragging. Unlike other methods of copying and moving, dragging and dropping does not place any information on either the Windows Clipboard or the Office Clipboard.
To copy using drag-and-drop, select the cell or range that you want to copy and then press Ctrl and move the mouse to one of the selection's borders. (The mouse pointer is augmented with a small plus sign.) Then drag the selection to its new location while you continue to press the Ctrl key. The original selection remains behind, and Excel makes a new copy when you release the mouse button.
To move a range using drag-and-drop, don't press Ctrl while dragging the border.
Often, you need to copy a cell to an adjacent cell or range. This type of copying is quite common when you're working with formulas. For example, if you're working on a budget, you might create a formula to add the values in column B. You can use the same formula to add the values in the other columns. Rather than re-enter the formula, you can copy it to the adjacent cells.
Excel provides additional options for copying to adjacent cells. To use these commands, activate the cell that you're copying and extend the cell selection to include the cells that you're copying to. Then issue the appropriate command from the following list for one-step copying:
None of these commands places information on either the Windows Clipboard or the Office Clipboard.
You can use the copy procedures described previously to copy a cell or range to another worksheet, even if the worksheet is in a different workbook. You must, of course, activate the other worksheet before you select the location to which you want to copy.
Excel offers a quicker way to copy a cell or range and paste it to other worksheets in the same workbook:
[Group]
in the workbook's title bar.Whenever you cut or copy information in an Office program, such as Excel, you can place the data on both the Windows Clipboard and the Office Clipboard. When you copy information to the Office Clipboard, you append the information to the Office Clipboard instead of replacing what is already there. With multiple items stored on the Office Clipboard, you can then paste the items either individually or as a group.
To use the Office Clipboard, you first need to open it. Use the dialog launcher on the bottom right of the Home Clipboard group to toggle the Clipboard task pane on and off.
After you open the Clipboard task pane, select the first cell or range that you want to copy to the Office Clipboard and copy it by using any of the preceding techniques. Repeat this process, selecting the next cell or range that you want to copy. As soon as you copy the information, the Office Clipboard task pane shows you the number of items that you've copied and a brief description (it will hold up to 24 items). Figure 4.9 shows the Office Clipboard with five copied items (four from Excel and one from Word).
When you're ready to paste information, select the cell into which you want to paste information. To paste an individual item, click it in the Clipboard task pane. To paste all the items that you've copied, click the Paste All button (which is at the top of the Clipboard task pane). The items are pasted, one after the other. The Paste All button is probably more useful in Word, for situations in which you copy text from various sources and then paste it all at once.
You can clear the contents of the Office Clipboard by clicking the Clear All button.
The following items about the Office Clipboard and how it functions are worth noting:
You may not always want to copy everything from the source range to the destination range. For example, you may want to copy only the formula results rather than the formulas themselves. Or you may want to copy the number formats from one range to another without overwriting any existing data or formulas.
To control what is copied into the destination range, choose Home Clipboard Paste and use the drop-down menu shown in Figure 4.10. When you hover your mouse pointer over an icon, you'll see a preview of the pasted information in the destination range. Click the icon to use the selected paste option.
The paste options are
For yet another pasting method, choose Home Clipboard Paste Paste Special to display the Paste Special dialog box (see Figure 4.11). You can also right-click and choose Paste Special from the shortcut menu to display this dialog box. This dialog box has several options, which I explain in the following list.
In addition, the Paste Special dialog box enables you to perform other operations, described in the following sections.
The option buttons in the Operation section of the Paste Special dialog box let you perform an arithmetic operation on values and formulas in the destination range. For example, you can copy a range to another range and select the Multiply operation. Excel multiplies the corresponding values in the source range and the destination range and replaces the destination range with the new values.
This feature also works with a single copied cell, pasted to a multicell range. Assume that you have a range of values, and you want to increase each value by 5 percent. Enter 105% into any blank cell and copy that cell to the Clipboard. Then select the range of values and bring up the Paste Special dialog box. Select the Multiply option, and each value in the range is multiplied by 105%.
The Skip Blanks option in the Paste Special dialog box prevents Excel from overwriting cell contents in your paste area with blank cells from the copied range. This option is useful if you're copying a range to another area but don't want the blank cells in the copied range to overwrite existing data.
The Transpose option in the Paste Special dialog box changes the orientation of the copied range. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed. Note that you can use this check box with the other options in the Paste Special dialog box. Figure 4.12 shows an example of a horizontal range (A1:D5) that was transposed to a different range (A9:E12).
Dealing with cryptic cell and range addresses can sometimes be confusing, especially when you work with formulas, which I cover in Chapter 10, “Introducing Formulas and Functions.” Fortunately, Excel allows you to assign descriptive names to cells and ranges. For example, you can give a cell a name such as Interest_Rate, or you can name a range JulySales. Working with these names (rather than cell or range addresses) has several advantages:
#NAME?
error.=Income—Taxes
is certainly more intuitive than =D20—D40
.Excel provides several methods you can use to create range names. Before you begin, however, you should be aware of a few rules:
The fastest way to create a name is to use the Name box (to the left of the Formula bar). Select the cell or range to name, click the Name box, and type the name. Press Enter to create the name. (You must press Enter to actually record the name; if you type a name and then click in the worksheet, Excel doesn't create the name.)
If you type an invalid name (such as May21, which happens to be a cell address, MAY21), Excel activates that address and doesn't warn you that the name is not valid. If the name you type includes an invalid character, Excel displays an error message. If a name already exists, you can't use the Name box to change the range to which that name refers. Attempting to do so simply selects the range.
The Name box is a drop-down list and shows all names in the workbook. To choose a named cell or range, click the Name box and choose the name. The name appears in the Name box, and Excel selects the named cell or range in the worksheet.
For more control over naming cells and ranges, use the New Name dialog box. Start by selecting the cell or range that you want to name. Then choose Formulas Defined Names Define Name. Excel displays the New Name dialog box, shown in Figure 4.13. Note that this is a resizable dialog box. Click and drag a border to change the dimensions.
Type a name in the Name text field (or use the name that Excel proposes, if any). The selected cell or range address appears in the Refers To text field. Use the Scope drop-down list to indicate the scope for the name. The scope indicates where the name will be valid, and it's either the entire workbook or the worksheet in which the name is defined. If you like, you can add a comment that describes the named range or cell. Click OK to add the name to your workbook and close the dialog box.
You may have a worksheet that contains text that you want to use for names for adjacent cells or ranges. For example, you may want to use the text in column A to create names for the corresponding values in column B. Excel makes this task easy.
To create names by using adjacent text, start by selecting the name text and the cells that you want to name. (These items can be individual cells or ranges of cells.) The names must be adjacent to the cells that you're naming. (A multiple selection is allowed.) Then choose Formulas Defined Names Create from Selection. Excel displays the Create Names from Selection dialog box, shown in Figure 4.14.
The check marks in the Create Names from Selection dialog box are based on Excel's analysis of the selected range. For example, if Excel finds text in the first row of the selection, it proposes that you create names based on the top row. If Excel didn't guess correctly, you can change the check boxes. Click OK, and Excel creates the names. Using the data in Figure 4.14, Excel creates twelve names: January for cell B1, February for cell B2, and so on.
A workbook can have any number of named cells and ranges. If your workbook has many names, you should know about the Name Manager, shown in Figure 4.15.
The Name Manager appears when you choose Formulas Defined Names Name Manager (or press Ctrl+F3). The Name Manager has the following features:
If you delete the rows or columns that contain named cells or ranges, the names contain an invalid reference. For example, if cell A1 on Sheet1 is named Interest and you delete row 1 or column A, the name Interest then refers to =Sheet1!#REF!
(that is, to an erroneous reference). If you use the name Interest in a formula, the formula displays #REF
.
Documentation that explains certain elements in the worksheet can often be helpful. One way to document your work is to add comments to cells. This feature is useful when you need to describe a particular value or explain how a formula works.
To add a comment to a cell, select the cell and use any of these actions:
Excel inserts a comment that points to the active cell. Initially, the comment consists of your name, as specified in the General tab of the Excel Options dialog box (choose File Options to display this dialog box). You can delete your name from the comment, if you like. Enter the text for the cell comment and then click anywhere in the worksheet to hide the comment. You can change the size of the comment by clicking and dragging any of its borders. Figure 4.16 shows a cell with a comment.
Cells that have a comment display a small red triangle in the upper-right corner. When you move the mouse pointer over a cell that contains a comment (or activate the cell), the comment becomes visible.
You can force a comment to be displayed even when its cell is not activated. Right-click the cell and choose Show/Hide Comments. Although this command refers to “comments” (plural), it affects only the comment in the active cell. To return to normal (make the comment appear only when its cell is activated or the mouse point hovers over it), right-click the cell and choose Hide Comment.
If you don't like the default look of cell comments, you can make some changes. Right-click the cell and choose Edit Comment. Select the text in the comment and use the commands of the Font and the Alignment groups (on the Home tab) to make changes to the comment's appearance.
For even more formatting options, right-click the comment's border and choose Format Comment from the shortcut menu. Excel responds by displaying the Format Comment dialog box, which allows you to change many aspects of its appearance, including color, border, and margins.
Cell comments are rectangular, but they don't have to be. To change the shape of a cell comment, add a command to your Quick Access toolbar:
After you perform these steps, your Quick Access toolbar has a new Change Shape icon.
To change the shape of a comment, make sure that it's visible (right-click the cell and select Edit Comment). Then click the comment's border to select it as a Shape (or Ctrl+click the comment to select it as a Shape). Click the Change Shape button on the Quick Access toolbar and choose a new shape for the comment. Figure 4.18 shows a cell comment with a nonstandard shape.
To read all comments in a workbook, choose Review Comments Next. Keep clicking Next to cycle through all the comments in a workbook. Choose Review Comments Previous to view the comments in reverse order.
Normally, when you print a worksheet that contains cell comments, the comments are not printed. If you would like to print the comments, though, here's how:
If you want all cell comments to be visible (regardless of the location of the cell pointer), choose Review Comments Show All Comments. This command is a toggle; select it again to hide all cell comments.
To toggle the display of an individual comment, select its cell and then choose Review Comments Show/Hide Comment.
To quickly select all cells in a worksheet that contain a comment, choose Home Editing Find & Select Go to Special. Then choose the Comments option and click OK.
To edit the text in a comment, activate the cell, right-click, and then choose Edit Comment from the shortcut menu. Or select the cell and press Shift+F2. After you make your changes, click any cell.
To delete a cell comment, activate the cell that contains the comment and then choose Review Comments Delete. Or right-click and then choose Delete Comment from the shortcut menu.