Conditional Formatting: Apply Rules to Entire Rows

Yes. In Microsoft Excel for Microsoft 365 on Windows, conditional formatting can highlight an entire row when a cell in that row meets a condition. Select the cells you want formatted, then create a rule that tests a column with a formula such as =$C2="Overdue"; the dollar sign keeps the test in column C while Excel adjusts the row number.

What row-based conditional formatting means

Conditional formatting changes the appearance of cells when a rule evaluates as true. A rule can apply to one cell, a column, a rectangular block, or a range spanning several columns and rows. To make a row stand out, set the rule’s “Applies to” range to cover the row’s relevant cells, then make the condition refer to the value that controls the highlight.

For example, a task list might have task names in column A, owners in B, status in C, due dates in D, and notes in E. If you want every cell from A through E to turn red when the status is “Overdue,” the formatting range should include A:E for the task rows, while the formula checks column C. The condition and the formatting scope are separate parts of the rule.

This does not necessarily format every column in the worksheet. “Entire row” usually means the row portion of your table, such as A2:E100, rather than all 16,384 worksheet columns. Limiting the range to the table keeps the visual effect focused and avoids unnecessary formatting across a large sheet.

How Excel evaluates a rule across rows

Excel evaluates a conditional-formatting formula for each cell in the rule’s “Applies to” range. When the selected range contains multiple rows, relative references shift as Excel evaluates each row. If the first row of the range is row 2, a formula that refers to C2 is interpreted against row 2 for that first row, then against C3 for the next, C4 after that, and so on.

That behavior lets one formula control many rows. The test can look at a status, date, number, or other value in one column, while the rule applies a fill, font, border, or other available format to the whole table row. You do not need to create a separate rule for every row.

The formula should be written with the first data row of the “Applies to” range in mind. If the range begins at row 2, use row 2 in the formula. If it begins at row 5, use row 5. A mismatch can make the formatting appear one row too high or low, a common source of confusion when setting up a rule.

Apply a conditional-formatting rule to rows

For a reliable setup in Excel for Microsoft 365 on Windows, use a formula-based rule:

Select the data cells to format, including all columns that should receive the highlight. For a table from columns A through E with data in rows 2–100, select A2:E100.
Choose Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
Enter a formula that tests the relevant cell in the first selected row. For a status in column C, the example below tests whether row 2 is marked Overdue.
Select Format, choose the fill, font, border, or other format you want, and confirm the formatting choices.
Select OK to create the rule. Review its range if needed through Home > Conditional Formatting > Manage Rules.

Assuming the status is in C2:C100 and the formatting range is A2:E100, the rule formula is =$C2="Overdue". The dollar sign before C fixes the test column; the row number is left relative so Excel checks each row’s own status.

On Excel for Mac, the Conditional Formatting command is also on the Home tab, though dialog layouts and some controls can differ from Windows. In Excel for the web, conditional-formatting options may be more limited than in the desktop app; use the desktop version if you need controls that are not available in the browser.

Formula examples for common row conditions

A formula can check many kinds of conditions, not just a text status. Use the appropriate formula pattern for the data and make sure the columns and first row match your sheet.

Highlight a row for a particular status. With status values in C2:C100 and the target format range A2:E100, use =$C2="Complete". The row is formatted when its column C entry is exactly Complete.
Flag a past-due item. With due dates in D2:D100 and the format range A2:E100, use =AND($D2<>"",$D2<TODAY()). The blank check prevents empty due-date cells from being treated as overdue. TODAY updates with the current date when Excel recalculates.
Highlight rows above a threshold. With numeric scores in B2:B50 and the format range A2:D50, use =$B2>=90. Each row is highlighted when its score is 90 or higher.
Match a value in another cell. With status values in C2:C100, the format range A2:E100, and the desired status stored in G1, use =$C2=$G$1. Both dollar signs around G1 keep the comparison target fixed for every row.

For text comparisons, spelling and extra spaces matter. If a cell contains “Overdue ” with a trailing space, it may not match the intended status. Using a validated list for statuses can reduce inconsistent entries. Likewise, a date that looks like a date but is stored as text may not behave as expected in a date comparison.

Why dollar signs and the first row matter

Excel’s dollar signs control which parts of a cell reference can change when the formula is applied across a range. In =$C2="Overdue", $C locks the column, while 2 remains adjustable. If the range covers A2:E100, all cells in row 2 check C2; all cells in row 3 check C3. This is the usual pattern for highlighting rows based on a fixed test column.

