This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

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.

return to topApplying 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).

  1. Select the range of cells to be formatted

  2. From the Home command tab, in the Styles group, click CONDITIONAL FORMATTINGConditional Formatting button
    A pull-down list appears.

  3. Select Highlight Cells Rules » select the desired criterion
    A dialog box appears.

  4. In the dialog box, specify your criteria
    NOTE: The criteria will differ depending on the option chosen in step 3.

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

  1. Select the range of cells to be formatted

  2. From the Home command tab, in the Styles group, click CONDITIONAL FORMATTINGConditional Formatting button
    A pull-down list appears.

  3. Select Top/Bottom Rules » select the desired criterion
    A dialog box appears.

  4. In the dialog box, specify your criteria
    NOTE: The criteria will differ depending on the option chosen in step 3.

  5. 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
Examples of Conditional Formatting
  1. Select the range of cells to be formatted

  2. From the Home command tab, in the Styles group, click CONDITIONAL FORMATTINGConditional Formatting button
    A pull-down list appears.

  3. Select Data Bars, Color Scales, or Icon Sets » select your desired style
    The rule is applied.

return to topCreating 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.

  1. Select the range of cells to be formatted

  2. From the Home command tab, in the Styles group, click CONDITIONAL FORMATTINGConditional Formatting button» select New Rule...
    The New Formatting Rule dialog box appears.
    New Formatting Rule dialog box

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

  4. In the Edit the Rule Description section, select your criteria
    The criteria will vary based on the selection made in step 3.

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

  6. When finished, click OK
    The new rule is applied and saved.

return to topEditing Rules

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

  1. From the Home command tab, in the Styles group, click CONDITIONAL FORMATTINGConditional Formatting button» select Manage Rules...
    The Conditional Formatting Rules Manager appears.
    Conditional Formatting Rules Manager

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

  3. From the list, select the rule you want to edit
    The rule is highlighted.

  4. Click EDIT RULE...
    The Edit Formatting Rule dialog box appears.
    Edit Formatting Rule dialog box

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

  6. To change criteria, in the Edit the Rule Description section, select your criteria

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

  8. When finished, click OK
    The changes are saved.

  9. To close the Conditional Formatting Rules Manager, click OK

return to topPrioritizing 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.

  1. From the Home command tab, in the Styles group, click CONDITIONAL FORMATTINGConditional Formatting button» select Manage Rules...
    The Conditional Formatting Rules Manager appears.
    Conditional Formatting Rules Manager

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

  3. Select the rule for which you want to change priority
    The rule is highlighted.

  4. To move the rule up by one rule, click MOVE UPMove Up button
    To move the rule down by one rule, click MOVE DOWN Move Down button
    The rule adjusts.

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

return to topDeleting Rules

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

  1. From the Home command tab, in the Styles group, click CONDITIONAL FORMATTINGConditional Formatting button» select Manage Rules...
    The Conditional Formatting Rules Manager appears.
    Conditional Formatting Rules Manager

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

  3. Select the rule to delete
    The rule is highlighted.

  4. Click DELETE RULEDelete Rule button
    The rule is deleted.

  5. Click OK
    The changes are saved.

Excellence. Our Measure. Our Motto. Our Goal.