Conditional formatting in Excel, with formula rules

In short: select a range, then go to Home > Conditional Formatting. The built-in rules cover simple cases. To color whole rows, use a formula rule such as =$C2>=5000. The $ in front of the column is what makes it work.

Try it now

The sheet lists the sales of a team. Every row with sales of at least 5000 should be highlighted, name, region and sales alike. Type the rule formula into the yellow cell. It applies to the whole framed range.

Task Use conditional formatting to highlight the whole rows A2:C8 where sales are at least 5000. Write the rule formula as it applies to the first cell A2.

	A	B	C
1	Name	Region	Sales $
2	Jana Weber	North	4200
3	Murat Kaya	South	7800
4	Lena Hoffmann	West	5100
5	Tom Schulz	East	2900
6	Sara Klein	North	6400
7	Ali Demir	South	4999
8	Mia Wagner	West	5000

The built-in rules

Home > Conditional Formatting offers several groups of ready-made rules. Most everyday tasks need no formula at all.

Menu item	What it does	Example
Highlight Cells Rules	Colors cells that meet a condition: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values	Sales below 3000 in red
Data Bars	Draws a bar inside each cell, sized by its value	Compare sales at a glance
Color Scales	Shades cells along a gradient from low to high	Temperatures or workload
Icon Sets	Adds arrows, traffic lights or circles depending on the value	Traffic light for targets

All of these only format the cell whose value they check. As soon as you want to color a different cell than the one being tested, you need a formula.

Formula rules: highlighting whole rows

Select the whole range, say A2:C8, starting from the top left. Choose Home > Conditional Formatting > New Rule, then Use a formula to determine which cells to format. Write the condition as if it sat in the first cell, A2, and pick a color under Format.

Excel then shifts the formula for every cell in the range, exactly like filling. To make every cell in a row check the same column, lock just the column: =$C2>=5000. The row stays relative, so row 3 checks C3. If the $ rules are still fuzzy, the page on absolute references walks through them.

One more: flag open invoices

Same technique, this time with text: every invoice with the status "open" should stand out across the whole row. Text inside a formula goes in quotation marks.

Task Use conditional formatting to highlight the whole rows A2:D8 whose status is “open”. Write the rule formula as it applies to the first cell A2.

	A	B	C	D
1	Invoice	Customer	Amount $	Status
2	R-2301	Becker Ltd	1250	paid
3	R-2302	Northlight Inc	840	open
4	R-2303	Shaw & Partners	3100	paid
5	R-2304	Becker Ltd	460	open
6	R-2305	Weber Logistics	2275	paid
7	R-2306	Northlight Inc	990	paid
8	R-2307	Hansen Group	1580	open

Managing rules

Home > Conditional Formatting > Manage Rules opens the Conditional Formatting Rules Manager. Switch the drop-down at the top to "This Worksheet" to see every rule, not just those on the selected cells. Here you edit or delete rules, change their range under "Applies to" and set their order with the arrow buttons. For a quick cleanup there is also Clear Rules right in the menu.

Common mistakes

The $ in the wrong place

Without a $ the column moves too: column A checks C2, column B already checks D2, and only part of the row lights up. With $C$2 the row is locked as well, so every row depends on C2. This often slips in unnoticed, because clicking a cell inside the rule dialog inserts $C$2. Press F4 twice until it reads $C2.

A formula written for the wrong first cell

The formula has to match the top-left cell of the range. Select from A1 including the header but write =$C2>=5000, and every highlight lands one row off. Either select from A2 or adjust the formula to the first row.

Overlapping rules

When two rules hit the same cell and both set a fill color, the rule higher up in the Rules Manager wins. Check the order if a color does not show. Copying and pasting also tends to create duplicate rules with fragmented ranges, so a look into Manage Rules pays off.

Text without quotation marks

=$D2=open highlights nothing, because Excel reads "open" as a name. The correct rule is =$D2="open".

Conditional formatting in German Excel

English	German
Home > Conditional Formatting	Start > Bedingte Formatierung
Highlight Cells Rules	Regeln zum Hervorheben von Zellen
Data Bars / Color Scales / Icon Sets	Datenbalken / Farbskalen / Symbolsätze
Use a formula to determine which cells to format	Formel zur Ermittlung der zu formatierenden Zellen verwenden
Manage Rules	Regeln verwalten
=AND($C2>=5000,$B2="North")	=UND($C2>=5000;$B2="Nord")

Which Excel versions have conditional formatting?

All current ones: Excel 2016, 2019, 2021, 2024 and Microsoft 365, plus Excel for Mac and Excel for the web. The menus look very much alike everywhere.

Frequently asked questions