Another slick feature of Excel is the ability it gives you to add conditional formatting to your worksheet, formatting that automatically adjusts depending on the contents of worksheet cells. In plain English, this means that you can highlight important trends in your data— such as the rise in a stock price, or a sudden spurt in your college expenses—based on conditions you set in advance using the Conditional Formatting dialog box. Using this feature, an out-of-the-ordinary number will "jump out" at anyone who routinely uses the worksheet.
The following example shows how to add conditional formatting to a sample worksheet that tracks stock prices. If a stock in the Gain/Loss column rises by more than 20 percent, the conditional formatting will display numbers in bold type on a light blue diagonal background. If a stock in the Gain/Loss column falls by more than 20 percent, the number will be displayed in bold type on a solid red background. (Our worksheet is shown in Figure 17-17)
ON THE WEB
The CondForm.xls example is on the Running Office 2000 Reader's Corner page.
FIGURE 17-17. Conditional formatting highlights noteworthy numbers automatically, according to your specifications.
Here's how you would create such a conditional format.
- Create a worksheet containing one or more cells of numeric information. (The worksheet can be an invoice, a financial document, a sales report, or any other document with useful numeric data.)
- Select the cell range to which you want to apply the conditional formatting. (Note that each cell can maintain its own, unique conditional formatting, so that you can set up several different conditions.)
- Choose Conditional Formatting from the Format menu. The Conditional Formatting dialog box appears, containing several drop-down list boxes.
- In the first list box, indicate whether you want Excel to use the current formula or the current value from the cells that you have selected. (In most cases, you'll want to use the cell value.)
- In the second list box, indicate the comparison operator you'd like to use in the conditional formatting. For our example, we selected greater than (>), because we're looking for stock returns greater than 20 percent.
- In the third list box, type the number you want to use in the comparison. We typed 20% or 0.2, because we want to isolate gains over 20 percent.
- Now click the Format button and specify the formatting you'll use for the cells if the conditional statement you specified in steps 4 through 6 becomes true.
A modified Format Cells dialog box appears that has three formatting tabs. We selected light blue on the Patterns tab, and then clicked OK.
- If necessary, click the Add button in the Conditional Formatting dialog box to add another condition to the scenario. (We took this opportunity to add a condition that highlighted losses of more than 20 percent in the worksheet.) The dialog box expands to accept an additional condition.
The Add button lets you add up to three conditions. The Delete button removes conditions you no longer want.
- Specify the operator you want to use in the second drop-down list box, and then type a value in the third list box. We specified less than (<) as the operator, and then typed -20%.
- If you specified a second condition, click the Format button for Condition 2 and select a unique formatting color or type style.
Using the Patterns tab, we specified red shading. Our screen looks like this:
- Click OK to close the dialog box and see the conditional formatting applied to the selected text. If any numbers fall into the ranges you specified, the formatting you specified will be carried out. Figure 17-17 shows two gains and one loss highlighted by the conditional formatting we entered for this example. Our efforts certainly paid off, especially if we now act on the knowledge of our profits or losses!