Excel's built-in conditional formatting rules look only at the cell being formatted. To color a cell based on a different cell, you use the "Use a formula to determine which cells to format" option. The trick is entirely in how you lock the references with the dollar sign.
The Steps
- Select the cells you want to format (the ones that will change color).
- Home tab → Conditional Formatting → New Rule.
- Choose Use a formula to determine which cells to format.
- Enter a formula that returns TRUE for the cells to format.
- Click Format, pick a fill or font color, then OK.
Format a Cell Based on Another Cell
Say you select B2 and want it to turn green when C2 says "Paid":
=$C2="Paid"
The $ before C locks the column, so every cell in column B always checks column C on its own row. Leaving the row number unlocked (2, not $2) lets the rule move down the rows.
Highlight an Entire Row
This is the most common use. Select the whole data range (for example A2:D100), then use a formula that points at one column with the column locked:
=$D2="Overdue"
Every cell in the row checks column D of its own row, so the entire row highlights when column D is "Overdue". The rule for locking is simple: lock the column ($D), leave the row free (2) when you want to read one column across a whole row.
Compare Two Cells
Highlight a cell when it is greater than the target in another cell:
=B2>$E$1
Here $E$1 is fully locked (both column and row) because every cell compares against the same single target cell.
Reference Locking Cheat Sheet
- $C2 - always column C, row moves. Use to check one column across many rows.
- C$2 - always row 2, column moves. Use to check one row across many columns.
- $C$2 - always the exact cell C2. Use for a single fixed target.
- C2 - fully relative, moves both ways. Rarely what you want in a cross-cell rule.
Common Problems
- Only the first cell formats. You locked the row by accident ($C$2). Unlock the row so the rule moves down: $C2.
- The wrong rows highlight. Your selection did not start on the row the formula assumes. If the formula says row 2, the selection must start on row 2.
- Nothing happens. The formula does not return TRUE/FALSE, or it points at the wrong column. Test it in a spare cell first; it should show TRUE or FALSE.
- Text match fails. Conditional formatting text comparisons are not case sensitive, but stray spaces break them. Wrap the reference in TRIM if the data may have trailing spaces.
Frequently Asked Questions
How do I highlight a row based on one cell's value?
Select the whole row range, create a formula rule, and lock only the column of the cell you are testing: =$D2="Overdue". The locked column and free row make every cell in the row read column D of its own row.
What does the dollar sign do in a conditional formatting formula?
The dollar sign locks part of a reference so it does not shift as the rule is applied across your selection. $D2 keeps the column fixed on D while letting the row change per line, which is exactly what you need to read one column across many rows.
Why does only the top cell get formatted?
You almost certainly locked the row ($D$2). With the row locked, every cell checks the same single cell instead of its own row. Change it to $D2 so the row reference moves.
Can I format based on a cell in another sheet?
Not directly in the rule box, because conditional formatting formulas cannot reference another sheet. Pull the other sheet's value into a helper cell on the current sheet first, then point the rule at that helper cell.