Amal Nagm

To create a conditional format, you select the cells to which you want to apply the format, display the Home tab of the user interface, and then, in the Styles group, click Conditional Formatting to display a menu of possible conditional formats. Office Excel 2007 enables you to create all the conditional formats available in previous versions of the program and offers many more conditional formats than were previously available. Prior to Office Excel 2007, you could create conditional formats to highlight cells that contained values meeting a certain condition. For example, you could highlight all cells that contain a value over 100, contain a date before 1/28/2007, or contain an order amount between $100 and $500. In Office Excel 2007, you can define conditional formats that change how the program displays data in cells that contain values above or below the average values of the related cells, that contain values near the top or bottom of the value range, or that contain values duplicated elsewhere in the selected range.

When you select which kind of condition to create, Office Excel 2007 displays a dialog box that contains fields and controls you can use to define your rule. To display all your rules, display the Home tab and then, in the Styles group, click Conditional Formatting. From the menu that appears, click Manage Rules to display the Conditional Formatting Rules Manager.

Manage conditional formatting rules

The Conditional Formatting Rules Manager, which is new in Office Excel 2007, enables you to control your conditional formats in the following ways:

• Creates a new rule by clicking the New Rule button.
• Changes a rule by clicking the rule and then clicking the Edit Rule button.
• Removes a rule by clicking the rule and then clicking the Delete Rule button.
• Moves a rule up or down in the order by clicking the Move Up or Move Down button.
• Controls whether Office Excel 2007 continues evaluating conditional formats after it finds a rule to apply by selecting or clearing a rule's Stop If True check box.
• Saves any new rules and closes the Conditional Formatting Rules Manager by clicking OK.
• Saves any new rules without closing the Conditional Formatting Rules Manager by clicking Apply.
• Discards any unsaved changes by clicking Cancel.

Note Clicking the New Rule button in the Conditional Formatting Rules Manager displays the New Formatting Rule dialog box. The commands in the New Formatting Rule dialog box duplicate the options displayed when you click the Home tab's Conditional Formatting button.

Change the format of a rule

After you create a rule, you can change the format applied if the rule is true by clicking the rule and then clicking the Edit Rule button to display the Edit Formatting Rule dialog box. In that dialog box, click the Format button to display the Format Cells dialog box. After you define your format, click OK.

Important Office Excel 2007 doesn't check to make sure that your conditions are logically consistent, so you need to be sure that you enter your conditions correctly.

Create three new types of conditional formats

Office Excel 2007 also enables you to create three new types of conditional formats: data bars, color scales, and icon sets. Data bars summarize the relative magnitude of values in a cell range by extending a band of color across the cell.

Color scales compare the relative magnitude of values in a cell range by applying colors from a two- or three-color set to your cells. The intensity of a cell's color reflects the value's tendency toward the top or bottom of the values in the range.

Icon sets are collections of three, four, or five images that Office Excel 2007 displays when certain rules are met.

When you click a color scale or icon set in the Conditional Formatting Rule Manager and then click the Edit Rule button, you can control when Office Excel 2007 applies a color or icon to your data.

Caution Be sure to not include cells that contain summary formulas in your conditionally formatted ranges. The values, which could be much higher or lower than your regular cell data, could throw off your formatting comparisons