Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Understanding Excel cells and ranges

2. Selecting cells and ranges

3. Copying or moving ranges

4. Using names to work with ranges

5. Adding comments to cells

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.

Understanding Cells and Ranges

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:

Selecting ranges

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.

Figure 4.1 When you select a range, it appears highlighted, but the active cell within the range is not highlighted.

You can select a range in several ways:

· Press the left mouse button and drag, highlighting the range. Then release the mouse button. If you drag to the end of the window, the worksheet will scroll.

· Press the Shift key while you use the navigation keys to select a range.

· Press F8 and then move the cell pointer with the navigation keys to highlight the range. Press F8 again to return the navigation keys to normal movement.

· Type the cell or range address into the Name box (located to the left of the Formula bar) and press Enter. Excel selects the cell or range that you specified.

· Choose Home Editing Find & Select Go To (or press F5) and enter a range's address manually in the Go To dialog box. When you click OK, Excel selects the cells in the range that you specified.

Tip

While you're selecting a range that contains more than one cell, Excel displays the number of rows and columns in your selection in the Name box (which is to the left of the Formula bar). When you finish making the selection, the Name box reverts to showing the address of the active cell.

Quick Analysis?

When you select a range of data, Excel may display a Quick Analysis icon at the lower right of your selection. Click the icon, and you'll see a list of analysis options that you can quickly apply to the selected data. You can add conditional formatting, create a chart, add formulas, create a pivot table, and generate Sparkline graphics. The exact options vary, depending on the data in the range.

These options provide nothing that you can't do using standard commands, and all these options are discussed elsewhere in this book. If you find the Quick Analysis icon annoying, choose File Options to display the Excel Options dialog box, select the General tab, and deselect Show Quick Analysis Options on Selection.

Selecting complete rows and columns

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:

· Click the row or column border to select a single row or column.

· To select multiple adjacent rows or columns, click a row or column border and drag to highlight additional rows or columns.

· To select multiple (nonadjacent) rows or columns, press Ctrl while you click the row or column borders that you want.

· Press Ctrl+spacebar to select a column. The column of the active cell (or columns of the selected cells) is highlighted.

· Press Shift+spacebar to select a row. The row of the active cell (or rows of the selected cells) is highlighted.

Tip

Press Ctrl+A to select all cells in the worksheet, which is the same as selecting all rows and all columns. If the active cell is within a table (created by choosing Insert Tables Table), you may need to press Ctrl+A two or even three times to select all cells in the worksheet. You can also click the area at the intersection of the row and column borders to select all cells.

Selecting noncontiguous 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.

Figure 4.2 Excel enables you to select noncontiguous ranges.

You can select a noncontiguous range in several ways:

· Select the first range (or cell). Then press and hold Ctrl as you click and drag the mouse to highlight additional cells or ranges.

· From the keyboard, select a range as described previously (using F8 or the Shift key). Then press Shift+F8 to select another range without canceling the previous range selections.

· Enter the range (or cell) address in the Name box and press Enter. Separate each range address with a comma.

· Choose Home Editing Find & Select Go To (or press F5) to display the Go To dialog box. Enter the range (or cell) address in the Reference box, and separate each range address with a comma. Click OK, and Excel selects the ranges.

Note

Noncontiguous ranges differ from contiguous ranges in several important ways. One major difference is that you can't use drag-and-drop methods (described later) to move or copy noncontiguous ranges.

Selecting multisheet ranges

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.

Figure 4.3 The worksheets in this workbook are laid out identically.

This workbook, named budget.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.

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:

1. Activate the Totals worksheet by clicking its tab.

2. Select the range B3:F3.

3. Press Shift and click the Manufacturing sheet tab. This step selects all worksheets between the active worksheet (Totals) and the sheet tab that you click — in essence, a three-dimensional range of cells (see Figure 4.4). When multiple sheets are selected, the workbook window's title bar displays [Group] to remind you that you've selected a group of sheets and that you're in Group mode.

Figure 4.4 In Group mode, you can work with a three-dimensional range of cells that extend across multiple worksheets.

