Conditional Formatting a Row Based on a Cell

In Excel for Microsoft 365, select the rows you want to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that refers to the condition cell in the first selected row. For example, to highlight A2:F100 whenever the status in column D is Complete, use =$D2="Complete"; choose a format and select OK.

How row-based conditional formatting works

Conditional formatting applies a visual style when a rule evaluates as true. A rule can test a cell in one column while applying its format across several columns, so the tested cell does not have to be the cell that changes color. This is useful for making a whole record stand out when a task is finished, an order is late, or a value crosses a threshold.

Excel evaluates the rule for each cell in the selected Applies to range, adjusting relative references as it goes. For a row-based rule, the formula is written with the row number of the range’s first row. In the example above, the dollar sign fixes the test to column D, while the row number remains relative. That tells Excel to check D2 for the first row, D3 for the next, and so on, while formatting columns A through F on the corresponding row.

The formula returns a logical result: TRUE means apply the chosen format, and FALSE means do not apply it. A formula rule does not change cell values or rearrange data; it changes only how matching cells appear. The condition can be simple, such as matching a word, or combine several checks.

Set up a rule for an entire row

Select the full range to format, including every column that should change. For example, select A2:F100 if the records occupy columns A through F and row 1 contains headings.
On the Windows ribbon, go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Type a formula that tests the relevant cell in the first row of the selected range. Do not include a leading apostrophe.
Select Format, choose the desired font, fill, border, or number format, and select OK.
Check the Applies to range in the rule manager if the formatting should cover a different area. Select OK to save the rule.

When the selection begins at row 2, the formula should normally refer to row 2. If the selection begins at row 5, use row 5 in the formula instead. The row reference must line up with the first row in Applies to; otherwise, Excel can evaluate the wrong record and the visible result may appear shifted.

In Excel for Mac, the Conditional Formatting controls are also on the Home tab, though the dialog layout can differ. In Excel for the web, the available controls and dialogs may differ from the Windows desktop application. The formula logic remains the same: match the formula’s starting row to the top row of the formatting range.

Choose the right cell reference

Reference style is the key to getting a row-wide rule right. A reference such as D2 is relative in both its column and row. If Excel adjusts it across the selected range, it can shift to E2 or another column, which is generally not what a rule based on one particular field needs. Adding a dollar sign before the column letter fixes the test column while allowing the row number to change.

With status values in D2:D100 and the intended Applies to range A2:F100, the formula =$D2="Complete" checks column D for every row. The dollar sign before D is essential; the absence of a dollar sign before 2 is also intentional. Using =$D$2="Complete" instead would make every row depend on D2, so all rows would receive the same result based on just that one cell.

If the condition is in one fixed cell outside the rows, fixing both parts may be appropriate. For example, a rule that compares every row’s value with a single target in H1 can lock H1 while keeping the row reference in the data range relative. Choose absolute and relative references based on what should change as Excel evaluates different rows, not simply as a matter of style.

Useful row-formatting examples

For a numeric threshold, suppose the value to test is in column C, and the full records are in A2:F100. Use =$C2>=1000. The row is highlighted when its C value is at least 1,000; a value of 1,250 qualifies, while 999 does not. Select the same full row range as the Applies to area if the whole record should receive the format.

For overdue dates, suppose due dates are in E2:E100, with records in A2:F100. Use =AND($E2<>"",$E2<TODAY()). The first test prevents empty cells from being treated as overdue, and the second checks whether the date is before today. A due date equal to today does not meet the less-than test. TODAY updates as the workbook recalculates, so the set of highlighted rows can change on a later day without editing the rule. The date values must be recognized by Excel as dates, not text that merely looks like a date.

For a row that needs attention when its status is either Urgent or Overdue, suppose the status is in D2:D100 and the records are A2:F100. Use =OR($D2="Urgent",$D2="Overdue"). Either matching value makes the rule TRUE. Text comparisons in ordinary Excel formulas are not case-sensitive, so urgent and Urgent are treated alike in this example.

To highlight rows where a required field in column B is empty, with data in rows 2 through 100, use =$B2="" and apply the rule to the full record range. This also matches a formula in B that returns an empty string, not just a cell cleared with Delete. If you need to distinguish a genuinely empty cell from a formula returning an empty string, a simple equality-to-empty test does not make that distinction; adjust the rule to suit the workbook’s data design.

Choose the formatting range and appearance

The formula decides whether a row meets the condition, but the Applies to range decides which cells receive the format. Selecting only D2:D100 tests and formats the status cells; selecting A2:F100 tests those statuses and formats the whole record. This separation is useful when the condition column should remain visually unchanged while other columns are highlighted.

