Conditional Formatting Formulas: Custom Rules in Excel

A custom conditional formatting formula lets you format cells based on logic the built-in rules can't express — comparing other cells, checking multiple conditions, or coloring an entire row from one flag column. The formula must evaluate to TRUE or FALSE, and getting the $ anchoring right is what decides whether it works on every row or breaks after the first one.

How do I write a custom conditional formatting formula?

Select the range first, open New Rule, choose the formula option, and type a formula that returns TRUE or FALSE — never wrap it in IF(), since the rule engine already expects a boolean result.

1. Select the range (e.g. B2:B50)
2. Home tab → Conditional Formatting → New Rule
3. Choose "Use a formula to determine which cells to format"
4. Enter: =B2>100
5. Click Format, choose a fill or font style
6. OK

Excel evaluates the formula relative to the active cell in the selection (usually the top-left one), then applies the same logic to every other cell in the range the way a dragged formula would.

How do I highlight an entire row based on one cell's value?

Select the full row range, not just the flag column, and lock only the column in the formula so the row reference stays free to shift down the selection.

Goal: highlight the whole row when Status (column D) = "Complete"

1. Select A2:E100 (entire data block, A2 must be the active cell)
2. New Rule → Use a formula
3. Formula: =$D2="Complete"
4. Set a fill color → OK

Because the column is locked ($D) and the row isn't, row 2's copy of the rule checks D2, row 3's checks D3, and so on — every cell across a matching row gets formatted, not just column D.

What is the difference between relative and absolute references in a CF formula?

A conditional formatting formula behaves exactly like a formula you'd drag across a range: a bare reference (D2) shifts with every cell, while a $ pins that part in place. The table below is the anchoring cheat sheet for a rule applied to A2:E100.

Formula	Behavior	Use case
=$D2="Complete"	Column locked, row moves	Whole-row highlighting
=D$2="Complete"	Row locked, column moves	Whole-column highlighting
=$D$2="Complete"	Both locked	Every cell checks D2 only
=D2="Complete"	Nothing locked	Each cell checks a different cell — usually wrong

What formula recipes are useful for common conditional formatting tasks?

Most real-world rules are variations on comparing a cell to a fixed value, another cell, or a running count — the same anchoring logic applies to all of them.

Duplicates in a column (select A2:A100, active cell A2):
=COUNTIF($A$2:$A$100, A2)>1

Compare two columns for mismatches (select A2:A100):
=$A2<>$B2

Alternate row shading (select A2:E100):
=MOD(ROW(),2)=0

Highlight past-due dates that aren't complete (select A2:E100):
=AND($A2<TODAY(), $D2<>"Complete")

The duplicate check reuses COUNTIF's counting logic; see highlighting duplicates with conditional formatting for the built-in rule that covers the simple case without a custom formula.

What are the most common mistakes with conditional formatting formulas?

Nearly every broken rule comes down to $ placement, the wrong active cell, or an unnecessary IF() wrapper — all three are easy to check once you know where to look.

• Missing $ on the column: =D2="Yes" instead of =$D2="Yes" — the column drifts as the rule copies across the row
• Writing the formula for the wrong cell: if the selection is A2:E100, the formula must be written as if the active cell is A2, not A1 or D2
• Wrapping in IF(): use =B2>100, not =IF(B2>100,TRUE,FALSE) — the rule already expects a boolean
• Text that looks like a number: a value imported as text (left-aligned "50") never satisfies =B2>40; wrap the comparison in VALUE() or fix the source data
• Applies-to range too narrow: the rule works when tested in a spare cell but does nothing on the sheet because it was only applied to part of the range