Excel Formula Conditional Formatting by Row

Formulas & functions · Updated November 22, 2026

To apply conditional formatting by row in Excel, use a formula that references the row’s first cell with a mixed reference (e.g., =$A2), then apply it to the entire range. For example, to highlight rows where column B is greater than 100, select A2:E10, go to Home > Conditional Formatting > New Rule > Use a formula, enter =$B2>100, set a format, and click OK.

Why Row-Based Formatting Needs a Formula

Excel’s built-in conditional formatting presets (like Highlight Cells Rules) only evaluate the selected cells individually. If you select a whole row range and use a preset, each cell is compared to its own value—so a row where column B is high but column C is low won’t be highlighted uniformly. A formula rule lets you anchor the condition to a specific column while the row number changes relative to each row. This is the core difference between cell-level and row-level formatting.

How the Mixed Reference Works

The trick is the dollar sign placement. In a formula like =$B2>100, the column B is locked (absolute) but the row number 2 is relative. When Excel applies the rule to cell C2, it shifts the row reference to C2’s row, so it becomes =$B2>100 (still B2). For cell A3, it becomes =$B3>100. This makes each row’s formatting depend on the value in column B of that same row. If you wrote =B2>100 without the dollar sign, the column would shift as you move across columns, breaking the logic.

Step-by-Step: Highlight Entire Rows Based on a Cell Value

  1. Select the range you want to format, for example A2:E10 (do not include the header row if you want to exclude it).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. In the formula box, enter a formula that references the first row of your selection. For instance, if your selection starts at row 2, use =$B2>100.
  5. Click Format, choose a fill color, then click OK twice.

For text criteria, use =$B2="Overdue" (with straight quotes). For dates, use =$B2 to highlight rows where the date in column B is before today.

Common Scenarios and Formula Examples

  • Highlight rows where a status equals “Done”: Select A2:F100, formula =$C2="Done".
  • Highlight rows where sales are in the top 10% of the column: Use =$B2>=LARGE($B$2:$B$100,ROUND(COUNT($B$2:$B$100)*0.1,0)) – but a simpler approach is to use the preset Top/Bottom Rules, which works per cell, not per row. For row-based, you'd need a helper column or a formula with RANK.
  • Highlight alternating rows (banded rows): Select A2:E100, formula =MOD(ROW(),2)=0 for even rows, or =MOD(ROW(),2)=1 for odd.
  • Highlight rows where a checkbox (linked cell) is TRUE: If column A contains TRUE/FALSE from a checkbox, use =$A2=TRUE.

Choosing the Right Range and Anchor

The range you select before creating the rule determines where the formatting applies. If you select A2:E10, the rule will only format those cells. The formula's row reference must match the first row of your selection. If your selection starts at row 2, use row 2 in the formula. If you include headers (e.g., A1:E10) and your formula references row 1, the header row will also be formatted if the condition is met. To avoid that, start the selection at row 2 or use a formula that excludes the header, like =AND(ROW()>1,$B2>100).

Common Problems and Fixes

  • Formatting applies to the wrong cells: Check the “Applies to” box in the Conditional Formatting Rules Manager. If it shows only the active cell, re-select the full range before creating the rule.
  • Formula returns an error: If you see #N/A or #VALUE, the formula may be referencing a column that doesn't exist in the range, or the data types don't match (e.g., comparing text to a number). Use =ISNUMBER($B2) to test.
  • Rule doesn't update when new rows are added: Conditional formatting does not automatically extend to new rows unless you format the entire column (e.g., A:E) or use an Excel Table (Insert > Table). With a table, the rule automatically applies to new rows.
  • Multiple rules conflict: The order of rules matters. In the Rules Manager, use the up/down arrows to set priority. Check “Stop If True” to prevent lower rules from overriding.

Comparisons: Row vs. Cell vs. Table Formatting

Cell-level formatting (presets) is simpler but only colors individual cells. Row-level formatting (formula) colors the whole row for readability, which is better for scanning lists of transactions or project statuses. Excel Tables (Insert > Table) offer built-in banded rows and automatic formula copying, but they don't allow row highlighting based on a condition without using a formula rule. Tables also change cell references to structured references (e.g., =[@Sales]), which can be used in conditional formatting formulas—for example, =[@Sales]>100 works in a table.

Frequently Asked Questions

Can I use conditional formatting to highlight a row based on a cell in another sheet? Yes, but you need to reference the other sheet with a named range, because direct cross-sheet references in conditional formatting are not allowed. Create a named range (Formulas > Name Manager) for the cell, then use that name in your formula, e.g., =$B2>Threshold.

Why does my formula work in a cell but not in conditional formatting? Conditional formatting formulas are evaluated relative to the active cell in the selected range. If you type the formula in a cell and it works, but the rule doesn't, check the active cell (the first cell in your selection) and adjust the row reference accordingly. Also, ensure you don't use absolute row references like $B$2 unless you want every row to compare to row 2.

How do I highlight an entire row if any cell in that row contains a specific word? Use the COUNTIF function with a mixed reference: =COUNTIF($A2:$E2,"urgent")>0. This checks the entire row for the word “urgent”.

Can I use a formula to highlight rows based on a dropdown selection? Yes, if the dropdown is in a cell, reference it directly. For example, if the dropdown is in column A, use =$A2="Yes". If the dropdown is on a different sheet, use a named range for that cell.

Does conditional formatting slow down my workbook? Yes, if you apply many rules to large ranges, especially with volatile functions like TODAY() or INDIRECT(). To improve performance, limit the range to the actual data area, avoid whole-column references, and use simpler formulas.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.