If you wrote =C2="Overdue" without locking the column, Excel could shift the test reference as it evaluates different columns within the applied range. For example, a cell in column D could end up testing column E instead of C. Conversely, =$C$2="Overdue" locks both the column and row, so every row checks only C2. That usually is not what you want when each row has its own status.

Keep the top row of the formula aligned with the first row in “Applies to.” If the range is A5:E50, the matching status formula would begin with row 5, not row 2. If you later change the range’s starting row, revisit the formula reference as well.

Built-in rules or a formula rule?

Built-in conditional-formatting options are convenient for simple tasks. For example, Home > Conditional Formatting > Highlight Cells Rules includes options for values such as greater than, equal to, or text that contains. These are useful when you want to format the cells being compared, such as highlighting only the status cell.

For a full-row highlight based on one column, a formula rule is usually more direct because it separates the condition from the range that receives formatting. You can also use formulas for combined tests, such as a status plus a date condition, when a single built-in rule does not express the logic you need. Built-in rules may still work for some layouts, but check the resulting “Applies to” range to verify that the format reaches the intended cells.

Choose a restrained format that makes the row easier to scan without obscuring its contents. A light fill or a bold font is often more readable than a strong fill combined with several other effects. The right choice depends on the number of rules and the importance of the condition.

Check the range, priority, and rule behavior

If an existing rule does not cover the right cells, select Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the “Applies to” field and expand the list selector if necessary to see the relevant worksheet rules. You can edit the formula, range, format, and—in cases where rules overlap—their order.

When more than one rule applies to the same cell, the rules can interact. Excel evaluates rules in priority order, and the Stop If True setting can prevent lower-priority rules from being evaluated for a cell when a higher rule is true. A fill from one rule may also make another rule’s fill appear absent. If the result looks wrong, temporarily review the rules and their formats individually instead of assuming the formula itself is the only problem.

Adding rows can also affect coverage. If you insert data within an existing formatted range, Excel often adjusts the range, but do not assume it always matches your intended table boundaries. Check “Applies to” after structural changes, particularly when new data is pasted below the current range. If your data is formatted as an Excel table, adding rows to the table can help keep related data together, but still verify the conditional-formatting rule when the result matters.

Common problems and practical fixes

Only one cell changes. The “Applies to” range may include only the test cell. Edit it to cover all columns that should be highlighted, such as A2:E100.
The highlight is shifted by one row. Match the formula’s row reference to the first row of the applied range, and check that the selected range does not include a header row unintentionally.
The wrong columns control the result. Lock the test column with a dollar sign, as in =$C2="Overdue", while leaving the row relative.
Nothing matches even though the text looks right. Check for extra spaces, different wording, or values stored in an unexpected format. A visible date may be text rather than a numeric Excel date.
Another color appears instead. Look for overlapping rules, their priority, and whether Stop If True is enabled. Also confirm that the format you selected is visible against the cell’s existing font or fill.

Filtering and sorting do not change the basic logic of a rule: it remains tied to worksheet cells and its applied range, not simply to the rows currently visible on screen. After sorting a table, the condition should still follow the values in the cells because the formula is evaluated against the row’s current contents. If the result no longer makes sense, inspect the underlying values and references.

Frequently Asked Questions

Can conditional formatting highlight an entire row in Excel?
Yes. Set “Applies to” to all cells in the row area you want formatted, then create a formula rule that tests the relevant column for each row.

Do I need a separate rule for every row?
No. One formula-based rule can cover many rows. Use a relative row reference so Excel evaluates the condition separately for each row in the range.

Can I highlight a row based on a different column?
Yes. Lock the column that contains the condition, but leave the row number relative. For example, if status is in C and the range starts on row 2, =$C2="Complete" can format the other cells in each matching row.

Can I use more than one condition?
Yes. A formula can combine tests, or you can add multiple conditional-formatting rules. Check their priority and formats if the rules apply to the same cells.

Why does my rule highlight the row above or below?
The formula’s starting row likely does not match the first row of the “Applies to” range. Align those row numbers, then check whether the range accidentally includes a heading row.

Does the rule apply to the whole worksheet row?
Only if its “Applies to” range covers the whole worksheet row. In most tables, it is clearer and more efficient to format only the table’s columns, such as A2:E100.

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.