Part I: Getting Started with Excel

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

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 Excel Options dialog box (you can delete your name, 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 clarify important 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, the comment becomes visible.

94

Chapter 4: Working with Cells and Ranges

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), right-click the cell and choose Hide Comment.

Tip

You can control how comments are displayed. Access 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. n

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 rightclick the comment’s border 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. n

FIGURE 4.17

This comment contains a graphics image.

95

Part I: Getting Started with Excel

Changing a comment’s shape

Normally, a cell comment is 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 performing 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 Show/ Hide Comments). 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:

96

Chapter 4: Working with Cells and Ranges

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.

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 comments, 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.

97

Part I: Getting Started with Excel

An Alternative to Cell Comments

You can make use of Excel’s Data Validation (covered in Chapter 25) 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 to display the Data Validation dialog box.

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.As an option, 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 performing 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.

Editing comments

To edit 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.

98