DATA CLEANUP & ERROR CHECKS

Conditional Formatting Based on Another Cell (5 Formula Examples)

Excel's built-in highlight rules only look at the cell being formatted. Real work needs more: color an amount when it is above a limit typed somewhere else, turn a whole row green when the Status column says Paid, or flag an invoice only when it is both unpaid and past due. All of that takes one rule type, Use a formula to determine which cells to format, and one idea: where to put the dollar signs.

Conditional formatting based on another cell in Excel: the rule =AND($F4>0,$C4<$J$3) highlights 3 overdue invoices with an overdue balance of 11,375.00, alongside 3 over-limit and 3 paid rows. — Five formula rules on one invoice list, each driven by another cell.

What you will build

Area	Cells	What it holds
Invoice list	A3:G16	12 invoices: due date, amount, paid, balance (=Amount-Paid) and status, plus totals
Inputs	J3:J5	As-of date, approval limit and a customer search box (the "other cells")
Rule check	I7:J14	How many rows each rule should highlight, the overdue balance and a Ties check

Free download: Get the conditional formatting workbook (.xlsx). All five rules are already set up, so you can open Manage Rules and compare. Works in Excel 2010 and later, Microsoft 365 and Excel for the web.

Step 1: Know the three rules of a formula rule

Every example below follows the same three rules, and almost every broken highlight breaks one of them:

Select the whole range first (the Applies to range), starting from its top-left cell.
Write the formula for that top-left cell only. Excel adjusts it for every other cell, exactly as if you had copied a formula.
The formula must return TRUE or FALSE. TRUE means "format this cell".

The dollar signs decide what moves. $J$4 is locked completely, so every cell compares itself with the same limit. $D4 locks the column but not the row, so row 5 reads D5, row 6 reads D6, and every column in a row reads column D. That one pattern, $ before the column, no $ before the row, is how you format a cell or a whole row based on another cell. Press F4 to cycle through the four reference types.

Excel invoice list with Invoice, Customer, Due Date, Amount, Paid, Balance and Status columns, and input cells for an as-of date of Oct 12, 2026, an approval limit of 5,000.00 and a customer search of Dental. — Three input cells in J3:J5 drive all five rules.

Checkpoint: The open balance in F16 is 30,965.00 on invoices totaling 44,815.00, with 13,850.00 paid.

Step 2: Highlight a cell based on another cell's value

Flag every amount above the approval limit in J4. Select D4:D15, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and type:

=$D4>$J$4

Click Format, pick an amber fill, and OK. Change J4 and the highlights follow, which is the whole point: the limit lives in one cell instead of being typed into the rule.

Excel Edit Formatting Rule dialog with the formula =$D4>$J$4 applied to D4:D15, highlighting the three amounts above the 5,000.00 approval limit in J4. — The rule is written for D4, the first cell of D4:D15.

Checkpoint: 3 amounts are highlighted: 6,800.00, 5,600.00 and 7,350.00. INV-2110 is exactly 5,000.00 and stays plain, because > means greater than. Use >= if the limit itself should be flagged.

Step 3: Highlight the entire row based on one cell

Now color the whole row of every paid invoice. Select A4:G15 (all columns) and add:

=$G4="Paid"

Because of the $ before G, every cell in row 4, from A4 to G4, checks G4. Without it, B4 would check H4, C4 would check I4, and the highlight would land in the wrong places.

Excel conditional formatting rule =$G4="Paid" applied to A4:G15, turning the three paid invoice rows green across all columns. — $G locks every column onto Status; the row number still moves.

Checkpoint: 3 rows turn green (INV-2101, INV-2104 and INV-2106). Text comparisons are not case-sensitive, so "paid" matches too. If you prefer a tick box to a Status column, the checkbox trick that highlights rows uses the same rule with TRUE.

Step 4: Multiple conditions with AND (and OR)

An invoice is overdue only when two things are both true: there is still a balance, and the due date is before the as-of date in J3. Select A4:G15 and add:

=AND($F4>0,$C4<$J$3)

Excel conditional formatting rule =AND($F4>0,$C4<$J$3) highlighting three overdue invoice rows in red, with an overdue balance of 11,375.00. — AND needs both tests to be TRUE.

Checkpoint: 3 rows turn red: INV-2102, INV-2103 and INV-2105, with an overdue balance of 11,375.00 in J13. INV-2101 was due Sep 18 but is paid, so it stays green. A due-date-only rule would wrongly flag it.

For either-or tests use OR, for example =OR($G4="Overdue",$D4>$J$4). J3 holds a fixed date so the examples stay the same; in a live tracker type =TODAY() in J3. Dates that turn red when they are late covers date rules in more depth, and COUNTIFS for overdue items counts them.