4. Choose Home Font Bold and then choose Home Font Fill Color to apply a colored background. Excel applies the formatting to the selected range across the selected sheets.

5. Click one of the other sheet tabs. This step selects the sheet and cancels 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.

Note

When Excel is in Group mode, some commands are disabled and can't be used. For example, in the preceding example, you can't convert all these ranges to tables by choosing Insert Tables Table.

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.

Tip

To select all sheets in a workbook, right-click any sheet tab and choose Select All Sheets from the shortcut menu.

Selecting special types of cells

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.

Figure 4.5 Use the Go to Special dialog box to select specific types of cells.

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.

Tip

If you bring up the Go to Special dialog box with only one cell selected, Excel bases its selection on the entire used area of the worksheet. Otherwise, the selection is based on the selected range.

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

Tip

When you select an option in the Go to Special dialog box, be sure to note which suboptions become available. The placement of these suboptions can be misleading. For example, when you select Constants, the suboptions under Formulas become available to help you further refine the results. Likewise, the suboptions under Dependents also apply to Precedents, and those under Data Validation also apply to Conditional Formats.

Selecting cells by searching

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.

Figure 4.6 The Find and Replace dialog box, with its options displayed.

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.

Figure 4.7 The Find and Replace dialog box, with its results listed.

Note

The Find and Replace dialog box allows you to return to the worksheet without dismissing the dialog box.

The Find and Replace dialog box supports two wildcard 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.

Tip

To search for a question mark or an asterisk, precede the character with a tilde (˜). For example, the following search string finds the text *NONE*:

1. ˜*NONE˜*

If you need to search for the tilde character, use two tildes.

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):

· Match Case: If this check box is selected, the case of the text must match exactly. For example, searching for smith does not locate Smith.

· Match Entire Cell Contents: If this check box is selected, a match occurs if the cell contains only the search string (and nothing else). For example, searching for Excel doesn't locate a cell that contains Microsoft Excel. When using wildcard characters, an exact match is not required.

· Look In: This drop-down list has three options: Values, Formulas, and Comments. If, for example, Values is selected, searching for 900 doesn't find a cell that contains 900 if that value is generated by a formula (unless the formula itself contains 900).

Copying or Moving Ranges

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:

· Copy a cell to another location.

· Copy a cell to a range of cells. The source cell is copied to every cell in the destination range.

· Copy a range to another range.

· Move a range of cells to another location.

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.

Note

Copying a cell normally copies the cell's contents, any formatting that is applied to the original cell (including conditional formatting and data validation), and the cell comment (if it has one). When you copy a cell that contains a formula, the cell references in the copied formulas are changed automatically to be relative to their new destination.

Copying or moving consists of two steps (although shortcut methods are available):

1. Select the cell or range to copy (the source range), and copy it to the Clipboard. To move the range instead of copying it, cut the range instead of copying it.

2. Move the cell pointer to the range that will hold the copy (the destination range), and paste the Clipboard contents.

Caution

When you paste information, Excel overwrites any cells that get in the way without warning you. If you find that pasting overwrote some essential cells, choose Undo from the Quick Access toolbar (or press Ctrl+Z).

Caution

When you copy a cell or range, Excel surrounds the copied area with a thick-dashed border. As long as that border remains visible, the copied information is available for pasting. If you press Esc to cancel the border, Excel removes the information from the Clipboard.

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.

Copying by using Ribbon commands

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.

Tip

The Home Clipboard Paste control contains a drop-down arrow that, when clicked, gives you additional paste option icons. The paste preview icons are explained later in this chapter (see “Pasting in special ways”).

About the Office Clipboard

Whenever you cut or copy information from a Windows program, Windows stores the information on the Windows Clipboard, which is an area of your computer's memory. Each time that you cut or copy information, Windows replaces the information previously stored on the Clipboard with the new information that you cut or copied. The Windows Clipboard can store data in a variety of formats. Because Windows manages information on the Clipboard, it can be pasted to other Windows applications, regardless of where it originated.

Microsoft Office has its own Clipboard (the Office Clipboard), which is available only in Office programs. To view or hide the Office Clipboard, click the dialog launcher icon in the bottom-right corner of the Home Clipboard group.

