Custom Conditional Formatting Rules and Rule Management

CSS applies styles to HTML elements when conditions are met (:hover, .active, [disabled]). Excel's Conditional Formatting works the same way — it applies cell formatting (fill color, font color, borders, icons) automatically whenever a defined condition evaluates to true. Custom rules go beyond the preset options to let you define exact thresholds, specific text, date ranges, blank cells, and error states.

Part 1: Rule Category Overview

Conditional formatting rules are organized into five categories, all accessible from: Home tab > Conditional Formatting

Part 2: Creating a Custom Rule

Home > Conditional Formatting > New Rule...

The New Formatting Rule dialog offers six rule types:

"Format only cells that contain" — Key Options

Part 3: Configuring the Format

Click the Format... button in the rule dialog to open the Format Cells dialog. You can configure:

• Font tab — Bold, italic, underline, font color
• Border tab — Cell border style and color
• Fill tab — Background fill color or pattern

A Preview box shows the combined effect before you confirm.

Part 4: Applying to the Right Range

When creating a rule, the Applies to field in the Manage Rules dialog controls which cells the rule targets.

• Click a cell or drag a range before opening New Rule to pre-populate this field.
• Use absolute references ($A$2:$D$100) to pin the range.
• Use partial references ($A$2:$A$100) to target a single column.

Part 5: Practical Examples

Highlight cells over budget (> $10,000)

• Rule type: Format only cells that contain
• Condition: Cell Value > 10000
• Format: Red fill

Flag blank required fields

• Rule type: Format only cells that contain
• Condition: Blanks
• Format: Yellow fill

Mark errors in formula cells

• Rule type: Format only cells that contain
• Condition: Errors
• Format: Red font + red border

Top 5 sales performers

• Rule type: Format only top or bottom ranked values
• Top 5 items
• Format: Green fill

Part 6: Manage Rules — Reorder, Edit, and Stop If True

CSS has a specificity system — when multiple rules target the same element, the one with the highest specificity wins. Excel's conditional formatting has a priority system that works similarly. The Manage Rules dialog is the control panel for this system: it shows every rule applied to a cell or sheet, lets you reorder them, edit them, and control whether multiple rules can apply simultaneously or whether the first match stops all further evaluation.

Opening the Manage Rules Dialog

Home tab > Conditional Formatting > Manage Rules...

The dialog header contains a dropdown: "Show formatting rules for:"

Each rule is listed with its format preview, the formula or condition, and the "Applies to" range.

Rule Priority — How Conflicts Are Resolved

Rules are evaluated top-to-bottom in the Manage Rules list. The first rule that evaluates to TRUE wins — unless Stop If True is disabled, in which case multiple rules can apply simultaneously (their formats stack or the last applied format is shown).

Use the Up (▲) and Down (▼) arrow buttons in the Manage Rules dialog to change priority. The rule at the top has the highest priority.

Stop If True

The Stop If True checkbox controls whether Excel stops evaluating remaining rules once a rule fires for a cell.

Use it for mutually exclusive conditions — when only one rule should ever apply to a cell at a time:

Example — a status column with three states (Critical, Warning, OK): 1. Rule 1: =$D2="Critical" → Red fill — Stop If True: checked 2. Rule 2: =$D2="Warning" → Yellow fill — Stop If True: checked 3. Rule 3: =$D2="OK" → Green fill

Without Stop If True: if a cell is "Critical", both Rule 1 (red) and all lower rules would evaluate. With Stop If True on Rule 1: once a cell is flagged Critical, Rules 2 and 3 are skipped entirely for that cell.

Leave Stop If True unchecked when rules are additive — for example, one rule bolds the font, another changes the fill color. Both should apply simultaneously.

Editing Rules

Double-click any rule in the Manage Rules list to open the Edit Formatting Rule dialog — the same interface used when creating the rule. You can change the condition, the format, or switch the rule type entirely.

Deleting Rules

To delete a specific rule: 1. Select the rule in the Manage Rules list. 2. Click the Delete Rule button.

To clear all conditional formatting from selected cells: Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells

To clear all conditional formatting from the entire sheet: Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet

Editing the "Applies To" Range

In the Manage Rules dialog, the Applies to column shows the range each rule covers. You can click directly in this field and edit the range reference without reopening the rule editor.

This is useful when you add new rows to a dataset and need to extend the rule's range downward.

Official Resources

• Microsoft Support — Add, change, find, or clear conditional formats
• Microsoft Support — Use conditional formatting to highlight information
• Microsoft Support — Manage conditional formatting rule precedence