Conditional formatting formula examples in Excel

Where the formula goes

Select the range, for example A2:F11, then Home › Conditional Formatting › New Rule › Use a formula to determine which cells to format. Type the formula, click Format, pick a fill and confirm. In Google Sheets the same option is Format › Conditional formatting › Custom formula is.

The formula must return TRUE or FALSE (any non-zero number also counts as TRUE). Write it as if it sat in the first cell of the selection, A2 here. Excel then shifts every relative reference for the other cells, exactly as if you had copied the formula down and across.

The $ rule: lock the column, not the row

To colour a full row from one column, lock the column with $ and leave the row free: $E2. Every cell in row 2, from A2 to F2, then looks at E2, and every cell in row 3 looks at E3. Without the dollar sign, B2 would test F2 and C2 would test G2, and the colours land in the wrong places.

Fixed inputs such as the as-of date get two dollar signs, $C$13, so every cell compares with the same date. This is the same absolute-reference logic as in a SUMIFS criteria cell. If a rule colours one row too low or one cell too far right, the references are almost always written for the wrong starting cell.

Ten conditional formatting formula examples

All examples are written for a range starting in row 2, with invoices in A2:F11 as in the sheet below.

• Overdue and still open (whole row): =AND($E2="Open",$C2<$C$13). Use TODAY() instead of $C$13 for a rule that updates every day.
• Due in the next 7 days: =AND($E2="Open",$C2>=$C$13,$C2<=$C$13+7)
• Top 3 amounts: =$D2>=LARGE($D$2:$D$11,3)
• Below average: =$D2<AVERAGE($D$2:$D$11)
• Duplicate customer: =COUNTIF($B$2:$B$11,$B2)>1
• Second and later occurrence only: =COUNTIF($B$2:$B2,$B2)>1 (the range grows down the list)
• Banded rows: =MOD(ROW(),2)=0, which survives sorting and inserted rows
• Weekend dates: =WEEKDAY($C2,2)>5
• Blank required field: =$D2=""
• Text contains a word: =ISNUMBER(SEARCH("hotel",$B2)), not case-sensitive

Test the rule in a helper column first

A rule that colours nothing gives no error message. The fastest way to debug it is to type the same formula into a spare column next to the data and copy it down: you see TRUE or FALSE per row and can fix the logic before it goes into the rule box. Column F of the example does this. Multiplying by 1, =AND(…)*1, turns TRUE and FALSE into 1 and 0, so you can also count and sum the results: 3 overdue invoices, and =SUMPRODUCT(D2:D11,F2:F11) = $6,525.50 overdue. When the column is right, paste the formula without *1 into the rule and delete the helper, or keep it hidden as documentation.

Note the boundary in the overdue rule. INV-405 is due on the as-of date itself, 15 Oct 2026, and is not late yet, so the rule uses <, not <=. Paid invoices never turn red because AND needs both conditions.

Worked example: helper column for an overdue-invoice rule

Column F holds the overdue rule as a formula, times 1 so the result shows as 1 or 0. Rows 14 to 19 count what the other example rules would colour. The invoices are fictional.

Worked example: helper column for an overdue-invoice rule

	A	B	C	D	E	F
1	Invoice	Customer	Due date	Amount	Status	Overdue rule (1 = TRUE)
2	INV-401	Harbor Cafe	28 Sep 2026	1,240.00	Paid	0
3	INV-402	Lumen Hotel	02 Oct 2026	3,860.00	Open	1
4	INV-403	Pier 4 Deli	09 Oct 2026	515.50	Open	1
5	INV-404	Cityline Offices	14 Oct 2026	2,150.00	Open	1
6	INV-405	Lumen Hotel	15 Oct 2026	3,860.00	Open	0
7	INV-406	Northpark Gym	18 Oct 2026	780.00	Open	0
8	INV-407	Harbor Cafe	20 Oct 2026	1,240.00	Paid	0
9	INV-408	Oak & Ivy Bistro	01 Oct 2026	960.00	Paid	0
10	INV-409	Fernwood College	21 Oct 2026	4,420.00	Open	0
11	INV-410	Pier 4 Deli	30 Oct 2026	515.50	Open	0
12
13	As of		15 Oct 2026
14	Overdue invoices					3
15	Overdue amount					6,525.50
16	Top 3 cut-off: 3rd largest amount					3,860.00
17	Average amount (below-average rule)					1,954.10
18	Rows with a repeated customer					6
19	Open and due in the next 7 days					3

In the rule box, write references for the first cell of the range you selected (row 2 here). The as-of date in C13 can be replaced with TODAY().

Several rules on one range: order and Stop If True

When rules overlap, open Conditional Formatting › Manage Rules. Rules higher in the list win when they set the same property, for example both a red and an amber fill. Put the most severe rule on top, or tick Stop If True so that a cell that matched stays out of the rules below. For a clean red, amber, green status it is often easier to calculate the status with IFS in a column and colour that column with three simple equal to rules.

Formula rules vs built-in rules

The built-in rules, such as Top 10, Above Average or Duplicate Values, colour only the cells of the column you selected. A formula rule can colour the whole row, combine conditions with AND and OR and use any other column. In the example, the built-in Top 10 rule set to 3 items would colour the same three amounts as the LARGE formula, $4,420.00 and the two invoices of $3,860.00, but only the formula version can paint the full invoice line.

Common mistakes

• Writing the formula for the wrong cell. The formula is relative to the top-left cell of the selected range. If you select A2:F11 but write the rule for row 1, every colour is shifted by one row.
• No $ before the column. =E2="Open" applied to a whole row makes each cell test a different column. Use $E2.
• Locking the row as well. $E$2 makes every row test the first invoice, so the whole table is coloured or nothing is.
• Comparing with text that looks like a date. $C2<"15/10/2026" compares with text. Put the date in a cell or use DATE(2026,10,15) or TODAY().
• Leaving out the = sign. Without it, Excel stores the entry as text in quotes and the rule never fires. Start every rule with = and check it in Manage Rules.
• Piling up copies of the same rule. Copying and pasting cells duplicates rules for each pasted range. Clean them up in Manage Rules so one rule covers the whole range.