Conditional formatting with formulas in Excel
Highlight whole rows based on a value in that row, using references that move down correctly.
Download the example workbook (.xlsx)
Illustrative data. English function names and comma argument separators are shown; Excel may use different separators or translated function names. The file was structurally inspected, but native Excel calculation and UI behavior need local verification.
Highlight scores below each row’s threshold
In the workbook, score is column C and threshold is column D. Select A2:F6, then create a conditional formatting rule using this formula:
=$C2<$D2
The dollar signs keep the test on columns C and D while row 2 changes as Excel evaluates each row. Row 2 (9 < 10) and row 5 (-1 < 0) should be highlighted. Row 3 is exactly 10 = 10 and should remain unmarked; that equality boundary prevents an accidental “less than or equal” rule.
Check the Apply to range
The top-left cell of the Apply to range must be A2 for this formula. If the range begins at A3, change the relative row in the rule to 3. Selecting only column C will highlight scores, not the whole record. The workbook has a helper output in E2:E6 reading Yes, No, No, Yes, No so you can audit the condition independently of the fill color.
For a date-specific rule with blank-date protection, use the due-date example. For duplicate highlighting, see the dedicated duplicate rule.
Compatibility and source
Compatibility: The formulas and rules use desktop Excel’s conditional formatting or Data Validation controls. Excel for web and Mac may present different menus or rule editors. Check each rule after opening the file in your edition; native dialog behavior has not been tested here.
Formula and UI behavior are described in Microsoft’s Excel guidance. Start with the broad formula and shortcut reference or browse more Excel tips.