Microsoft Excel 2007

Working with Conditional Formatting

Conditional formatting rules allow you to format cells depending on how their data relates to other data. For example, in a list of numbers ranging from 1 to 10, you can format cells closer to 1 with red fill color, and cells closer to 10 with green fill color. You can also format data bars to appear within the cell, making a concise bar graph within a worksheet. You can apply preformatted rules, or you can create original rules. Conditional formatting logs your formatting rules so you can edit, prioritize, and delete rules easily.

Applying Preformatted Rules
Creating a Customized Rule
Editing Rules
Prioritizing Rules
Deleting Rules

Applying Preformatted Rules

Using preformatted rules is a quick way to apply conditional formatting to your worksheet. To create an original rule, refer to Creating a Customized Rule.

Applying Cell Highlighting

You can apply highlighting to cells if they satisfy criteria that you set. The criteria can be number-based (e.g., greater than, less than, equal to), text-based (e.g., text contains, date occurring), or both (e.g., duplicate values).

Select the range of cells to be formatted
From the Home command tab, in the Styles group, click CONDITIONAL FORMATTING
A pull-down list appears.
Select Highlight Cells Rules » select the desired criterion
A dialog box appears.
In the dialog box, specify your criteria
NOTE: The criteria will differ depending on the option chosen in step 3.
Click OK
The rule is applied to cells which satisfy the criteria.

Applying Top, Bottom, and Average Rules

You can apply conditional formatting to cells that satisfy criteria based on on the ten highest or lowest numbers, percentages, or averages.

Select the range of cells to be formatted
From the Home command tab, in the Styles group, click CONDITIONAL FORMATTING
A pull-down list appears.
Select Top/Bottom Rules » select the desired criterion
A dialog box appears.
In the dialog box, specify your criteria
NOTE: The criteria will differ depending on the option chosen in step 3.
Click OK
The rule is applied to cells which satisfy the criteria.

Applying Data Bars, Color Scales, and Icon Sets

Data Bars, Color Scales, and Icon Sets will format all cells in a range, depending on how each cell compares to the rest of the range.

EXAMPLES: In the following graphic, each column represents a preformatted rule as the value increases.

Data Bars	Color Scales	Icon Sets

Select the range of cells to be formatted
From the Home command tab, in the Styles group, click CONDITIONAL FORMATTING
A pull-down list appears.
Select Data Bars, Color Scales, or Icon Sets » select your desired style
The rule is applied.

Creating a Customized Rule

If you do not want to use one of Excel's preformatted rules, you can create your own using the New Formatting Rule dialog box.

Select the range of cells to be formatted
From the Home command tab, in the Styles group, click CONDITIONAL FORMATTING» select New Rule...
The New Formatting Rule dialog box appears.
From the Select a Rule Type section, select the condition that will trigger formatting
The Edit the Rule Description section will refresh to display new options.
In the Edit the Rule Description section, select your criteria
The criteria will vary based on the selection made in step 3.
To specify the formatting
1. Click FORMAT...
  The Format Cells dialog box appears.
2. Select the desired formatting options
3. Click OK
  The Format Cells dialog box closes.
When finished, click OK
The new rule is applied and saved.

Editing Rules

You can edit preformatted and original rules. Rules are only editable if they have been applied in a worksheet.

From the Home command tab, in the Styles group, click CONDITIONAL FORMATTING» select Manage Rules...
The Conditional Formatting Rules Manager appears.
From the Show formatting rules for pull-down list, select the worksheet containing the rule you want to edit
The conditional formatting rules applied in that selection will appear in the dialog box.
From the list, select the rule you want to edit
The rule is highlighted.
Click EDIT RULE...
The Edit Formatting Rule dialog box appears.
To change what triggers the formatting, from the Select a Rule Type section, select the condition
The Edit the Rule Description section will refresh to display new options.
To change criteria, in the Edit the Rule Description section, select your criteria
To change the formatting
1. Click FORMAT...
  The Format Cells dialog box appears.
2. Select the desired formatting options
3. Click OK
  The Format Cells dialog box closes.
When finished, click OK
The changes are saved.
To close the Conditional Formatting Rules Manager, click OK

Prioritizing Rules

When using conditional formatting, it may be necessary to prioritize your rules. For example, if you create a rule to format cells B2–B5 with red fill color, and you create another rule to format cells B2–E2 with yellow fill color, the cell B2 will have conflicting formatting. If this happens, both effects may appear, or one rule may simply override the other. With prioritized rules, the rule with higher priority will apply. You can easily adjust priorities through the Conditional Formatting Rules Manager.

NOTE: New rules are given the highest priority.

From the Home command tab, in the Styles group, click CONDITIONAL FORMATTING» select Manage Rules...
The Conditional Formatting Rules Manager appears.
From the Show formatting rules for pull-down list, select the worksheet containing the rule(s) you want to prioritize
The conditional formatting rules applied in that selection will appear in the dialog box.
NOTE: Rules at the top of the list have the highest priority.
Select the rule for which you want to change priority
The rule is highlighted.
To move the rule up by one rule, click MOVE UP
To move the rule down by one rule, click MOVE DOWN
The rule adjusts.
OPTIONAL: If you want Excel to automatically choose a rule that has a lower priority than its alternatives, for that rule, select Stop If True
NOTE: Not all rules have a Stop If True option.

Deleting Rules

Once a rule has been applied, it may be deleted.

From the Home command tab, in the Styles group, click CONDITIONAL FORMATTING» select Manage Rules...
The Conditional Formatting Rules Manager appears.
From the Show formatting rules for pull-down list, select the worksheet containing the rule you want to delete
The conditional formatting rules applied in that selection will appear in the dialog box.
Select the rule to delete
The rule is highlighted.
Click DELETE RULE
The rule is deleted.
Click OK
The changes are saved.