Whenever you cut or copy information in an Office program, such as Excel or Word, the program places the information on both the Windows Clipboard and the Office Clipboard. However, the program treats information on the Office Clipboard differently from the way it treats information on the Windows Clipboard. Instead of replacing information on the Office Clipboard, the program appends the information to the Office Clipboard. With multiple items stored on the Clipboard, you can then paste the items either individually or as a group.

You can find out more about this feature (including an important limitation) in “Using the Office Clipboard to paste,” later in this chapter.

Copying by using shortcut menu commands

If you prefer, you can use the following shortcut menu commands for copying and pasting:

· Right-click the range and choose Copy (or Cut) from the shortcut menu to copy the selected cells to the Clipboard.

· Right-click and choose Paste from the shortcut menu that appears to paste the Clipboard contents to the selected cell or range.

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).

Figure 4.8 The paste icons on the shortcut menu provide more control over how the pasted information appears.

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.

Copying by using shortcut keys

The copy and paste operations also have shortcut keys associated with them:

· Ctrl+C copies the selected cells to both the Windows Clipboard and the Office Clipboard.

· Ctrl+X cuts the selected cells to both the Windows Clipboard and the Office Clipboard.

· Ctrl+V pastes the Windows Clipboard contents to the selected cell or range.

Tip

These are standard key combinations, used by many other Windows applications.

Using Paste Options Buttons When Inserting and Pasting

Some cell and range operations — specifically inserting, pasting, and filling cells by dragging — result in the display of paste option buttons. For example, if you copy a range and then paste it to a different location using Home Clipboard Paste, a drop-down options list appears at the lower right of the pasted range. Click the list (or press Ctrl), and you see the options shown in the figure here. These options enable you to specify how the data should be pasted, such as values only or formatting only. In this case, using the paste option buttons is an alternative to using options in the Paste Special dialog box. (Read more about Paste Special in the upcoming section “Using the Paste Special dialog box.”)

Some users find these paste options buttons helpful, and others think that they're annoying. (Count me in the latter group.) To disable this feature, choose File Options and click the Advanced tab. Remove the check mark from the two options labeled Show Paste Options Buttons When Content Is Pasted and Show Insert Options Buttons.

Copying or moving by using drag-and-drop

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.

Caution

The drag-and-drop method of moving does offer one advantage over the cut-and-paste method: Excel warns you if a drag-and-drop move operation will overwrite existing cell contents. Oddly, you do not get a warning if a drag-and-drop copy operation will overwrite existing cell contents.

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.

Tip

If the mouse pointer doesn't turn into an arrow when you point to the border of a cell or range, you need to make a change to your settings. Choose File Options to display the Excel Options dialog box, select the Advanced tab, and place a check mark on the option labeled Enable Fill Handle and Cell Drag-and-Drop.

Copying to adjacent cells

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:

· Home Editing Fill Down (or Ctrl+D) copies the cell to the selected range below.

· Home Editing Fill Right (or Ctrl+R) copies the cell to the selected range to the right.

· Home Editing Fill Up copies the cell to the selected range above.

· Home Editing Fill Left copies the cell to the selected range to the left.

None of these commands places information on either the Windows Clipboard or the Office Clipboard.

Tip

You also can use AutoFill to copy to adjacent cells by dragging the selection's fill handle (the small square in the bottom-right corner of the selected cell or range). Excel copies the original selection to the cells that you highlight while dragging. For more control over the AutoFill operation, drag the fill handle with the right mouse button, and you'll get a shortcut menu with additional options.

Copying a range to other sheets

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:

1. Select the range to copy.

2. Press Ctrl and click the sheet tabs for the worksheets to which you want to copy the information. Excel displays [Group] in the workbook's title bar.

3. Choose Home Editing Fill Across Worksheets. A dialog box appears to ask you what you want to copy (All, Contents, or Formats).

4. Make your choice and then click OK. Excel copies the selected range to the selected worksheets; the new copy occupies the same cells in the selected worksheets as the original occupies in the initial worksheet.