Including the heading row by mistake can cause confusing results because the rule’s row reference is aligned to the first row of the selected range. For data with headings in row 1, start the selection and Applies to range at row 2. If you intentionally want the header formatted too, create an appropriate separate rule rather than assuming a data-row formula will line up automatically.

Use a format that communicates the condition without making the sheet hard to scan. A light fill or restrained font color often preserves readability better than several strong colors or a format that obscures existing number formats. Conditional formatting can set font, fill, border, and number formatting, but a rule should be checked against the normal appearance of the cells so that dates, percentages, and other values remain clear.

Manage overlapping rules and changing data

A worksheet can have multiple conditional-formatting rules applying to the same cells. Open Home > Conditional Formatting > Manage Rules to review the rules, their Applies to ranges, and their order. In the rule manager, the display can be scoped to the current selection or the worksheet; choose the worksheet view when you need to inspect rules beyond the currently selected cells.

If two rules both match a row, their formats may combine or conflict depending on the formatting attributes and rule settings. The order of rules and the Stop If True option can affect which rules are evaluated for the range. When results look inconsistent, review the relevant rules together rather than editing only the formula that is easiest to see.

Rules can also be affected by inserting, deleting, or moving cells and by pasting formatted content. After changing a table’s structure, inspect the Applies to range to confirm it still covers the intended records. If the worksheet contains a large number of individual, overlapping rules, consolidating equivalent rules into a single well-designed rule can make maintenance easier.

Common mistakes and how to fix them

Every row changes together: Check whether the formula locks the tested row, such as with $D$2. If each data row should be checked independently, lock only the column: $D2.
The wrong rows are highlighted: Make sure the row number in the formula matches the top row of the Applies to range. A range beginning at row 2 needs a row-2 reference.
Only the condition cell changes: Expand Applies to to include all columns in each record. The formula can keep testing one column.
The condition shifts to another column: Add a dollar sign before the test column letter, as in $D2, so the reference stays in that column.
Blank dates appear overdue: Add a blank check to the date comparison, as in the AND example above, and verify the cells contain actual Excel dates.
The text rule does not match: Check for extra spaces, inconsistent data, or a different spelling. Normal formula text comparisons ignore case, but they do not automatically remove unwanted spaces or correct misspellings.
The rule seems missing: In Manage Rules, inspect both the worksheet-wide rules and the Applies to range. A rule may exist but apply to a different range than the one being viewed.

It is usually easier to diagnose a formula rule by checking one row at a time. Compare the value in the condition cell with the rule’s exact test, then confirm that the row number and column reference point to that cell. Also check whether another rule overlays the same cells. The formula bar and Manage Rules dialog are often more informative than changing colors repeatedly.

Formula rules compared with built-in rules

Excel’s built-in options under Home > Conditional Formatting include rules for highlighting cells and other common visual patterns. Use a built-in rule when it directly expresses the goal, such as highlighting values greater than a number in the cells that contain those values. It is usually faster to configure and easier for another workbook user to recognize.

A formula rule is the better fit when the condition is in one column but formatting should extend across the row, or when the condition combines several cells. It offers more flexibility, but it also makes reference anchoring and range alignment your responsibility. If a built-in rule’s scope cannot express the desired row-wide effect, selecting the full range and writing a formula rule is a standard solution.

Frequently Asked Questions

Can I highlight a whole row based on one cell in Excel?
Yes. Select the full row range, create a formula-based conditional-formatting rule, and use a reference that fixes the condition column but allows the row to change, such as =$D2="Complete" for a range beginning on row 2.

Why does Excel highlight the row above or below the one I expect?
The formula’s row reference probably does not match the first row in the Applies to range. If the range starts at row 3, the formula should normally test row 3, not row 2.

Can a row be formatted based on a value on another worksheet?
A conditional-formatting formula generally cannot directly use a reference to another worksheet in the same way as an ordinary worksheet formula. A common approach is to bring the needed value onto the rule’s worksheet in a helper column, then base the rule on that local cell. The exact setup depends on how the source data is organized.

Will the rule include new rows I add later?
Not necessarily if the Applies to range is a fixed range such as A2:F100 and you add data below row 100. Check the rule’s range after adding records. Using an Excel table can help manage expanding data, but confirm that the rule is applied to the table’s intended columns and rows.

Does a conditional-formatting rule change the cell’s value?
No. It changes the cell’s appearance while the condition is true. It does not edit the stored value, sort the records, or replace a formula in the worksheet.

Can I use more than one condition?
Yes. A formula can combine tests with functions such as AND and OR, or you can create multiple conditional-formatting rules. Choose a combined formula when the conditions together define one clear format; use separate rules when different conditions need different appearances.

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.