Conditional Formatting to Highlight an Entire Row in Excel

Yes. In Excel for Microsoft 365, select the data range, create a formula-based conditional-formatting rule, and use a formula that tests a cell in each row while locking its column—for example, with records in A2:F100 and status in column C, use =$C2="Overdue". Apply the rule to A2:F100 to highlight every cell across each matching row.

How row highlighting works

Conditional formatting changes how cells look when a rule evaluates as true. To color a whole row based on one value, the rule checks a cell in that row, while the rule’s “Applies to” range covers all the cells you want formatted. Excel evaluates the formula for each cell in that range, adjusting relative references as it moves.

In the example, the dollar sign before C fixes the test to column C. The row number 2 has no dollar sign, so it changes as Excel evaluates the next row: the rule checks C2 for the first record, C3 for the second, and so on. If the condition is true, the selected format appears across the corresponding cells in the apply range.

This distinction matters: a rule that applies only to C2:C100 can color the status cells, but not the rest of each row. To highlight the full record, the apply range must cover all its columns, such as A2:F100. You can still base the decision on just one column.

Create a rule in Excel for Microsoft 365

On Windows, use this process to make a row highlight when its status says Overdue:

Identify the table’s first data row and the columns to format. In this example, records occupy A2:F100 and status is in column C.
Select A2:F100. Start at the first data row rather than the header if you do not want the heading formatted.
Choose Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
With A2:F100 as the apply range and status values in C2:C100, enter =$C2="Overdue".
Choose Format, select a fill color or another visual style, and confirm the dialogs.

Excel’s text comparison in this kind of formula is generally not case-sensitive, so “Overdue” and “OVERDUE” match. The value still needs to match the text you are checking; an extra space or a different label such as “Past due” will not satisfy the exact comparison. If the rule does not appear to work, check the underlying cell contents as well as the formula.

Lock the right references

The dollar signs determine how a reference moves across the apply range. For a rule applied to several columns, the usual pattern is to lock the column being tested and leave the row relative. In =$C2="Overdue", $C stays fixed as formatting is evaluated across A:F, while row 2 can become row 3, row 4, and so forth.

Locking both parts, as in =$C$2="Overdue", makes every row depend on C2. If C2 says Overdue, the rule could format every row in the apply range; if not, it could format none. Leaving the column unlocked, as in =C2="Overdue", can make the tested column shift as the rule is evaluated across the row. That is usually wrong when every cell in a row should be judged by column C.

The formula’s row number should correspond to the first row of the apply range. For example, if formatting begins at A5, the formula should refer to row 5, not row 2. This alignment is one of the most common sources of a rule that seems offset by several rows.

Useful row-highlighting examples

Different conditions can drive the same approach. The formula checks the relevant row, and the apply range determines how much of the row receives the format.

Highlight incomplete records: with records in A2:F100 and a completion status in C, apply to A2:F100. Use =$C2="Incomplete".
Flag overdue dates without treating blanks as dates: with due dates in C2:C100 and records in A2:F100, use =AND($C2<>"",$C2<TODAY()). The blank check prevents empty due-date cells from being highlighted as past dates. TODAY updates when Excel recalculates the workbook.
Highlight a row when its amount reaches a threshold: with amounts in B2:B100, records in A2:F100, and the threshold in H1, use =$B2>=$H$1. The absolute reference keeps the threshold fixed as the rule evaluates other rows and columns.
Shade alternating data rows: with the apply range A2:F100, use =MOD(ROW(),2)=0. This shades even-numbered worksheet rows. Because the data starts on row 2, the first data row is shaded; change the comparison to 1 if you want the opposite pattern.

For a text condition that can appear anywhere in a row, with text in A2:F100 and the same apply range, use =COUNTIF($A2:$F2,"*urgent*")>0. The asterisks are wildcards that match text before or after “urgent.” This example checks all six cells in each row; if you only want to check one column, use that column instead.

Choose the apply range carefully

“Highlight the row” does not have to mean every column on the worksheet. Usually, it is clearer to format just the table, such as A2:F100, rather than entire worksheet rows. Formatting only the relevant data avoids coloring unused columns and keeps the visual signal focused. Include a helper or notes column only if readers should see it highlighted too.

