Conditional Formatting Based on Another Cell in Excel

Part of the free Module 7: Conditional Formatting · Lesson 14 of 15 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted. The in-cell checkbox control is Excel 365 only.

Conditional formatting based on another cell means the cell that changes colour is not the cell being tested. You do it with a formula rule: select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and write a formula that points at the other cell, such as =$C2="Closed" for a status column, =$C2=$H$1 for a drop-down selector, or =$E2=TRUE for a checkbox.

Table of Contents

How a rule reads a different cell

Every built-in rule type looks only at the cell it formats. A formula rule is different: Excel evaluates the formula for each cell in the Applies to range and formats the cell when the result is TRUE, but the formula can reference any cell on the sheet, or on another sheet in the same workbook. That is what makes “format this cell because of that cell” possible.

The formula is written for the top-left cell of the Applies to range. Excel then shifts it for every other cell exactly as if you had filled a formula down and across the block. So the reference you type decides which “other cell” each formatted cell reads:

Reference	Locked part	Which cell is read	Use for
`C2`	None	A cell that moves with the formatted cell in both directions	Comparing two parallel columns cell by cell
`$C2`	Column	Column C of the same row	Highlighting a whole row based on one column
`C$2`	Row	Row 2 of the same column	Highlighting a whole column based on a header cell
`$C$2`	Both	Always cell C2	One input cell: a search box, a drop-down or a checkbox

Two practical habits help. Select the range starting from its top-left cell so that cell is active. And press F2 after clicking in the formula box before using the arrow keys; otherwise the arrow keys insert cell references instead of moving the cursor.

Step by step: colour a row based on the value in one column

The sample is an order list in A1:E16: Order ID, Customer, Amount, Status and Owner. The aim is to shade every row whose Status is Closed.

Select A2:E16, starting from A2.
Go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format.
Type =$D2="Closed". The column is locked so every cell in the row reads column D; the row is relative so row 3 reads D3, row 4 reads D4 and so on.
Click Format, choose a grey fill on the Fill tab, click OK twice.

Text comparison in a rule is not case-sensitive, so “closed” and “CLOSED” both match. If you want the highlight only in the Status cell, apply the rule to D2:D16 with the same formula; if you want it in column A only, apply it to A2:A16 and keep $D2.

Compare one cell with another cell in the same row

To flag Actual below Target when Target is in column C and Actual in column D, apply =$D2<$C2 to the rows. To colour only the Actual cell, apply =D2<C2 to D2:D16; the relative references work here because each formatted cell reads the cells in its own row. A percentage version is =$D2<$C2*0.9 for more than 10 percent below target. Blanks in the Actual column count as 0 and will be flagged; add $D2<>"" inside AND to ignore them.

Conditional formatting based on a drop-down list

A drop-down list made with Data > Data Validation > List is just a cell that holds one of a fixed set of values. Two patterns cover almost every need.

Pattern 1: a drop-down inside each row

The Status column itself is a drop-down with Open, In Progress and Closed. Create one rule per value, all applied to A2:E16:

Rule formula	Format
`=$D2="Open"`	Light red fill
`=$D2="In Progress"`	Yellow fill
`=$D2="Closed"`	Green fill

Choosing a value from the drop-down changes the row colour immediately. The list values must match the rule text exactly, apart from case, so keep the validation list and the rules in step. A safer version references the list cells instead of typing the text: if the list lives in H2:H4, use =$D2=$H$2, =$D2=$H$3 and =$D2=$H$4.

Pattern 2: one drop-down that filters the whole table

Put a single drop-down in H1 that lists the owners. Apply one rule to A2:E16 with the formula =$E2=$H$1. Here the first reference has only the column locked (each row reads its own Owner) and the second is fully locked (every row reads the same selector). Pick a name in H1 and every order belonging to that owner lights up. To handle a blank selector without colouring everything, use =AND($H$1<>"",$E2=$H$1). A search-box variant uses =AND($H$1<>"",ISNUMBER(SEARCH($H$1,$B2))), which highlights rows where the customer name contains the typed text.

Conditional formatting based on a checkbox

A checkbox gives you a TRUE or FALSE value, and a formula rule can read it directly. How you get that value depends on the kind of checkbox.

Excel 365 in-cell checkboxes

In Excel 365, select the cells and choose Insert > Checkbox (in the Cell Controls group). The checkbox lives in the cell and the cell’s value is TRUE when ticked and FALSE when not. To strike through a completed task, select A2:E16 and use =$E2=TRUE, or simply =$E2, because TRUE on its own is enough for the rule. On the Format dialog’s Font tab, tick Strikethrough and pick a grey colour. Ticking the box in column E instantly strikes through the row.

Form Control checkboxes in older Excel

In Excel 2016, 2019 and 2021, enable the Developer tab, then use Developer > Insert > Check Box (Form Control). A Form Control checkbox floats above the sheet and has no value until you link it: right-click it, choose Format Control, and set Cell link to a cell such as F2. That cell then shows TRUE or FALSE. Write the rule against the linked cell, for example =$F2=TRUE, and use a white font on the linked cells if you want to hide the words. Each checkbox needs its own link cell, so this is more work than the 365 control for a long list.

Worked example: task list with status colours and a done checkbox

Task	Owner	Priority	Status	Done
Write proposal	Priya	High	In Progress	FALSE
Book venue	Amit	Low	Closed	TRUE
Send invites	Neha	High	Open	FALSE
Order badges	Amit	Medium	Open	FALSE