Step 5: Highlight if a cell contains text typed in another cell

Turn J5 into a search box. Select B4:B15 and add:

=AND($J$5<>"",ISNUMBER(SEARCH($J$5,$B4)))

SEARCH returns the position of the text in J5 inside the customer name, or an error if it is not there, and ISNUMBER turns that into TRUE or FALSE. SEARCH ignores case; use FIND for a case-sensitive match. The first part, $J$5<>"", matters: an empty search text is found in every cell, so without it clearing J5 would highlight the whole column.

Excel conditional formatting rule =AND($J$5<>"",ISNUMBER(SEARCH($J$5,$B4))) highlighting the three Pine Dental customer cells when J5 contains Dental. — Type part of a name in J5 and every match lights up.

Checkpoint: "Dental" highlights 3 Pine Dental invoices. Type cafe and 2 Harbor Cafe cells light up instead.

Step 6: Due soon, rule order and the check

The fifth rule flags open invoices due in the next 7 days, from the as-of date up to and including 7 days later:

=AND($F4>0,$C4>=$J$3,$C4<=$J$3+7)

Open Home > Conditional Formatting > Manage Rules and choose This Worksheet to see all five. When two rules color the same cell, the one higher in the list wins, so the over-limit and search rules sit at the top: their amber and blue still show inside a red or yellow row. Use the arrows to reorder.

Excel Conditional Formatting Rules Manager listing five formula rules, with three invoices due in the next 7 days in light yellow and a check cell showing Ties. — Rules higher in the list win when they set the same format.

Checkpoint: 3 invoices are due soon (Oct 14, Oct 16 and Oct 19). Paid 3 + overdue 3 + due soon 3 + later 3 = 12, so J14 shows Ties: every invoice falls into exactly one status color.

Ready-to-share test: Change J3 to Oct 20, 2026. Six rows turn red, the overdue balance in J13 becomes 21,925.00, only INV-2110 (due Oct 23) is yellow, and J14 still shows Ties. Then change J4 to 4,000: five amounts are highlighted. Change both back when you are done.

Why is my conditional formatting formula not working?

Symptom	Likely cause	Fix
Only the first column of the row is colored	No $ before the column letter	Use $G4, not G4.
Every row copies row 4's result	$ before the row number	Use $G4, not $G$4.
The highlight is one row off	The formula was written for row 3 or 5, but the range starts at row 4	Always write it for the top-left cell of Applies to.
Nothing is highlighted	The formula returns a number or text, or compares text with a number	Test it in a spare cell first: it must show TRUE or FALSE. See numbers stored as text.
Rules multiply when you copy rows	Copy and paste duplicates the rules	Clean up in Manage Rules; edit the Applies to range instead.
The rule refers to another sheet and fails	Very old Excel versions do not allow it	Point the rule at a cell on the same sheet that links to the other sheet.

Formula rules are also the backbone of a readable report. A sales dashboard uses them to flag a falling month, and a heat map is the color-scale version for spotting highs and lows without a formula.

Frequently asked questions

How do I apply conditional formatting based on another cell?

Select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and write a formula for the first selected cell that refers to the other cell, such as =$D4>$J$4.

How do I highlight a row if a cell contains specific text?

Select the whole table and use =ISNUMBER(SEARCH(“text”,$B4)), or point SEARCH at a cell like $J$5 so you can change the text without editing the rule.

Can I use IF in a conditional formatting formula?

You can, but you do not need to. The rule only needs TRUE or FALSE, so =$F4>0 does the same as =IF($F4>0,TRUE,FALSE).

Can a rule use multiple conditions?

Yes. Use AND() when every condition must be true and OR() when any one is enough, as in Step 4.

Should I use TODAY() in a rule?

Yes for a live tracker, but put =TODAY() in one cell and refer to that cell. The rule stays readable and you can test it with any date.

Final checklist

The whole Applies to range was selected before the rule was created.
The formula is written for the top-left cell of that range.
$ before the column for row highlights; $ on both for fixed input cells.
The formula returns TRUE or FALSE (test it in a spare cell).
Limits, dates and search text live in input cells, not inside the rule.
Manage Rules shows the rules in the right order and no duplicates.

Practice file: Download the conditional formatting workbook. The “How It Works” sheet lists all five rules and what each one checks.

Make problems jump off the page

Browse the OneXcel Tutorial Hub for more formatting guides, explore ready-to-use Excel templates with these highlights built in, or turn your data into a dashboard with the OneXcel Dashboard Builder.