Conditional Formatting: Highlight a Row Based on a Cell

To highlight an entire row in Excel when a particular cell meets a condition, select the rows you want to format and create a conditional-formatting rule using a formula such as =$C2="Yes". If your selected range begins on row 2 and column C contains the test value, the dollar sign locks the column while the row number adjusts for each row.

What the rule does

Conditional formatting changes a cell’s appearance when its value meets a rule; it does not change the underlying value. To highlight a whole row, the rule must be applied to the whole row range, even though its formula checks only one cell in each row. For example, you might shade every column from A through F whenever the corresponding value in column C is Yes.

The key is matching the formula’s reference to the top row of the selected range. If the range begins at row 2, use row 2 in the formula. Excel evaluates the formula separately for each row in the range, shifting the row reference as it goes. Thus, a rule based on column C checks C2 for the first row, C3 for the next, and so on.

This approach works for text labels, numbers, dates, checkboxes that return TRUE or FALSE, and many other conditions. It is useful for flagging overdue work, identifying a particular status, drawing attention to low stock, or distinguishing records that need review.

Create a row-highlighting rule in Excel

In Excel for Microsoft 365 on Windows, set up the rule as follows. The example assumes a table-like range in A2:F100 and a status in column C.

Select the cells to shade: A2:F100. Do not select only the status cells if you want the entire row across all six columns to change appearance.
Choose Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
Enter =$C2="Yes" in the formula box. The formula should refer to the first row of the selected range.
Choose Format, select a fill color or another format, and confirm the dialog.
Select OK again to create the rule. Rows whose column C value is Yes should now receive the chosen formatting.

To confirm the rule’s scope, choose Home > Conditional Formatting > Manage Rules. Check the Applies to field. It should cover the intended row range, such as =$A$2:$F$100. If it covers only column C, the rule can evaluate correctly but will shade only that column.

Why the dollar signs matter

In =$C2="Yes", the dollar sign before C fixes the test to column C. The row number 2 has no dollar sign, so it can change as Excel evaluates the rule down the selected range. This mixed reference is what lets each row use its own status cell while applying formatting across several columns.

If you wrote =C2="Yes" instead, both the column and row could shift as the rule is applied across the range. A cell farther to the right might end up checking a different column, producing inconsistent results. If you wrote =$C$2="Yes", both the column and row would be fixed, so every row would be judged by the single value in C2. That is rarely the goal when each record should be evaluated independently.

For a selected range beginning on row 5, use row 5 in the formula, for example =$C5="Yes". If the range begins at A1, use =$C1="Yes". The formula’s row number is not necessarily the worksheet’s header row; it should correspond to the first row of cells included in the rule.

Adapt the formula to different conditions

Keep the same reference pattern and change the test to fit the data. With the formatting range still starting on row 2, these examples check column D or C as indicated:

Highlight rows where the number in column D is below 10: =$D2<10.
Highlight rows where column D is blank: =$D2="".
Highlight rows where column C contains the exact text Closed: =$C2="Closed".
Highlight rows where a checkbox-linked cell in column E is TRUE: =$E2=TRUE.
Highlight rows where column D contains a date earlier than today: =$D2<TODAY().

For a text search that finds a word anywhere in the cell, use =ISNUMBER(SEARCH("late",$C2)). SEARCH is not case-sensitive, so it matches Late, LATE, or a longer entry such as Late delivery. If the search should distinguish uppercase and lowercase, Excel’s FIND function is case-sensitive; a corresponding rule is =ISNUMBER(FIND("Late",$C2)). Both approaches treat a missing match as a condition that is not met.

To combine conditions, use AND when every test must be true, or OR when any test can be true. For example, to highlight a row when column C says Open and column D is past due, use =AND($C2="Open",$D2<TODAY()). To highlight it when either column C says Urgent or column D is below 10, use =OR($C2="Urgent",$D2<10). Make sure the referenced columns and first-row number match your own layout.

Choose the right range and layout

The formula decides whether a row qualifies; the Applies to range decides which cells can be formatted. Suppose the records occupy A2:F100 and the trigger is in C. Selecting A2:F100 shades the full record. Selecting A2:C100 shades only the first three columns. Selecting C2:C100 shades only the trigger column. These are separate choices, so check both the formula and the range when a rule does not look right.