Caution

Be careful with the Home Editing Fill Across Worksheets command because Excel doesn't warn you when the destination cells contain information. You can quickly overwrite lots of cells with this command and not even realize it. So make sure you check your work, and use Undo if the result isn't what you expected.

Using the Office Clipboard to paste

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.

Tip

To make the Clipboard task pane open automatically, click the Options button near the bottom of the task pane and choose the Show Office Clipboard Automatically option.

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).

Figure 4.9 Use the Clipboard task pane to copy and paste multiple items.

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:

· Excel pastes the contents of the Windows Clipboard (the last item you copied to the Office Clipboard) when you paste by choosing Home Clipboard Paste, by pressing Ctrl+V, or by right-clicking and choosing Paste from the shortcut menu.

· The last item that you cut or copied appears on both the Office Clipboard and the Windows Clipboard.

· Pasting from the Office Clipboard also places that item on the Windows Clipboard. If you choose Paste All from the Office Clipboard toolbar, you paste all items stored on the Office Clipboard onto the Windows Clipboard as a single item.

· Clearing the Office Clipboard also clears the Windows Clipboard.

Caution

The Office Clipboard has a serious problem that limits its usefulness for Excel users: if you copy a range that contains formulas, the formulas are not transferred when you paste to a different range. Only the values are pasted. Furthermore, Excel doesn't even warn you about this fact.

Pasting in special ways

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.

Figure 4.10 Excel offers several pasting options, with preview. Here, the information is copied from E4:G7 and is being pasted beginning at cell F11 using the Transpose option.

The paste options are

· Paste (P): Pastes the cell's contents, formats, and data validation from the Windows Clipboard.

· Formulas (F): Pastes formulas but not formatting.

· Formulas & Number Formatting (O): Pastes formulas and number formatting only.

· Keep Source Formatting (K): Pastes formulas and all formatting.

· No Borders (B): Pastes everything except borders that appear in the source range.

· Keep Source Column Width (W): Pastes formulas and duplicates the column width of the copied cells.

· Transpose (T): 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.

· Merge Conditional Formatting (G): This icon is displayed only when the copied cells contain conditional formatting. When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range.

· Values (V): Pastes the results of formulas. The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values.

· Values & Number Formatting (A): Pastes the results of formulas plus the number formatting.

· Values & Source Formatting (E): Pastes the results of formulas plus all formatting.

· Formatting (R): Pastes only the formatting of the source range.

· Paste Link (N): Creates formulas in the destination range that refer to the cells in the copied range.

· Picture (U): Pastes the copied information as a picture.

· Linked Picture (I): Pastes the copied information as a “live” picture that is updated if the source range is changed.

· Paste Special: Displays the Paste Special dialog box (described in the next section).

Note

After you paste, you're offered another chance to change your mind. A Paste Options drop-down appears at the lower right of the pasted range. Click it (or press Ctrl), and you see the paste option icons again.

Using the Paste Special dialog box

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.

Figure 4.11 The Paste Special dialog box.

Note

Excel actually has several different Paste Special dialog boxes, each with different options. The one displayed depends on what's copied. This section describes the Paste Special dialog box that appears when a range or cell has been copied.

Tip

For the Paste Special command to be available, you need to copy a cell or range. (Choosing Home Clipboard Cut doesn't work.)

· All: Pastes the cell's contents, formats, and data validation from the Windows Clipboard.

· Formulas: Pastes values and formulas, with no formatting.

· Values: Pastes values and the results of formulas (no formatting). The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values.

· Formats: Copies only the formatting.

· Comments: Copies only the cell comments from a cell or range. This option doesn't copy cell contents or formatting.

· Validation: Copies the validation criteria so the same data validation will apply. Data validation is applied by choosing Data Data Tools Data Validation.

· All Using Source Theme: Pastes everything but uses the formatting from the document theme of the source. This option is relevant only if you're pasting information from a different workbook, and the workbook uses a different document theme than the active workbook.

· All Except Borders: Pastes everything except borders that appear in the source range.

· Column Widths: Pastes only column width information.

· Formulas and Number Formats: Pastes all values, formulas, and number formats (but no other formatting).

· Values and Number Formats: Pastes all values and numeric formats but not the formulas themselves.

· All merging conditional formats: Merges the copied conditional formatting with any conditional formatting in the destination range. This option is enabled only when you're copying a range that contains conditional formatting.

In addition, the Paste Special dialog box enables you to perform other operations, described in the following sections.

Performing mathematical operations without formulas

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%.

Caution

If the destination range contains formulas, the formulas are also modified. In many cases, this is not what you want.

Skipping blanks when pasting

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.

Transposing a range

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).

