Excel Conditional Formatting Per Row: How To

How-to basics · Updated November 22, 2026

The simplest way to apply conditional formatting per row in Excel is to use a formula rule with a mixed reference: lock the column letter (e.g., $A1) but leave the row number relative, then apply the rule to the entire range. This lets each row evaluate its own cells independently, so you can highlight an entire row based on a value in that row’s column.

Why Per-Row Formatting Differs from Standard Rules

Most conditional formatting presets, like Highlight Cells Rules or Top/Bottom Rules, compare each cell to a fixed threshold. For example, selecting B2:B10 and choosing “Greater Than 100” highlights only the cells in that column that exceed 100. But when you want to highlight a whole row because, say, the status in column C says “Overdue,” you need a formula that looks at the row’s own data and then applies the format to every cell in that row. The key is understanding relative and absolute references inside the conditional formatting formula—Excel evaluates the formula for the top-left cell of the selected range, then shifts it across and down, exactly like copying a formula.

How the Formula Works: Mixed References Explained

Assume your data is in A2:D20, with a status in column C. To highlight any row where C says “Overdue,” select A2:D20, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this formula:

=$C2="Overdue"

Here, the $C locks the column to C, but the row number 2 is relative. Excel applies the rule to the active cell (A2) first, checks $C2, then moves to B2 (still checks $C2), then C2, and so on. When it moves to row 3, the formula becomes $C3, so each row is evaluated against its own status. If you had written $C$2, every row would check the same cell, C2, which is rarely what you want.

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

  1. Select the data range, e.g., A2:D20. Make sure the active cell is A2 (click it after selecting).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. In the formula box, type =$C2="Overdue" (adjust column and value as needed).
  5. Click Format, choose a fill color, and click OK twice.
  6. To add more rules, repeat with different formulas, e.g., =$C2="Pending".

On a Mac, the steps are identical—the ribbon path is the same. In Excel for the web, conditional formatting formulas work, but you cannot create new rules with formulas in the free version; you need a Microsoft 365 subscription for that.

Common Scenarios and Formula Examples

  • Highlight rows where a number exceeds a threshold: Select A2:D20, use =$B2>100 to highlight rows where column B is greater than 100.
  • Highlight rows where two conditions are true: Use =AND($A2="Yes",$B2<50) to highlight only rows that meet both conditions.
  • Highlight rows with a date older than 30 days: Use =$D2 (make sure column D contains real dates).
  • Highlight every other row (banding): Use =MOD(ROW(),2)=0 to format even rows, or =MOD(ROW(),2)=1 for odd rows.
  • Highlight rows where a cell contains specific text: Use =ISNUMBER(SEARCH("Urgent",$E2)) to highlight rows where column E contains "Urgent" anywhere.

For text matching, remember that = is case-insensitive and requires an exact match. Use SEARCH or FIND for partial matches—FIND is case-sensitive, SEARCH is not.

Choosing the Right Reference: Absolute, Relative, or Mixed

Getting the dollar signs right is the most common mistake. Here's a quick guide:

  • Relative (no $): C2—both column and row shift as the rule moves. Use this when you want each cell to compare to a different cell in the same relative position, but that's rare in row-based formatting.
  • Absolute (both $): $C$2—always refers to the exact cell C2. Use this for a fixed threshold value stored in a cell, like =$B2>$E$1 where E1 holds the limit.
  • Mixed (column locked, row relative): $C2—column stays C, row changes. This is the standard for row-based formatting because you want to check the same column for each row.
  • Mixed (row locked, column relative): C$2—row stays 2, column changes. Use this for column-based formatting (e.g., highlight a whole column based on a value in row 1).

When applying the rule to a range, always think about the top-left cell. The formula you type is for that cell, and Excel adjusts the relative parts as it fills the range.

Common Problems and How to Fix Them

  • Rule highlights the wrong rows: Usually a missing or extra dollar sign. Double-check that the column is locked but the row is relative.
  • Rule applies to only one cell: You may have selected only one cell before creating the rule, or the "Applies to" range is wrong. Edit the rule and set the range correctly.
  • Text match fails because of extra spaces: Use =TRIM($C2)="Overdue" to ignore leading/trailing spaces.
  • Numbers stored as text: If a cell looks like a number but the comparison fails, convert text to numbers using the VALUE function or the Text to Columns wizard.
  • Multiple rules conflict: Excel applies rules in order, and the first true rule wins unless "Stop If True" is checked. Use the Rules Manager (Home > Conditional Formatting > Manage Rules) to reorder or set stop flags.
  • Rule disappears when you sort or filter: Conditional formatting rules are attached to the range, not the data. If you sort, the formatting moves with the rows, which is usually fine. If you insert rows, the rule may not extend automatically—check the "Applies to" range after inserting.

Comparison: Formula Rules vs. Built-in Presets

Built-in presets (like Color Scales, Data Bars, or Icon Sets) work on a range of cells and compare values within that range, not across rows. For example, a Color Scale on A2:A20 shades each cell based on its value relative to the whole column—it cannot highlight an entire row. Formula rules are the only way to format a row based on a condition in another cell of that row. They are also more flexible: you can combine conditions with AND/OR, use wildcards, or reference other sheets. The downside is that formulas are harder to write and debug, and they recalculate every time the sheet changes, which can slow down very large workbooks.

Performance and Maintenance Tips

  • Apply rules to the smallest range needed; avoid whole-column references like A:A unless necessary, as they force Excel to check every cell.
  • Use Stop If True when you have mutually exclusive rules to avoid unnecessary recalculations.
  • If you have many rows, consider using a table (Insert > Table) because conditional formatting rules automatically expand to new rows in a table.
  • To delete a rule, select the range, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
  • To copy a rule to another range, use Format Painter (Home > Format Painter) after selecting a cell with the rule, or copy and paste special as formats.

Frequently Asked Questions

Why does my formula highlight the entire row even when the condition is false? That usually means the formula has an absolute row reference, like $C$2, so every row checks the same cell. Change it to $C2 (no dollar before the row number) and re-apply.

Can I use a formula that references another sheet? Yes, you can reference another sheet in a conditional formatting formula, but the sheet name must be in single quotes if it contains spaces, e.g., ='Data Sheet'!$A1>100. The formula will work as long as the other sheet is in the same workbook.

How do I highlight a row if any cell in that row contains a certain word? Use =COUNTIF($A2:$D2,"*urgent*")>0. The asterisks are wildcards that match any characters before and after "urgent". This works for text, but not for numbers.

Does conditional formatting work with merged cells? It can, but it's tricky. Merged cells only have one value in the top-left cell, and the formula evaluates based on that top-left cell. If you merge cells across a row, the rule will still work, but it may not behave as expected if the merged area spans multiple columns.

Can I use a formula to highlight a row based on a dropdown list value? Yes, if the dropdown is a data validation list, the cell contains the selected text. Use a normal formula like =$C2="High" and it will work. The formula doesn't care how the value got there.

Why is my rule not applying to new rows I add? If you're not using a table, the rule's "Applies to" range is fixed. After adding rows, edit the rule and expand the range, or convert the data to a table (Insert > Table) so the rule extends automatically.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.