What does Stop If True do, and does rule order matter?

Order matters: Excel checks rules top to bottom in Manage Rules, and later rules can paint over earlier ones. Stop If True halts evaluation for a cell once that rule matches, so lower rules never apply to it.

How do I format an entire row instead of one cell?

Use New Rule → Use a formula with a $ anchoring the column, like =$D2="Done", and set Applies to across all the row's columns. The anchored column stays fixed while the row reference moves down.

Why is conditional formatting making my workbook slow?

Usually it is whole-column rules (A:A) or volatile functions evaluating across a million rows. Narrow each rule's Applies to range to the actual data, like A2:E500, and the lag disappears almost immediately.

How do I copy a conditional formatting rule to another range?

Select a formatted cell, click Format Painter on the Home tab, then drag across the target range — it copies the conditional formatting rule along with the other formatting. Double-click Format Painter to apply it repeatedly.

Conditional Formatting in Excel: The 5 Rules You'll Actually Use

Last updated June 2026

Quick answer

Conditional formatting lives under Home → Conditional Formatting. Five rules cover almost everything: Highlight Cells Rules → Duplicate Values to catch repeats, Less Than 0 in red for over-budget figures, Data Bars for instant magnitude, Top/Bottom Rules → Top 10 Items, and a New Rule formula like =$D2="Done" to color an entire row. The steps below apply one end to end.

How do you apply a conditional formatting rule?

Highlight duplicate values

Select the range first
Click the first cell of the column you want to check, then Ctrl+Shift+Down to select to the end of the data. Conditional formatting applies only to the cells selected when you create the rule, so get this right before opening the menu.
Open the Duplicate Values rule
On the Home tab, choose Conditional Formatting → Highlight Cells Rules → Duplicate Values. In the dialog, leave Duplicate selected (switch to Unique to flag one-offs instead) and pick a fill, then click OK.
Read the result
Every value that appears more than once is now shaded. This finds duplicates in place without deleting anything — useful before you run a true de-dupe pass on the data.
Flag negatives in red
Select a numbers column, then Conditional Formatting → Highlight Cells Rules → Less Than, type 0, and keep the red fill. Over-budget or negative figures now light up automatically as you type.
Add a data bar
Select the column, choose Conditional Formatting → Data Bars, and pick a gradient. Each cell gets an in-cell bar sized to its value, so you read magnitude at a glance without a chart.
Audit everything with Manage Rules
Open Conditional Formatting → Manage Rules, then set Show formatting rules for to This Worksheet. Here you reorder rules with the arrows, edit a rule's range under Applies to, toggle Stop If True, and delete anything stale — the one place to debug formatting that misbehaves.

How do you color a whole row from one cell?

The four rules above format the cell that holds the value. To shade an entire row based on one column, use a formula rule and anchor the column reference with a $. In a task list where column D holds the word Done, select the data rows, choose Conditional Formatting → New Rule → Use a formula to determine which cells to format, and enter the formula below.

=$D2="Done"

The $ before D locks every cell in the row to look at column D, while the unlocked 2 lets the row number step down with each row. Reference the top-left cell of your selection (here row 2). Because the column is anchored but the row floats, Excel evaluates $D2, $D3, $D4 and shades each whole row whose D cell reads Done.

Rule	Ribbon path	When to use
Duplicate Values	Conditional Formatting → Highlight Cells Rules → Duplicate Values	Spot repeated entries before cleaning a list
Less Than 0	Conditional Formatting → Highlight Cells Rules → Less Than	Flag negatives — over-budget lines, losses, shortfalls
Data Bars	Conditional Formatting → Data Bars	Compare magnitudes in a column at a glance
Top 10 Items	Conditional Formatting → Top/Bottom Rules → Top 10 Items	Surface the biggest values (the 10 is adjustable)
Formula rule	Conditional Formatting → New Rule → Use a formula	Color whole rows or anything the presets can't express

The five rules, where they live, and when to reach for each.

Apply formula rules to a defined range like A2:E51, not whole columns like A:E. Whole-column rules ask Excel to evaluate over a million rows and visibly slow the workbook; a tight Applies to range keeps recalculation instant.

Frequently asked questions

What does Stop If True do, and does rule order matter?: Order matters: Excel checks rules top to bottom in Manage Rules, and later rules can paint over earlier ones. Stop If True halts evaluation for a cell once that rule matches, so lower rules never apply to it.
How do I format an entire row instead of one cell?: Use New Rule → Use a formula with a $ anchoring the column, like =$D2="Done", and set Applies to across all the row's columns. The anchored column stays fixed while the row reference moves down.
Why is conditional formatting making my workbook slow?: Usually it is whole-column rules (A:A) or volatile functions evaluating across a million rows. Narrow each rule's Applies to range to the actual data, like A2:E500, and the lag disappears almost immediately.
How do I copy a conditional formatting rule to another range?: Select a formatted cell, click Format Painter on the Home tab, then drag across the target range — it copies the conditional formatting rule along with the other formatting. Double-click Format Painter to apply it repeatedly.