Fix Excel Conditional Formatting That Highlights the Wrong Row

When Excel highlights the wrong row, compare the formula’s references with the range receiving the format. For an entire task row, the test can read one fixed column while its row number changes with each record. Adding dollar signs everywhere can make the colors follow a single task instead.

A Microsoft Q&A question describes the recognizable pattern: highlighting starts one cell too early and misses the final qualifying cell. The user’s class-enrollment formula referenced row 2 but applied to the whole C column. The task list below is a separate, constructed example for following the references, not that user’s workbook.

Highlight the two completed tasks

Use a normal cell range in Excel for Microsoft 365 on Windows. Put these headers and records in A1:D7. The worksheet-row column in this illustration identifies positions; do not add it as another data column.

Row	A: Task ID	B: Task	C: Due date	D: Status
2	T-201	Check quote	2026-10-26	In progress
3	T-202	Send documents	2026-10-25	Done
4	T-203	Review design	2026-10-27	In progress
5	T-204	Agree schedule	Empty	Waiting
6	T-205	Send account details	2026-10-29	Done
7	T-206	Check order	Text: 2026-10-24	In progress

Enter actual Excel dates in C2:C4 and C6. Leave C5 empty. In C7, deliberately enter '2026-10-24 with a leading apostrophe to store text. These differences will matter for the overdue rule, but the first rule only reads status.

Click A2, then Shift-click D7 so A2 remains the active starting cell. Choose Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Enter =$D2="Done", choose a green fill, and confirm. In Manage Rules, verify that Applies to is =$A$2:$D$7.

The intended result is green across A3:D3 and A6:D6. If only the status cells receive color, check whether the destination range is merely D2:D7. The formula identifies the evidence for the decision; the range identifies the cells that can receive the format. Microsoft documents formula-based formatting and rule management.

Find the one-row offset

Suppose you instead start at A1, select A1:D7, and create the rule with =$D2="Done". At A1, the reference points one row down. At A2, it consequently checks D3. At A5, it checks D6. The two Done records are on rows 3 and 6, but rows 2 and 5 receive their color.

In Manage Rules, change this rule’s Applies to range to =$A$2:$D$7. Then, with A2 selected, open Edit Rule for that rule and set the formula to =$D2="Done". Apply the changes and compare rows 3 and 6. If another worksheet’s records begin at row 5, start from A5 and use $D5 in a range beginning on that row.

You can expose the expected decisions in spare cells. Enter =$D2="Done" in F2 and fill down to F7. The expected sequence is FALSE, TRUE, FALSE, FALSE, TRUE, FALSE. If F3 and F6 are TRUE while the colors appear on rows 2 and 5, the status test is finding the intended records; its formatting placement needs correction. This helper does not inspect other formatting rules.

Keep the status column fixed

The dollar sign in $D2 fixes D while allowing the row to change. Compare the references below, all written relative to the starting cell A2. Microsoft’s reference guide explains the fixed and moving components.

Reference at A2	At B2	At A3	Effect on this list
D2	E2	D3	Moving across the row leaves the status column.
$D2	D2	D3	Each task uses its own status across the row.
D$2	E2	D2	The column moves, but the test stays on row 2.
$D$2	D2	D2	Every task depends on the first task’s status.

With =$D$2="Done", this example has no green tasks because D2 says In progress. Changing D2 to Done would make the whole destination pass, including tasks still waiting. A fully fixed reference is useful for one shared threshold, but it cannot represent the status of each separate task.

Clicking a cell while constructing the rule can insert an absolute reference. Read the resulting formula rather than assuming the selection produced the intended behavior. For a status column, remove the dollar sign before the row number; for a shared cutoff cell, retain both.

Add overdue dates without flagging blanks

Put =DATE(2026,10,27) in G1 and display it as a date. Define overdue as a numeric due date earlier than that cutoff, on a task whose status is not Done. A task due on October 27 is excluded. Starting from A2, create a red-fill rule for A2:D7:

=IFERROR(AND(ISNUMBER($C2),$C2<$G$1,$D2<>"Done"),FALSE)

AND requires all three checks to pass. The due-date and status rows move together, while every record compares against the same $G$1. T-201 is the only overdue task in the sample. T-202 is already Done; T-203 is due on the cutoff date; T-204 has no date. T-205 is Done and has a later date.

T-206 deserves a separate check. Its date looks earlier than the cutoff but was entered as text. ISNUMBER tests the stored type without converting it, so that record fails the numeric-date condition. No red fill means the rule did not classify it as overdue; it does not establish that the task is on time. Check the source and enter the intended date as a date value.

The reverse limitation also matters: the number 1 passes ISNUMBER. A numeric test cannot confirm that a value is a plausible deadline. Likewise, a different status word will not automatically count as Done. Review the actual values before treating the colors as a complete work queue.

IFERROR returns FALSE if the test produces an error. That controls this rule’s result; it does not repair an error in the source date or status. Keep those cells available for investigation instead of interpreting an uncolored row as validated.

For a daily queue, G1 can contain =TODAY(). For a dated review, keep the fixed cutoff. TODAY depends on recalculation, so a workbook using it can show different overdue tasks when calculated on a later day. The fixed October 27 cutoff makes the example’s expected results reproducible.

Resolve competing colors

Our red rule excludes Done tasks, so it does not compete with green. An older rule that simply highlights past due dates could still include T-202. In Manage Rules, show the worksheet’s rules and inspect that older condition before adding another override.

For conflicting formats, the higher rule takes precedence. Different properties can coexist: a green fill and bold text need not conflict. Stop If True prevents lower rules from being evaluated when the selected rule passes; it can therefore suppress a bold-text rule you wanted alongside the fill. See Microsoft’s rule precedence explanation.

For this task list, editing the old overdue condition to exclude Done records expresses the intended classification directly. Remove only an obsolete rule you have identified, rather than clearing every rule on the worksheet and losing unrelated highlights.

Check new rows and copied data

Add T-207 on row 8, with a due date of October 26 and status Waiting. Check both rules’ Applies to ranges. If they still end at row 7, extend them to =$A$2:$D$8. The starting row remains 2, so the formulas still begin with C2 and D2. Changing those references to row 8 would shift the checks for the existing tasks.

Copying formats from elsewhere can introduce additional conditional rules. When you only need data and want to retain this worksheet’s rules, paste values and inspect the resulting ranges. Values-only pasting also replaces copied formulas with their results, so it is unsuitable when those source formulas must remain live.

Finally, change T-201 to Done in the practice list. It should leave the red group and join the green group. T-207 should be red, while T-202 and T-205 remain green. Checking both changed and unchanged records catches a rule that accidentally makes every task follow the first status cell.