Conditional Formatting Excel: Color an Entire Row

Yes. To color an entire row in Excel based on one cell, select the rows you want formatted, create a formula-based conditional formatting rule, and use a reference that locks the test column but lets the row number change. For data in A2:F100 where each status is in column C, select A2:F100 and use =$C2="Overdue"; then choose a fill color.

How Excel colors a whole row

Conditional formatting changes how cells look when a specified condition is true. A rule normally evaluates cells in the range it applies to, but a formula rule can make the decision depend on another cell in the same row. Applying the rule to a multi-column range makes every cell in a matching row receive the chosen format.

For example, suppose a task list has task names in column A, owners in B, status in C, due dates in D, and notes in E and F. If the rule applies to A2:F100 and checks column C, Excel evaluates the condition separately for each row. When C2 matches the condition, cells A2:F2 are formatted; when C3 matches it, A3:F3 are formatted. The rule does not need a separate formula for every row.

The key is to distinguish the range being colored from the cell being checked. The range might cover six columns, while the formula refers to only one column. This is useful for review queues, inventory lists, attendance sheets, project trackers, and any table where a single value should make the whole record stand out.

Set up a formula-based rule in Excel for Windows

Select the full data area you want colored, such as A2:F100. Include all columns that should change color, but do not include the header if it should remain unformatted.
Choose Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
Enter the formula for the condition. For a status column C and a first data row of 2, enter =$C2="Overdue".
Choose Format, select a fill on the Fill tab, and confirm the formatting choices.
Confirm the rule. Check that it applies to the whole selected range, such as A2:F100, rather than only to column C.

Use the row number of the first selected data row in the formula. If your selection starts at row 5, the corresponding status formula should refer to row 5, not row 2. Excel adjusts the relative row reference as it evaluates the rule farther down the selected range.

Why the dollar sign matters

In =$C2="Overdue", the dollar sign before C locks the column, while the absence of a dollar sign before 2 leaves the row number free to change. That combination tells Excel to check column C for each row. If the rule is applied to A2:F100, the first evaluation checks C2, the next checks C3, and so on.

If you wrote =C2="Overdue" without locking the column, Excel could shift the reference across columns as the rule is evaluated in different cells. That may cause cells in the same row to check different fields. If you wrote =$C$2="Overdue", the column and row would both be fixed, so every row would check only C2. That can make the entire selected range turn on or off together instead of responding to each row separately.

Think of the conditional formatting range as the area receiving the paint and the formula reference as the instruction that decides which rows get painted. A relative row and fixed test column are the usual arrangement for row-based formatting.

Useful row-coloring formulas

After confirming the range and first row, adapt the condition to the kind of data you have. The examples below assume the data begins on row 2; change both the applied range and row reference to fit your sheet.

Highlight a particular status. With statuses in C2:C100 and the formatting range A2:F100, use =$C2="Complete". Rows whose status is Complete receive the selected format.
Highlight a numeric threshold. With scores in D2:D100 and rows in A2:F100, use =$D2>=90. This highlights rows with a score of 90 or higher.
Highlight overdue open tasks. With status in C2:C100, due dates in D2:D100, and the rule applied to A2:F100, use =AND($C2="Open",$D2<TODAY()). The row is highlighted only when the status is Open and the due date is earlier than today.
Highlight rows when a field is not blank. With a review date or other required entry in D2:D100 and the range A2:F100, use =$D2<>"". This is often useful for showing which records have a value entered in a key column.

In the overdue example, TODAY returns the current date, so the condition changes as the date changes. A task due today is not earlier than today and will not meet that formula. If it should count as due today, use a less-than-or-equal comparison instead: with the same status and date layout, the formula is =AND($C2="Open",$D2<=TODAY()). Blank due-date cells can also need separate handling, because Excel treats blank cells in comparisons in ways that may not match your intended meaning. Test the rule on a few representative rows.

Choose the right range and rule order

The Applies to range determines which cells can receive formatting. To color complete records, include every relevant column in that range. If it covers only C2:C100, the status cells can change color but the rest of each row cannot. If it includes headers, the formula may evaluate them too, so it is usually cleaner to begin at the first data row.

When a worksheet has multiple conditional formatting rules, their interaction can affect the visible result. For example, one rule might color overdue rows red and another might color completed rows green. A row that meets both conditions may show formatting according to the rules and their priority, especially when the rules set the same formatting property. Review or change rule order under Home > Conditional Formatting > Manage Rules. The dialog lets you inspect the rules and their Applies to ranges; the Stop If True option is available for controlling whether lower-priority rules are evaluated.