Figure 4.12 Transposing a range changes the orientation as the information is pasted into the worksheet.

Tip

If you click the Paste Link button in the Paste Special dialog box, you create formulas that link to the source range. As a result, the destination range automatically reflects changes in the source range.

Using Names to Work with Ranges

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:

· A meaningful range name (such as Total_Income) is much easier to remember than a cell address (such as AC21).

· Entering a name is less error prone than entering a cell or range address, and if you type a name incorrectly in a formula, Excel will display a #NAME? error.

· You can quickly move to areas of your worksheet either by using the Name box, located at the left side of the Formula bar (click the arrow to drop down a list of defined names), or by choosing Home Editing Find & Select Go To (or pressing F5) and specifying the range name.

· Creating formulas is easier. You can paste a cell or range name into a formula by using Formula Autocomplete.

See Chapter 10 for information on Formula Autocomplete.

· Names make your formulas more understandable and easier to use. A formula such as =Income—Taxes is certainly more intuitive than =D20—D40.

Creating range names in your workbooks

Excel provides several methods you can use to create range names. Before you begin, however, you should be aware of a few rules:

· Names can't contain spaces. You may want to use an underscore character to simulate a space (such as Annual_Total).

· You can use any combination of letters and numbers, but the name must begin with a letter, underscore, or backslash. A name can't begin with a number (such as 3rdQuarter) or look like a cell address (such as QTR3). If these are desirable names, though, you can precede the name with an underscore or a backslash: for example, _3rd Quarter and \QTR3.

· Symbols — except for underscores, backslashes, and periods — aren't allowed.

· Names are limited to 255 characters, but it's a good practice to keep names as short as possible yet still meaningful.

Caution

Excel also uses a few names internally for its own use. Although you can create names that override Excel's internal names, you should avoid doing so. To be on the safe side, avoid using the following for names: Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title. To delete a range name or rename a range, see “Managing names,” later in this chapter.

Using the Name box

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.

Using the New Name dialog box

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.

Figure 4.13 Create names for cells or ranges by using the New Name dialog box.

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.

Using the Create Names from Selection 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.

Figure 4.14 Use the Create Names from Selection dialog box to name cells using labels that appear in the worksheet.

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.

Note

If the text contained in a cell would result in an invalid name, Excel modifies the name to make it valid. For example, if a cell contains the text Net Income (which is invalid for a name because it contains a space), Excel converts the space to an underscore character. If Excel encounters a value or a numeric formula where text should be, however, it doesn't convert it to a valid name. It simply doesn't create a name — and does not inform you of that fact.

Caution

If the upper-left cell of the selection contains text and you choose the Top Row and Left Column options, Excel uses that text for the name of the entire range, excluding the top row and left column. So, after Excel creates the names, take a minute to make sure that they refer to the correct ranges. If Excel creates a name that is incorrect, you can delete or modify it by using the Name Manager (described next).

Managing names

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.

Figure 4.15 Use the Name Manager to work with range names.

The Name Manager appears when you choose Formulas Defined Names Name Manager (or press Ctrl+F3). The Name Manager has the following features:

· Displays information about each name in the workbook: You can resize the Name Manager dialog box, widen the columns to show more information, and even rearrange the order of the columns. You can also click a column heading to sort the information by the column.

· Allows you to filter the displayed names: Clicking the Filter button lets you show only those names that meet certain criteria. For example, you can view only the worksheet-level names.

· Provides quick access to the New Name dialog box: Click the New button to create a new name without closing the Name Manager.

