Excel Conditional Formatting Based on Another Cell

Excel conditional formatting can use a formula to format one cell or range based on a value in another cell.

The key is to write the formula for the first cell in the selected range, then use relative and absolute references deliberately.

How conditional formatting based on another cell works

A conditional formatting rule evaluates a formula as TRUE or FALSE.

When it is TRUE for a cell, Excel applies the chosen format.

For example, a rule can highlight a task name in column A when its status in column B is “Overdue.”

Formula-based rules are useful when the value that should trigger formatting is outside the formatted cell, when you need a comparison between columns, or when a built-in rule does not express the condition you need.

The formula should return a logical result; functions such as =$B2="Overdue" do that directly.

Highlight a cell based on a value in another column

Suppose task names are in A2:A20 and their statuses are in B2:B20.

To highlight each task name when its status is Overdue:

Select A2:A20.
Open Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter =$B2="Overdue".
Select Format, choose a fill or font style, and confirm.

Excel evaluates the formula for each selected row.

The dollar sign fixes the status column at B, while the row number remains relative: for A2 it checks B2, for A3 it checks B3, and so on.

Expected result: only task names whose corresponding status is Overdue receive the chosen format.

Use the right cell references

In a conditional formatting formula, references are interpreted relative to the top-left cell of the selected “Applies to” range.

A misplaced dollar sign can make every row check the same cell or make the rule shift into the wrong column.

B2 lets both the column and row adjust.
$B2 locks column B while allowing the row to adjust.
B$2 locks row 2 while allowing the column to adjust.
$B$2 locks both the column and row.

For a row-based rule applied to A2:C20, use =$B2="Overdue" to format all three cells in a row according to that row’s status.

If the Applies to range starts at row 3 instead, adjust the formula to start with row 3, such as =$B3="Overdue".

Compare two cells or columns

You can compare values across columns with a formula rule.

To highlight cells in C2:C50 when they are below the corresponding target in column D, select C2:C50 and use:

=C2<D2

Because both references are relative, each row compares its own values.

For example, if C2 is 8 and D2 is 10, C2 is highlighted; if C3 is 12 and D3 is 10, C3 is not.

To highlight entire rows across A2:D50 using the same comparison, use =$C2<$D2.

Highlight a cell when another cell is blank or nonblank

To highlight A2:A20 when the corresponding cell in column B is blank, use =$B2="".

To highlight when column B contains something, use =$B2<>"".

These formulas treat a formula that returns an empty string as blank too, which is often useful in worksheets containing formulas.

If spaces may be entered accidentally, a cell that looks empty may not actually be blank.

You can ignore leading and trailing spaces with =TRIM($B2)="".

TRIM removes ordinary spaces, though it does not remove every possible nonprinting character.

Match text without case sensitivity

Excel’s standard text comparison with the equals operator is not case-sensitive.

The rule =$B2="approved" matches Approved, APPROVED, and approved.

To highlight rows when B contains a phrase anywhere in the cell, use =ISNUMBER(SEARCH("urgent",$B2)).

SEARCH is case-insensitive; use FIND instead if the match must respect capitalization.

To test whether a cell equals one of several statuses, use =OR($B2="Overdue",$B2="Blocked").

For a larger list of alternatives, maintaining the statuses in a separate range and using a lookup-based formula may be easier to update than editing the rule each time.

Common problems and fixes

The wrong rows are highlighted: Check that the formula’s row number matches the first row of the Applies to range.
Every row checks the same status cell: Replace $B$2 with $B2 when the row should change but the column should stay fixed.
The rule applies to too many cells: Open Conditional Formatting Rules Manager and inspect the Applies to range.
A number comparison gives unexpected results: Confirm that the values are numbers rather than text that only looks numeric.

Convert or clean imported data when needed.
A text rule misses a value: Look for extra spaces, different punctuation, or spelling differences.

Use TRIM for ordinary spaces or SEARCH for a partial match.
Another color appears instead: Review overlapping rules and their order in Rules Manager.

If multiple rules apply, rule priority and the Stop If True option can affect the displayed format.

Compatibility and practical limits

Formula-based conditional formatting is supported in modern desktop versions of Excel, including Microsoft 365 and Excel 2021, 2019, and 2016, as well as Excel for the web.

Menu names and some rule-management controls can vary by version.

Older Excel versions may have lower limits on the number of distinct conditional formats in a workbook.

Keep formulas straightforward when applying a rule to thousands of cells, since complex calculations and large formatted ranges can affect workbook responsiveness.

A rule formula should be written for the top-left cell of its Applies to range, and it should return a result Excel can evaluate as TRUE or FALSE.

After changing the range, revisit the formula references so the rule still checks the intended cell for each formatted row.