(Legacy) Microsoft Excel 2007: Working with Conditional Formatting

Last Updated Saturday, April 20, 2024, at 2:43 am

Note: This article is based on legacy software.

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

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

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 BarsColor ScalesIcon Sets example

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.