For a list that grows over time, an Excel Table can make the setup easier to maintain. Select the table’s data cells when creating the rule, rather than an arbitrary block that ends at the current last row. Table formatting and conditional-formatting behavior can vary according to how the rule was created, so verify the Applies to range when adding rows. If the rule does not extend as expected, expand its range in Manage Rules or recreate it with the full intended range selected.

Exclude header cells unless you intentionally want them formatted. If your table has a header in row 1 and data starts in row 2, apply the rule from row 2 and use row 2 in the formula. Applying the rule to the header while using a data-row reference can make the header’s result confusing and can also offset the checks for the records below.

Common problems and how to fix them

Only the trigger cell changes color: Expand the Applies to range to include all columns that should be shaded.
The wrong row is highlighted: Make the formula’s row number match the first row in Applies to. A range beginning at row 3 needs a row-3 reference.
Every row reacts to one cell: Check for an accidental absolute row reference such as =$C$2="Yes". Use =$C2="Yes" when each row should check its own C cell.
The rule seems to ignore a matching word: Check for extra spaces or different text, and confirm the formula refers to the correct column. An exact text comparison will not match a longer phrase containing the word; use SEARCH if a partial match is intended.
Dates behave unexpectedly: Excel dates need to be stored as date values for comparisons such as earlier than today to work reliably. Text that merely looks like a date may not compare as expected.
A different rule overrides the color: Open Manage Rules and inspect other rules covering the same cells. Rule order and the option to stop processing additional rules can affect which formatting is visible.

For text comparisons such as Yes, Excel’s ordinary equality test is generally not case-sensitive. For example, Yes and YES are treated as equal in a standard comparison. Also distinguish a truly empty cell from a formula that returns an empty string: both may look blank, but they are not identical in every test. If a rule depends on blankness, try it on representative rows and make sure formulas or spaces are not producing unexpected values.

Excel for Mac and Excel for the web

The formula logic is the same in Excel for Microsoft 365 on Mac. Select the target range and use the conditional-formatting controls on the Home tab to create a formula-based rule; the dialog wording or layout can differ slightly from Windows. The mixed reference still needs to lock the test column and leave the row relative, as in =$C2="Yes".

Excel for the web also supports conditional formatting, though some dialog options and rule-management controls may not match the Windows desktop version exactly. If you cannot find the formula-based rule option or need to inspect a complex rule, open the workbook in the desktop app. Avoid changing the formula’s reference pattern just because the interface differs.

Conditional formatting versus other methods

Conditional formatting is a good choice when you want rows to respond automatically as values change while leaving the worksheet data intact. It can be removed or edited later, and it keeps the highlight tied to the condition rather than requiring users to remember to recolor records manually.

Manual fill color is simpler for a one-time visual annotation, but it does not automatically update if a status changes. Sorting or filtering can help you focus on matching records, but these actions do not permanently highlight the entire row. A helper column that returns a label such as Review can make a condition visible and filterable, while conditional formatting can then use that label as its trigger. These options can be combined: filter the table for qualifying records and use a rule to make them stand out.

Conditional formatting is visual only. It does not move records, prevent edits, or guarantee that a user notices a condition. If a condition must trigger a workflow, validation, or an alert outside the workbook, a fill color alone is not a substitute for that process.

Frequently Asked Questions

Can I highlight an entire row based on a cell in another column?
Yes. Apply the rule to the full row range and make the formula reference the trigger column with an absolute column and relative row, such as =$C2="Yes".

Why does my formula use a dollar sign before the column?
It keeps the check in the same column while the rule moves across the selected row range. Leave the row number relative so each row is checked against its own cell.

Can I highlight a row when a cell contains part of a word?
Yes. Use =ISNUMBER(SEARCH("late",$C2)) to find late anywhere in the column C cell. SEARCH is case-insensitive; FIND is the alternative when capitalization should matter.

Can one rule check more than one condition?
Yes. Use AND when all conditions must be met, or OR when at least one condition is enough. For example, =AND($C2="Open",$D2<TODAY()) checks both the status and due date.

Will new rows inherit the formatting?
They may if the rule’s Applies to range expands with the table or the range was set to include the new rows. Check Manage Rules after adding records; if necessary, extend the range so the rule covers them.

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.