If the data has a header in row 1, start the range at row 2 and make the formula refer to row 2. If the header itself should be part of the format, adjust both the apply range and the formula’s reference so that the first row of the range is evaluated correctly. A rule for a range beginning at A1 that tests records beginning in row 2 will otherwise be misaligned.

After setting up the rule, open Home > Conditional Formatting > Manage Rules to review the range and formula. In the rules window, check the Applies to field. If the data grows, verify that new rows are included; a rule restricted to A2:F100 will not necessarily cover records entered below row 100. You can edit the range there rather than recreating the rule.

Common problems and fixes

Only one cell changes color: the apply range may cover only the test column. Expand it across the row’s data columns.
The row above or below is highlighted: align the formula’s row number with the first row in the apply range.
Every row has the same result: check that the tested row number is not locked with a dollar sign. A reference such as $C$2 always tests the same cell.
The condition is true but the color is not visible: inspect the chosen format, overlapping conditional-formatting rules, and rule order in Manage Rules. Another rule may apply a competing format. The rule manager lets you change priority and, where applicable, use “Stop If True.”
Some matching text is missed: check spelling, spaces, and whether the cells contain the expected values. An exact text comparison will not match a different phrase. For a date rule, check that the entries are real Excel dates rather than text that merely looks like a date.

Conditional formatting does not replace the cell’s contents; it changes the display while the condition is met. If you edit a status from Overdue to Complete, for example, the row’s conditional formatting updates as Excel recalculates. A manually applied fill is different: it remains until someone changes it, so it can become misleading when the data changes.

Formula rules compared with other options

Excel’s built-in Highlight Cells Rules are convenient when you want to format the cells containing values above a number, matching text, or dates within a period. They are often the quickest choice for highlighting one column. A formula-based rule is more flexible when the condition is in one column but the desired format spans other columns, or when the condition combines more than one test.

Manual fill color is suitable for a one-off annotation, but it does not track changes to the data. Table styles can provide consistent banded rows, which are useful for readability but do not indicate that a particular record meets a condition. Conditional formatting is the better fit when the row’s appearance should follow a changing value or date.

These approaches can coexist, but keep the meaning of each visual treatment clear. For example, alternating banded rows can make a large list easier to scan, while a distinct conditional-formatting color flags overdue records. If colors compete or make text hard to read, simplify the styles and check the rule priorities.

Windows, Mac, and Excel for the web

The steps above are for Excel for Microsoft 365 on Windows. Excel for Microsoft 365 for Mac also supports formula-based conditional formatting. The command is available from Home > Conditional Formatting > New Rule; the dialog’s layout can differ from Windows, but choose the formula-based rule, enter the formula relative to the first row of the range, and set the format.

Excel for the web also offers conditional formatting, though the available controls and dialog layout can differ from the desktop app. If you cannot find a particular rule option in the web interface, open the workbook in the desktop version to create or manage the formula rule. In any version, confirm that the apply range covers all the columns you want highlighted.

Frequently Asked Questions

Can conditional formatting highlight an entire row based on one cell?
Yes. Apply the rule to the whole data range and write a formula that tests the relevant column with a fixed column reference and a relative row reference. For example, with data in A2:F100 and status in C, use =$C2="Overdue".

How do I highlight a row when a cell is not blank?
With records in A2:F100 and the cell to check in C2:C100, apply the rule to A2:F100 and use =$C2<>"". This formats rows where column C contains a value. If cells may contain formulas that return an empty string, test the result in your workbook to ensure it matches your intent.

Can I highlight a row based on a date that has passed?
Yes. With due dates in C2:C100 and records in A2:F100, use =AND($C2<>"",$C2<TODAY()). It checks that the date is not blank and is earlier than today. TODAY reflects the current date when Excel recalculates.

Why does the formula need a dollar sign before the column?
The fixed column makes every cell across a formatted row check the same field, such as column C. Leaving the row number relative lets Excel check the corresponding record as it moves down the apply range.

Can conditional formatting highlight the row I click?
Not reliably by itself. Conditional formatting is designed to respond to cell values and formulas, not simply to the active selection. A selection-driven highlight generally requires a different approach, such as VBA, and macros should be used cautiously because their actions cannot be undone with Ctrl+Z.

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.