Conditional Formatting & Formatting Rules: Rules That Style Cells On Their Own

Conditional formatting is Excel's "if this, then format that" engine — or you can ask AI to build the rules for you. Rules change a cell's appearance automatically based on its value, and update live as your data changes.

Table of Contents

• What Is Conditional Formatting?
• Your First Rule
• Create a Custom Rule
• The Formatting Rules Manager
• Edit, Delete, and Prioritize Rules
• Clear Formatting
• Do It With AI

What Is Conditional Formatting?

A conditional rule is a condition with a format. If a cell's value meets the condition — for example, it's above 100 — the format applies. If not, the cell stays plain. When the value changes, the format updates instantly.

The rules live under Home → Conditional Formatting, grouped into:

• Highlight Cells Rules — duplicates, text that contains, between, equal to.
• Top/Bottom Rules — top 10, above average, below average.
• Data Bars / Color Scales / Icon Sets — the visualization presets (covered in Tutorial 5).
• New Rule — a full custom wizard for tricky conditions.

The difference from manual formatting: conditional formatting is live. Change a number and the format re-evaluates on its own.

Your First Rule

Highlight cells above average — the simplest starting point:

1. Select your data range.
2. Go to Home → Conditional Formatting → Top/Bottom → Above Average.
3. Confirm the default color by clicking OK.

Every cell above the average gets colored instantly. That's a working conditional rule.

Create a Custom Rule

For more control than presets, build your own rule:

1. Go to Home → Conditional Formatting → New Rule.
2. Choose Format only cells that contain.
3. Set the condition, for example greater than 100.
4. Click Format, pick a fill and font.
5. Click OK.

You've now defined "if a cell is over 100, tint it." The rule applies to whatever range was selected.

The Formatting Rules Manager

Once a range has several rules, the Formatting Rules Manager becomes the command center.

1. Select the range.
2. Go to Home → Conditional Formatting → Manage Rules.
3. Under Show formatting rules for, pick This Worksheet to list everything.

The manager shows every rule — its order, its friendly name, the range it targets:

Edit, Delete, and Prioritize Rules

• Edit — select a rule, click Edit Rule, adjust it, then OK.
• Delete — select a rule and hit Delete Rule.
• Reorder — use the Up / Down buttons. If two rules match the same cell, the rule higher in the list takes precedence when their formats conflict.

For cells covered by several rules, tick Stop If True on the rule that should take effect — once it matches, all lower-priority rules on that cell are skipped.

Clear Formatting

To wipe out conditional formatting without touching your data:

1. Select the formatted cells.
2. Go to Home → Conditional Formatting → Clear, then choose Clear Rules from Selected Cells.

Only the formatting is removed — the data stays untouched.

Do It With AI

Rule creation is a natural-language task, so Copilot is ideal for it:

"Highlight cells in the Sales column that are above 100 in red." "Highlight the top 10 values in column D."

Copilot builds the rule for you, and it still appears in the Rules Manager, where you can edit, reorder, or delete it exactly as described above. Ask follow-ups like "make the top values orange instead" and it updates the rule.