Excel Conditional Formatting Based on Another Cell

Mixed references are the entire difficulty here. Get them right and every conditional formatting task becomes straightforward; get them wrong and the rule appears to highlight at random.

Highlighting a whole row from one column

1. Select the data range first — A2:F100, headers excluded. The rule is written relative to this selection, so it matters.
2. Home → Conditional Formatting → New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =$B2="Overdue".
5. Click Format, pick a fill, then OK.

Why $B2 and not B2 or $B$2:

Reference	What happens
$B2	Correct. Column locked, row moves — every cell in row 5 tests B5
B2	Both move, so cell C5 tests C5 and D5 tests D5 — highlights scattered cells
$B$2	Both locked, so every cell tests B2 — all rows highlight or none do

The rule you type is applied to the top-left cell of the range and then offset for every other cell, exactly like filling a formula down and across. Picture what =$B2="Overdue" becomes in cell F50 — it becomes =$B50="Overdue", which is what you want.

Common rules worth having

Goal	Formula
Row where column B matches text	=$B2="Overdue"
Row where a date has passed	=$C2<TODAY()
Due within 7 days	=AND($C2>=TODAY(), $C2<=TODAY()+7)
Cell bigger than the one beside it	=B2>C2
Value above the column average	=B2>AVERAGE($B$2:$B$100)
Duplicate values	=COUNTIF($B$2:$B$100,$B2)>1
Blank cells	=$B2=""
Two conditions together	=AND($B2="Open", $D2>1000)

Notice the pattern: ranges are fully locked with $B$2:$B$100, while the cell being tested keeps its row relative as $B2. That combination is correct for almost every rule you will write.

Formatting based on another cell's value

This is what most people are actually searching for, and the answer is the mixed reference above. A rule on column D that reacts to column B is just =$B2="Overdue" with the range set to column D.

To compare against a single fixed cell — a threshold in H1, say — lock it completely: =$D2>$H$1. Now every row compares its own D against that one cell, and you can change the threshold without touching the rule.

When a rule does not work

• Nothing highlights. Check the "Applies to" range under Manage Rules — it is often narrower than you think. Also check for stray spaces: ="Overdue " never matches "Overdue".
• Wrong rows highlight. The formula was written for the wrong starting row. If the range starts at row 2, the formula must reference row 2.
• Scattered individual cells highlight. Missing the $ on the column.
• Everything or nothing highlights. Too many $ — you used $B$2 where you needed $B2.
• It worked, then stopped after sorting. Rules can fragment when rows move. Open Manage Rules and reset the "Applies to" range to the whole block.

Rules multiply when you copy rows

Worth knowing because it degrades a workbook quietly. Copying and pasting rows duplicates their conditional formatting, and a sheet can accumulate thousands of near-identical rules, each evaluated on every recalculation.

Check under Manage Rules, with "Show formatting rules for" set to This Worksheet. A list that scrolls a long way, or rules whose "Applies to" is a fragmented set of individual cells, means duplication. Delete them and reapply one rule to the whole range.

This is a common cause of a sluggish workbook — see why your Excel file is slow and large.

Order matters when rules overlap

Rules are evaluated top to bottom in Manage Rules, and the first match wins for any given format property. If a row could satisfy two rules, put the more specific one above the general one and use the arrows to reorder.

Stop If True, the tickbox beside each rule, halts evaluation for cells that matched — useful when a "completed" rule should override everything below it.

Frequently asked questions

How do I use conditional formatting based on another cell?

Select your range, choose New Rule → Use a formula, and write it with the column locked and the row relative: =$B2="Overdue". That makes every cell in the row test column B.

How do I highlight an entire row?

Select the whole data range, not just one column, then use a formula with the column locked — =$B2="Overdue". Selecting only column B is why people end up highlighting a single cell.

Why is my conditional formatting highlighting the wrong rows?

The formula was written for the wrong starting row. It applies to the top-left cell of the range and offsets from there, so a range starting at row 2 needs a formula referencing row 2.

What does the dollar sign do?

It locks part of the reference. $B2 locks the column and lets the row move — correct for row highlighting. $B$2 locks both, so every cell tests the same one. B2 locks neither.

How do I highlight dates that have passed?

=$C2<TODAY() with the range covering your rows. For a window, combine them: =AND($C2>=TODAY(), $C2<=TODAY()+7). This only works if the dates are real dates rather than text.

Why did my rules stop working after sorting?

Rules can fragment when rows move, leaving the "Applies to" range as scattered fragments. Open Manage Rules and reset it to the whole block.

Can conditional formatting slow Excel down?

Yes. Copying rows duplicates rules, and thousands of them get evaluated on every recalculation. Check Manage Rules with the scope set to This Worksheet and remove duplicates.

Conclusion

Almost everything here reduces to one habit: select the range first, then write the formula as though it applies to that range's top-left cell, locking the column with $ and leaving the row free.

If a rule misbehaves, check the "Applies to" range before rewriting the formula — a narrower-than-expected range is the more common fault.

Related: highlighting duplicates in Google Sheets covers the same anchoring idea there, and why your Excel file is slow covers the performance cost of duplicated rules.