Data is in A2:E5. Column D is a Data Validation list, column E holds checkboxes, and H1 is a drop-down of owner names. Apply these rules to A2:E5, in this order from the top of the Rules Manager:

Strikethrough, grey font: =$E2=TRUE
Bold font: =AND($H$1<>"",$B2=$H$1)
Light red fill: =$D2="Open"
Yellow fill: =$D2="In Progress"

Result: Book venue is struck through because its checkbox is ticked. Send invites and Order badges are red because their status is Open. Write proposal is yellow. Choose Amit in H1 and the Book venue and Order badges rows turn bold as well, because bold is a font property and the fills are not, so the rules do not conflict. Tick the checkbox on Send invites and the strikethrough appears on top of the red fill; the row keeps its fill because rule 1 does not set one.

Tips and common mistakes

Lock the column, not the row, for whole-row rules. $D2 is right; $D$2 makes every row read the same cell and either everything or nothing lights up.
Match the drop-down text exactly. A trailing space in the validation list (“Closed “) stops the rule matching. Point the rule at the list cells instead of retyping the text.
Numbers stored as text do not compare. =$C2>100 is FALSE for “100” typed as text. Convert the column first.
A blank selector matches blank cells. Wrap the test in AND($H$1<>"",...) so an empty drop-down highlights nothing.
Do not write =IF($D2="Closed",TRUE,FALSE). The comparison already returns TRUE or FALSE; the IF adds nothing.
Another sheet is fine, another workbook is not. =$D2=Settings!$B$1 works in Excel 2010 and later; references to a different file are refused.
Form Control checkboxes need a Cell link. Without it the box has no value and the rule can never see it.

Errors and how to fix them

Symptom	Cause	Fix
Only the tested cell changes colour	Rule applied to one column, or column not locked	Apply to all columns and use `$D2`
Every row changes at once	Both column and row locked (`$D$2`)	Remove the dollar before the row number
Highlights are one row off	Active cell was not the top-left cell when the rule was made	Edit the rule so the row number equals the first Applies to row
Drop-down value never matches	Extra space or different spelling in the list	Reference the list cells with `$H$2` style references
Checkbox ticks but nothing happens	Form Control has no Cell link	Right-click > Format Control > Cell link
You cannot use references to other workbooks	Formula points at another file	Copy the lookup values into the same workbook

Practice exercise

Open the course practice file or build a 15-row task list, then:

Add a Data Validation drop-down with Open, In Progress and Closed and colour each row by status with three formula rules.
Put an owner drop-down in H1 and make the matching rows bold with =$B2=$H$1, then fix the blank-selector problem with AND.
Add checkboxes (Insert > Checkbox in 365, or a Form Control with a Cell link) and strike through ticked rows.
Add a Target and an Actual column and colour only the Actual cell red when it is below Target, using relative references.
Move the checkbox rule to the bottom of the Rules Manager and note what changes; then move it back.

Key takeaways

Only the formula rule type can format a cell based on a different cell.
$D2 colours a whole row from one column; $H$1 reads a single selector cell; D2 compares parallel columns.
A drop-down is an ordinary cell, so rules test its value with =; reference the list cells to avoid typing mismatches.
Excel 365 checkboxes store TRUE or FALSE in the cell; Form Control checkboxes need a linked cell first.
Guard selector-driven rules with AND($H$1<>"",...) so an empty selector highlights nothing.
Rules that set different properties (fill, font, strikethrough) stack; rules that set the same property follow the order in the Rules Manager.

Related lessons

Frequently asked questions

How do I format a cell based on the value of another cell in Excel?

Select the cell or range to format, open Conditional Formatting > New Rule > Use a formula to determine which cells to format, and write a formula that references the other cell, for example =$D2="Closed". Excel applies the format wherever the formula is TRUE. Lock the column with a dollar sign when the rule covers several columns.

Can conditional formatting change a whole row based on a drop-down selection?

Yes. Apply a formula rule to all the columns of the table with a formula such as =$D2="Closed", where column D holds the drop-down. Each row reads its own drop-down cell because the column is locked and the row is not. Add one rule per list value if each needs a different colour.

How do I strike through a row when a checkbox is ticked?

With Excel 365 checkboxes, the cell value is TRUE when ticked, so the rule =$E2=TRUE applied to the row does it; set Strikethrough on the Font tab of the Format dialog. With a Form Control checkbox, first set a Cell link in Format Control and reference that linked cell instead.

Why does my rule highlight every row or no rows at all?

The reference is fully locked, such as $D$2, so every row reads the same cell. Change it to $D2 so the row part moves. The other common cause is that the active cell was not the top-left cell of the selection when you wrote the rule, which shifts every reference by that offset.

Can the other cell be on a different sheet?

Yes, in Excel 2010 and later a formula rule can reference cells on another worksheet in the same workbook, for example =$D2=Lists!$B$1. It cannot reference another workbook; Excel shows a message saying references to other workbooks are not allowed. Copy the values into the current workbook instead.

Want the finished version? Ready-made Excel trackers and dashboards with status colours and checkbox-driven formatting are available at NextGenTemplates.com.

← PreviousHighlight Dates in Excel: Due Today, Overdue, This Week and Weekends All lessonsConditional Formatting Next →Conditional Formatting Rule Precedence: Rule Order and Stop If True