Chapter 20: Visualizing Data Using Conditional Formatting

FIGURE 20.18

The sum is displayed only when all four values have been entered.

FIGURE 20.19

A missing value causes the sum to be hidden.

Working with Conditional Formats

This section describes some additional information about conditional formatting that you may find useful.

Managing rules

The Conditional Formatting Rules Manager dialog box is useful for checking, editing, deleting, and adding conditional formats. First select any cell in the range that contains conditional formatting. Then choose Home Styles Conditional Formatting Manage Rules.

You can specify as many rules as you like by clicking the New Rule button. As you can see in Figure 20.20, cells can even use data bars, color scales, and icon sets all at the same time — although I can’t think of a good reason to do so.

499

Part III: Creating Charts and Graphics

FIGURE 20.20

This range uses data bars, color scales, and icon sets.

Copying cells that contain conditional formatting

Conditional formatting information is stored with a cell much like standard formatting information is stored with a cell. As a result, when you copy a cell that contains conditional formatting, you also copy the conditional formatting.

Tip

To copy only the formatting (including conditional formatting), use the Paste Special dialog box and select the Formats option. Or, use Home Clipboard Paste Formatting (R). n

If you insert rows or columns within a range that contains conditional formatting, the new cells have the same conditional formatting.

Deleting conditional formatting

When you press Delete to delete the contents of a cell, you do not delete the conditional formatting for the cell (if any). To remove all conditional formats (as well as all other cell formatting), select the cell. Then choose Home Editing Clear Clear Formats. Or, choose Home Editing Clear Clear All to delete the cell contents and the conditional formatting.

To remove only conditional formatting (and leave the other formatting intact), use Home Styles Conditional Formatting Clear Rules.

500

Chapter 20: Visualizing Data Using Conditional Formatting

Locating cells that contain conditional formatting

You can’t tell, just by looking at a cell, whether it contains conditional formatting. You can, however, use the Go To dialog box to select such cells.

1.Choose Home Editing Find & Select Go To Special.

2.In the Go To Special dialog box, select the Conditional Formats option.

3.To select all cells on the worksheet containing conditional formatting, select the All option; to select only the cells that contain the same conditional formatting as the active cell, select the Same option.

4.Click OK. Excel selects the cells for you.

Note

The Excel Find and Replace dialog box includes a feature that allows you to search your worksheet to locate cells that contain specific formatting. This feature does not locate cells that contain formatting resulting from conditional formatting. n

501

CHAPTER

Creating Sparkline

Graphics

503