Excel
All Top 50 Excel & Business Modeling Questions#30
Highlighting Full Rows Dynamically with Formula-Driven Conditional Formatting
MediumGoldman SachsInterview Question #30
Asked at Goldman SachsIn a transaction ledger `A2:E100`, you want to highlight the entire row in soft red whenever Column D (Status) equals 'Overdue'. Write the formula rule to apply in Conditional Formatting and explain the exact cell locking required.
Input Table: Ledger (A1:E4)
3 rows preview| Invoice (Col A) | Client (Col B) | Amount (Col C) | Status (Col D) | Due Date (Col E) |
|---|---|---|---|---|
| INV-101 | Acme | 4500 | Paid | 2026-01-10 |
| INV-102 | Globex | 8200 | Overdue | 2026-01-15 |
| INV-103 | Initech | 1200 | Pending | 2026-02-01 |
Expected Output Structure3 rows
| Row Highlight Effect |
|---|
| Row 2 (INV-101) - Normal background |
| Row 3 (INV-102) - Entire row A3:E3 highlighted in Red |
| Row 4 (INV-103) - Normal background |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice (Col A) | Client (Col B) | Amount (Col C) | Status (Col D) | Due Date (Col E) | |
| 2 | INV-101 | Acme | 4500 | Paid | 2026-01-10 | |
| 3 | INV-102 | Globex | 8200 | Overdue | 2026-01-15 | |
| 4 | INV-103 | Initech | 1200 | Pending | 2026-02-01 | |
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect