How to use conditional formatting in Excel

Q: How do I remove conditional formatting?

Select the cells, then Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Clear Rules from Entire Sheet removes every rule of the sheet. The values stay.

Q: Can a rule use a value from another cell?

Yes. Type a reference such as =$E$1 instead of a value, or click the cell while the box is active. Excel inserts it with $ signs.

Last updated October 30, 2026

Conditional formatting colours cells automatically when they meet a condition. Select the cells, go to Home > Conditional Formatting > Highlight Cells Rules, choose a rule such as Greater Than, type a value (or a reference like =$E$1) and pick a format. For anything more specific, use New Rule > Use a formula to determine which cells to format, for example =$D2>1000 to highlight whole rows.

Where to find it in Excel

Home > Conditional Formatting > Highlight Cells Rules / New Rule

Highlight Cells Rules: Ready-made rules: Greater Than, Less Than, Between (both ends included), Equal To, Text that Contains, A Date Occurring and Duplicate Values.
Format: A preset such as Light Red Fill with Dark Red Text, Yellow Fill with Dark Yellow Text or Green Fill with Dark Green Text, or Custom Format for your own font, border and fill.
Use a formula: In New Rule, a formula that returns TRUE or FALSE, written for the active cell (usually the top-left one). Excel shifts it for every other cell like a copied formula, so $ decides what stays fixed.
Top/Bottom, Data Bars, Color Scales, Icon Sets: Other kinds of rules in the same menu: the top 10 items or those above average, bars inside the cells, colour gradients and icons. They work on the same principle, but aren’t practised here.
Manage Rules: The Rules Manager lists the rules of the selection or the sheet. You can edit a rule, change its Applies to range, move it up or down and tick Stop If True.

Practise conditional formatting here

Select cells and click Conditional Formatting… above the formula bar, as you would on the Home tab in Excel. The grid colours the cells your rule matches, live.

Exercise 1 of 1

Highlight the best sales

Highlight the sales above 5000 in B2:B9 with Green Fill with Dark Green Text. Select the range, click Conditional Formatting and use Greater Than.

Rep	Sales ($)
Olivia Reed	4,200
James Carter	6,100
Sofia Patel	3,800
Liam Brooks	7,450
Emma Hughes	5,000
Noah Bennett	2,900
Ava Collins	5,600
Ethan Ward	4,999

How to add conditional formatting, step by step

Select the cells to format, for example B2:B9. For whole rows, select the whole table without its headers, such as A2:D9.
On the Home tab, click Conditional Formatting, then Highlight Cells Rules and the rule you need, or New Rule for a formula.
Type the value, or a reference such as =$E$1. For a formula rule, write the formula for the top-left cell of the selection, such as =$D2>1000.
Choose the format and click OK. The colours update by themselves whenever the data changes.

Conditional formatting examples

Sales above 5000

5000

Greater Than with 5000 highlights 6100, 7450 and 5600, but not 5000 itself. Use Between or a formula with >= to include it.

A threshold in a cell

=$E$1

Less Than with =$E$1 compares every cell with the pass mark in E1. Change E1 and the highlights move. Without the $, each row would look one row lower.

Whole rows

=$D2>1000

A formula rule on A2:D9. $D keeps every cell of a row on column D; the row number stays relative, so row 3 tests D3.

Overdue and unpaid

=AND($C2<TODAY(),$D2="Unpaid")

Highlights invoices whose due date has passed and that aren’t paid. Replace TODAY() with a cell such as $G$1 to check a fixed date.

Every other row

=MOD(ROW(),2)=0

Bands the even rows of the range. Tables do this for you with banded rows, but the formula works on any range.

Common problems and how to fix them

Problem	Cause	Fix
Only the first row is right	The formula locks the row (`=$D$2>1000`), so every row tests D2.	Lock only the column: `=$D2>1000`.
Only one column of the row lights up	The column isn’t locked (`=D2>1000`), so cell B2 tests E2, C2 tests F2 and so on.	Put a $ before the column letter: `=$D2>1000`.
Highlights are one row off	The formula was written for another row than the top-left cell of the range, for example `=$D1` on a range starting in row 2.	Write the formula for the first row of the selection, or select from the top.
Nothing changes when the threshold cell changes	The value was typed (60) instead of referenced, or the rule points to the wrong cell.	Edit the rule in Manage Rules and use `=$E$1`.
The wrong colour wins	Two rules apply to the same cell; the higher one in Manage Rules takes priority.	Reorder the rules with the arrows, or tick Stop If True on the rule that should win.
Text numbers aren’t highlighted	Numbers stored as text are always “greater” than numbers, or don’t match number rules at all.	Convert them to numbers (Data > Text to Columns, or multiply by 1).

Highlight Cells Rules or a formula?

Highlight Cells Rules are fastest when the condition is about the cell itself: above, below, between, equal, containing a text, or duplicated. A formula rule can test other cells, combine conditions with AND and OR, and format whole rows, at the cost of getting the $ right. Unlike a fill applied by hand, both keep working when the data changes.

Frequently asked questions

How do I remove conditional formatting?

Select the cells, then Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Clear Rules from Entire Sheet removes every rule of the sheet. The values stay.

What happens when two rules match the same cell?

Excel applies them in the order of Manage Rules. If their formats conflict, for example two fills, the rule higher in the list wins; formats that don’t conflict, such as a fill and a bold font, both apply. Stop If True prevents the rules below from applying.

How do I highlight a whole row?

Select the table without headers, use New Rule > Use a formula, and lock the column of the cell you test: =$D2>1000.

Can a rule use a value from another cell?

Yes. Type a reference such as =$E$1 instead of a value, or click the cell while the box is active. Excel inserts it with $ signs.

Does conditional formatting slow Excel down?

Many rules over whole columns, especially with volatile functions such as TODAY() or OFFSET(), can. Apply rules to the used range and delete duplicated rules in Manage Rules.

Keep practising

All 5 conditional formatting exercises, with explanations and progress tracking.

Open the conditional formatting exercises