Conditional Formatting with Formulas: Highlight the Whole Row

Q: Which rule wins when two rules match?

The one higher in Manage Rules . Use the arrows to change the order.

By Ravindra Bagale, Excel & Data Analytics trainer LinkedIn Published: 6 October 2026 Last updated: 6 October 2026

Raja has eight invoices. He wants every Pending invoice to turn light red across the whole row, and the big pending ones (over ₹5,000) to turn darker. The built-in “Text that contains” rule colours only the Status cell. A formula rule colours the whole row.

A formula rule colours a cell when your formula returns TRUE. You write the formula for the first row of the selection; Excel moves it down for every other row. The $ before the column letter keeps every cell looking at the Status column.

Download the practice file (conditional-formatting-with-formulas.xlsx)

Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

Select A2:D9 (the data, without the header).
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Type =$D2="Pending".
Click Format > Fill, pick light red, click OK twice.

=$D2="Pending"
=AND($D2="Pending",$C2>5000)

The invoice list

Invoice	City	Amount	Status
INV-101	Pune	4200	Paid
INV-102	Nashik	7800	Pending
INV-103	Solapur	3100	Pending
INV-104	Nagpur	9500	Paid
INV-105	Kolhapur	6400	Pending
INV-106	Pune	2500	Pending
INV-107	Sambhaji Nagar	5100	Paid
INV-108	Nashik	8800	Pending

Why =$D2 and not =D2 or =$D$2

You type	What Excel checks in row 5, column B	Result
`=$D2="Pending"`	D5 (column fixed, row moves)	Whole row coloured correctly
`=D2="Pending"`	E5 (column moves too)	Wrong cells coloured
`=$D$2="Pending"`	D2 every time	All rows coloured or none

The rule is written for the top-left cell of the selection (A2). Excel shifts it for every other cell, just like copying a formula.

Add the second rule: big pending invoices

Keep A2:D9 selected and open Home > Conditional Formatting > New Rule again.
Choose Use a formula to determine which cells to format.
Type =AND($D2="Pending",$C2>5000).
Pick a darker red fill and click OK.
Open Home > Conditional Formatting > Manage Rules and move this rule to the top so it wins.

Check with formulas: =COUNTIF(D2:D9,"Pending") gives 5 pending rows and =COUNTIFS(D2:D9,"Pending",C2:C9,">5000") gives 3 big ones (INV-102, INV-105, INV-108).

More formula rules you will use

Highlight	Formula (data starts in row 2)
Due date (column E) already passed	`=$E2<TODAY()`
Every second row	`=MOD(ROW(),2)=0`
Duplicate invoice numbers	`=COUNTIF($A$2:$A$9,$A2)>1`
Amount above the average	`=$C2>AVERAGE($C$2:$C$9)`
Rows where the city cell in G1 matches	`=$B2=$G$1`

Ravindra Bagale's Tip

Test the rule as a normal formula first. Type =$D2="Pending" in an empty column and copy it down. If you see TRUE on the right rows, paste the same formula into the rule.

Practice task

Open the Invoices sheet. The rules are already set; check that five rows are light red and three are darker.
Change INV-104 to Pending. It turns dark red because 9500 is over 5000.
Open Home > Conditional Formatting > Manage Rules and read both formulas.
Add a rule =$B2="Pune" with a blue fill.
Check the counts in G2 and G3.

Learn it properly

Course lessons:

Related guides: IF Function · COUNTIF and COUNTIFS

Frequently asked questions

Why is my conditional formatting colouring the wrong rows?

The formula was written for a different first row, or the $ signs are wrong. Select the range starting at row 2 and use $D2.

How do I highlight the whole row?

Select all columns of the data first, then use a formula rule with the column fixed, like =$D2="Pending".

Which rule wins when two rules match?

The one higher in Manage Rules. Use the arrows to change the order.

Can I use another sheet in the rule?

Yes, in current Excel versions you can refer to another sheet directly, or use a named range.