· Lets you edit names: To edit a name, select it in the list and then click the Edit button. You can change the name itself, modify the Refers To range, or edit the comment.

· Lets you quickly delete unneeded names: To delete a name, select it in the list and click Delete.

Caution

Be extra careful when deleting names. If the name is used in a formula, deleting the name causes the formula to become invalid. (It displays #NAME?.) It seems logical that Excel would replace the name with its actual address — but that doesn't happen. However, deleting a name can be undone, so if you find that formulas return #NAME? after you delete a name, choose Undo from the Quick Access toolbar (or press Ctrl+Z) to get the name back.

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.

Tip

The Name Manager is useful, but it has a shortcoming: it doesn't let you display the list of names in a worksheet range so you can view or print them. Such a feat is possible, but you need to look beyond the Name Manager.

To create a list of names in a worksheet, first move the cell pointer to an empty area of your worksheet. The list is created at the active cell position and overwrites any information at that location. Press F3 to display the Paste Name dialog box, which lists all the defined names. Then click the Paste List button. Excel creates a list of all names in the workbook and their corresponding addresses.

Adding Comments to Cells

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:

· Choose Review Comments New Comment.

· Right-click the cell and choose Insert Comment from the shortcut menu.

· Press Shift+F2.

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.

Figure 4.16 You can add comments to cells to help point out specific items in your worksheets.

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.

Tip

You can control the way comments are displayed. Choose File Options and then select the Advanced tab of the Excel Options dialog box. In the Display section, select the No Comments or Indicators option from the For Cells with Comments, Show list.

Formatting comments

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.

Tip

You can also display an image inside a comment. Right-click the cell and choose Edit Comment. Then right-click the comment's border (not the comment itself) and choose Format Comment. Select the Colors and Lines tab in the Format Comment dialog box. Click the Color drop-down list and select Fill Effects. In the Fill Effects dialog box, click the Picture tab and then click the Select Picture button to specify a graphics file. Figure 4.17 shows a comment that contains a picture.

Figure 4.17 This comment contains a graphics image.

An Alternative to Cell Comments

You can make use of Excel's Data Validation (see Chapter 25, “Using Custom Number Formats”) feature to add a different type of comment to a cell. This type of comment appears automatically when the cell is selected. Follow these steps:

1. Select the cell that will contain the comment.

2. Choose Data Data Tools Data Validation. The Data Validation dialog box appears.

3. Click the Input Message tab.

4. Make sure that the Show Input Message When Cell Is Selected check box is selected.

5. Type your comment in the Input Message box.

6. (Optional) Type a title in the Title box. This text will appear in bold at the top of the message.

7. Click OK to close the Data Validation dialog box.

After you perform these steps, the message appears when the cell is activated, and it disappears when any other cell is activated.

Note that this message isn't a “real” comment. For example, a cell that contains this type of message doesn't display a comment indicator, and it's not affected by any of the commands used to work with cell comments. In addition, you can't format these messages in any way, and you can't print them.

Changing a comment's shape

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:

1. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. The Quick Access Toolbar section of the Excel Options dialog box appears.

2. From the Choose Commands From drop-down list, select Drawing Tools | Format Tab.

3. From the list on the left, select Change Shape, and then click Add.

4. Click OK to close the Excel Options dialog box.

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.

Figure 4.18 Cell comments don't have to be rectangles.

Reading comments

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.

Printing comments

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:

1. Click the dialog box launcher in the Page Layout Page Setup group. This is the small icon to the right of the Page Setup group name. Clicking this icon displays the Page Setup dialog box.

2. In the Page Setup dialog box, click the Sheet tab.

3. Make your choice from the Comments drop-down control: At End of Sheet or As Displayed on Sheet (see Figure 4.19).

Figure 4.19 Specifying how to print cell comments.

4. Click OK to close the Page Setup dialog box or click the Print button to print the worksheet.

Note

You can also access the Page Setup box from the Print panel of Backstage view.

Hiding and showing comments

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.

Selecting comments

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.

Editing comments

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.

Deleting comments

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.