It is often simplest to make conditions mutually exclusive, such as separate rules for Open and Complete statuses. If two rules intentionally overlap, decide which color should take priority and confirm the order in the Rules Manager. Avoid creating duplicate rules over overlapping ranges, which makes later edits harder to understand.

Common problems and how to fix them

Only one cell changes color. Check the Applies to range. It should cover the whole row area, not just the cell used in the formula.
The wrong rows are highlighted. Make sure the formula uses the first row of the applied range. If the range starts at row 2, a formula referring to row 1 is offset.
Every row gets the same color. Check that the formula does not lock the row by using a reference such as $C$2. For row-by-row checking, lock the column but leave the row relative, as in $C2.
Rows appear shifted by one. The first formula reference and the first row of Applies to may not match. For A2:F100, use a formula that checks row 2; for A3:F100, use one that checks row 3.
A status that looks right does not match. The cell may contain extra spaces, a different spelling, or a different value type. Compare the actual cell contents, not just what appears at a glance. A formula condition with an exact text match requires the text to match.
Dates behave unexpectedly. Excel stores real dates as date values, but imported dates may be text. Check whether the values behave as dates in ordinary sorting or calculations, and make sure blanks should not satisfy the comparison you chose.

To inspect a rule, select a cell in the affected area and choose Home > Conditional Formatting > Manage Rules. Use the list for the current selection or the worksheet, as appropriate, to see the formula and range. Editing the existing rule is generally better than adding another copy when the original range or formula is simply incorrect.

Tables, copying data, and keeping rules manageable

Excel tables can make a data list easier to sort, filter, and expand. You can apply conditional formatting to the table’s data rows, but check the Applies to range after adding rows or changing the table. A rule based on a specific range may not always cover cells outside that range, so verify that newly added records receive the intended formatting.

When you copy and paste cells, conditional formatting can be copied or combined with existing rules, depending on the paste operation. Repeated pasting may create extra rules or overlapping ranges. If formatting starts behaving inconsistently, open Manage Rules and look for duplicate entries or ranges that have grown unexpectedly. For a workbook shared with others, keep the rule formula and applied range simple enough that another person can understand what triggers the color.

Conditional formatting changes appearance; it does not change the stored status, date, or number. A red fill is not a substitute for a value such as Overdue, and formulas that read the underlying data do not usually treat the fill color as the condition. Keep the meaningful information in the cells and use color as a visual aid.

Excel for Mac and Excel for the web

In Excel for Microsoft 365 on Windows, the New Rule dialog includes the formula-based option described above. Excel for Mac also has conditional formatting and formula-based rules, though the dialog layout and wording can differ slightly. The logic of the formula is the same: select the intended range and use a relative row reference with the test column fixed.

Excel for the web has conditional formatting controls, but available options and rule-management details can differ from the Windows desktop app. If you cannot create or precisely edit the formula rule you need in the browser, open the workbook in the desktop version of Excel. When using a workbook across platforms, check the result after editing, particularly if the rule has multiple conditions or overlapping ranges.

Frequently Asked Questions

Can Excel color a whole row based on one cell?

Yes. Apply a conditional formatting formula rule to the full row range and make the formula test the relevant cell in each row. For example, a range A2:F100 can use =$C2="Overdue" to color rows according to the value in column C.

Do I select the whole row or only the cell with the condition?

Select the full range of cells you want colored, such as A2:F100. The formula can refer to just one column, but the Applies to range must include the other cells that should receive the fill.

Why does the formula use $C2 instead of $C$2?

The dollar sign fixes the column so Excel always checks column C. Leaving the row number unlocked lets the rule check C2, C3, C4, and later rows as it evaluates the range. Locking both parts would make every row check only C2.

Can I highlight a row when a date is overdue?

Yes. With status in column C, due date in column D, and data beginning on row 2, a rule applied to A2:F100 can use =AND($C2="Open",$D2<TODAY()). It highlights open tasks with a due date before today; use a less-than-or-equal comparison if today should count as overdue.

Can I use different colors for different statuses?

Yes. Create a separate formula-based rule for each status and choose a fill for each one. Apply each rule to the same row range, make the conditions distinct where possible, and review the rules in Manage Rules if their formatting could overlap.

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.