Conditional Formatting Based on Another Cell in Excel

I have an Excel Conditional Formatting rule that is supposed to highlight the entire row when the Status is `Overdue`.

My data is in A2:E6:

Row 2 — Acme Corp — INV-1001 — 05/08/2026 — Overdue — 1200
Row 3 — Beta Ltd — INV-1002 — 10/08/2026 — Paid — 850
Row 4 — Gamma Inc — INV-1003 — 12/08/2026 — Overdue — 2100
Row 5 — Delta Services — INV-1004 — 15/08/2026 — Open — 675
Row 6 — Northstar LLC — INV-1005 — 18/08/2026 — Overdue — 1450

The worksheet columns are:

A = Customer
B = Invoice #
C = Due Date
D = Status
E = Amount

The Conditional Formatting rule currently uses:

=$D$2="Overdue"

The rule applies to:

=$A$2:$E$6

Problem:

Excel is highlighting every row, even though only rows 2, 4, and 6 have `Overdue` in column D.

I want each row to be evaluated using its own Status cell.

Please answer in exactly these 6 sections:

1. Diagnosis — Explain why the current rule highlights every row.
2. Correct formula — Give the exact Conditional Formatting formula I should use instead.
3. Why the corrected reference works — Explain the difference between `$D$2` and `$D2` in this specific rule.
4. Expected result — List each sample row and state whether it should be highlighted after the fix.
5. How to fix the existing rule — Explain how to edit the current Conditional Formatting rule without recreating the data or adding helper columns.
6. Quick reference check — Briefly explain what `$D$2`, `$D2`, `D$2`, and `D2` would do when the rule is applied across A2:E6.

Formatting rules:
- Use exactly six numbered section headings, 1 through 6.
- Use plain text for formulas and ranges. Do not use code blocks.
- Do not use Markdown tables.
- In section 4, use one bullet point per row.
- In section 5, use bullet points only.
- Keep the answer concise and practical.
- Do not suggest helper columns.
- Do not modify the source data.

I have an Excel table of customer invoices.

The data is in A2:E7:

Row 2 — Acme Corp — INV-1001 — 05/08/2026 — Open — 1200
Row 3 — Beta Ltd — INV-1002 — 10/08/2026 — Paid — 850
Row 4 — Gamma Inc — INV-1003 — 20/08/2026 — Open — 2100
Row 5 — Delta Services — INV-1004 — 25/08/2026 — Open — 675
Row 6 — Northstar LLC — INV-1005 — 15/08/2026 — Overdue — 1450
Row 7 — Apex Partners — INV-1006 — 12/08/2026 — Open — 980

The worksheet columns are:

A = Customer
B = Invoice #
C = Due Date
D = Status
E = Amount

I want to use Excel Conditional Formatting to highlight the entire row from A:E only when BOTH of these conditions are true:

- Status in column D is exactly `Open`
- Due Date in column C is earlier than 23/08/2026

Do not treat a row with Status `Overdue` as a match, even if its Due Date is earlier than 23/08/2026.

I do not want to add helper columns or modify the source data.

Please answer in exactly these 6 sections:

1. Conditional Formatting formula — Give me the exact formula.
2. Range to select — Tell me exactly what range to select before creating the rule.
3. Why the formula works — Explain how the two conditions are combined and why the references to columns C and D should lock the columns but leave the row relative.
4. Expected result — List each sample row and state whether it should be highlighted.
5. Common mistakes — Explain briefly what could go wrong if I use OR instead of AND, use `$C$2` and `$D$2`, or compare the date to the text `"23/08/2026"` instead of using a proper Excel date value.
6. Exact Excel steps — Give the exact steps to create the rule using Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.

Formatting rules:
- Use exactly six numbered section headings, 1 through 6.
- Use plain text for formulas and ranges. Do not use code blocks.
- Do not use Markdown tables.
- In section 4, use one bullet point per row.
- In section 6, use bullet points only.
- Keep the answer concise and practical.
- Do not suggest helper columns.
- Do not modify the source data.