Conditional Formatting for an Entire Row in Excel 365

Yes. In Excel for Microsoft 365, you can conditionally format an entire row by selecting the rows to format, then creating a formula-based rule that checks a value in each row. For example, if each record’s status is in column C and the first record is on row 2, use =$C2="Overdue" to highlight rows whose status is Overdue.

What whole-row conditional formatting means

Conditional formatting changes how cells look when a specified condition is true. A whole-row rule applies that formatting across a chosen set of columns in each record, while using a value from one or more cells to decide whether the condition is met. A sales table, for instance, might shade every cell in a customer’s row when the status column says that the order is late.

The rule does not insert text, change a value, or move a record. It only changes the displayed appearance, such as the fill color, font, border, or number format. If you sort or filter the data, the underlying values remain unchanged. This distinction matters when a color is being used to communicate something important: the status should also be recorded in the data, not conveyed by color alone.

In most cases, “format the whole row” means format the full width of the table, not every column in the worksheet. If your records occupy columns A through F, you would apply the rule to A:F for the relevant data rows. Formatting all 16,384 Excel columns for every record is rarely useful and can make a workbook harder to manage.

Create a formula rule in Excel for Microsoft 365

On Excel for Microsoft 365 for Windows, use this process. The example assumes headers are in row 1, records start in row 2, and the table uses columns A through F.

Select the range to format, such as A2:F100. Start with the first data row, not the header row.
Go to Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
With statuses in C2:C100 and the first record on row 2, enter =$C2="Overdue" in the formula box.
Choose Format, select the fill, font, or other appearance you want, and confirm the dialog boxes.

Excel evaluates the rule for each cell in the selected range, adjusting the row reference as it moves down the records. When the condition is true for a row, the selected format appears across that row’s cells in the applied range. To check or change the range later, open Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the Applies to field; it should cover the full data area you intend to format.

How the formula references work

The dollar sign in =$C2="Overdue" is important. In $C2, the dollar sign fixes the column at C, while the row number remains relative. As Excel evaluates the next row, it checks C3, then C4, and so on. That lets the status in column C control formatting across columns A through F.

If you wrote =$C$2="Overdue", both the column and row would be fixed. Excel would check C2 for every row in the applied range, so one status could cause all the rows to take the same format. If you wrote =C2="Overdue" instead, both the column and row could shift as the rule is evaluated across the selected cells. The test might then inspect different columns, rather than consistently checking the status column.

The formula’s row number must match the first row in the Applies to range. If the range begins at A5, for example, a rule intended to check column C in each row should begin by referencing row 5. If the applied range starts on row 5 but the formula refers to row 2, the condition will be offset and may format the wrong records.

Useful whole-row rule examples

Choose the condition to match the way your data is organized. In each example, the formula uses a relative row number and an anchored column so that the same key column is checked throughout the selected rows.

Match a status: With status values in C2:C100 and the applied range A2:F100, use =$C2="Complete" to format completed records.
Highlight a numeric threshold: With sales totals in D2:D100 and the applied range A2:F100, use =$D2>1000 to highlight rows where the total is greater than 1,000.
Flag a date before today: With due dates in E2:E100 and the applied range A2:F100, use =AND($E2<TODAY(),$E2<>"") to highlight nonblank dates earlier than today.
Flag unpaid overdue records: With due dates in E2:E100, payment statuses in F2:F100, and the applied range A2:F100, use =AND($E2<TODAY(),$E2<>"",$F2<>"Paid"). The date must be earlier than today, the date cell must not be blank, and the payment status must not equal Paid.

In the date examples, TODAY() returns the current date and recalculates as the workbook recalculates. The formula compares the stored date with today; it does not change the date or mark a record as overdue in the underlying data. If your workbook uses different status wording, adjust the text in the rule to match the cell values exactly.

Choose the right range and row boundaries

The selection determines which cells can display the format; the formula determines which records meet the condition. Keeping these jobs separate makes the rule easier to troubleshoot. For a table spanning A:F, use an applied range such as A2:F100 if the header is in row 1. If you select only A2:A100, Excel can color only column A, even if the formula checks a value in column C.

Decide whether the rule should cover only current records or future additions too. A fixed range such as A2:F100 is straightforward, but new records below row 100 are outside it until you extend the Applies to range. You can choose a larger range to leave room for growth, or use an Excel table and check whether its conditional formatting extends as you add rows. Confirm the range in Manage Rules rather than assuming a new row inherited the rule.

For a compact, continuous data set, select its columns and rows without including unrelated notes or totals. If a totals row should not be highlighted, exclude it from the applied range. When a sheet has several separate tables, create a rule for each appropriate range or verify carefully that a multi-area selection is handled as intended.

Rule order, overlapping formats, and maintenance

A cell or row can satisfy more than one conditional formatting rule. Excel evaluates rules in the order shown in the rules manager, and rules with conflicting formats can make the result less obvious. For example, one rule might apply a red fill to overdue rows while another applies a green fill to completed rows. If a record can meet both conditions, decide which result should take priority and review the rule order.

The rules manager includes a Stop If True option for rules where stopping evaluation of lower-priority rules is appropriate. It is not necessary for every workbook, and using it without considering other rules can prevent intended formatting from appearing. When a displayed color seems wrong, inspect all rules that apply to the affected cells, not just the rule you most recently created.

Rules can also become confusing when copied or extended across a sheet. Excel may adjust references during copying, and separate rules can accumulate for overlapping ranges. Manage Rules lets you review the formula, priority, and Applies to range. Removing obsolete rules and keeping each rule’s purpose clear makes later edits safer.

Common problems and how to fix them

Only one cell changes: The Applies to range may cover one column or one cell. Expand it to the full row width you want formatted.
The wrong row is highlighted: Check that the formula’s row number matches the first row of the applied range. A mismatch can offset the test.
Every row gets the same format: Look for a fixed row reference such as $C$2. For a row-by-row test, usually lock the key column but not the row: $C2.
The rule checks the wrong column: Anchor the key column with a dollar sign, such as $C2, so the reference stays in column C across the formatted row.
A blank date looks overdue: Add a nonblank check. With dates in E2:E100 and the first data row at 2, =AND($E2<TODAY(),$E2<>"") excludes empty cells.
A text comparison does not match: Check the actual cell content for spelling and extra spaces. A dropdown choice, for example, may say Complete rather than Completed.

Also check whether the cells contain real dates or numbers rather than text that merely looks like a date or number. A comparison rule relies on the stored value Excel can evaluate. If the condition appears logically correct but never fires, inspect a few cells in the key column and verify their contents and data types.

Excel on Mac and Excel for the web

The rule concept and formula references are the same in Excel for Microsoft 365 on Mac. The Conditional Formatting controls are on the Home tab, although dialog layout and some labels can vary slightly by platform or update. Select the target range, create a formula-based rule, and check its formula and range after applying it.

Excel for the web also provides conditional formatting controls, but the available options and dialog experience can differ from the desktop application. If a workbook’s rule is difficult to create or manage in the browser, open it in the desktop app and use Home > Conditional Formatting > New Rule on Windows. The Windows path in this article is for Microsoft 365 desktop Excel; it should not be assumed to describe every web or Mac dialog exactly.

When a different approach is better

Conditional formatting is useful when the goal is to make qualifying records easier to scan while keeping the data in place. It is not a substitute for sorting, filtering, or changing a record’s status. If you want to display only overdue orders, use a filter on the status or date column; if you want records physically rearranged, sort the table. If you need to record that a task is complete, change or add a data value rather than relying on a color.

Built-in conditional formatting rules can handle common tasks such as highlighting duplicate values or values above a threshold. A formula rule is the flexible choice when a condition in one column should affect multiple columns, or when several conditions must be combined. For example, the unpaid-and-overdue formula checks both a date and a payment status. Keep the data source explicit and test the rule on a few known examples before relying on it across a large table.

Frequently Asked Questions

Can conditional formatting highlight a whole row based on one cell?
Yes. Select the full range to format, then create a formula rule that checks the key cell in each row. Anchor the key column, but normally leave the row number relative, as in =$C2="Overdue" when the first data row is 2.

Can I highlight a row based on a dropdown selection?
Yes. If a dropdown stores its selected value in column C, a formula rule can compare that cell with the relevant option. The text in the formula must match the dropdown value, and the Applies to range must include the cells across the row that should change appearance.

Does conditional formatting change or sort my data?
No. It changes the display of cells when a condition is true. It does not edit the cell values, move records, or automatically filter the list.

Why does Excel highlight the row above or below the correct one?
The formula’s row reference may not match the first row of the Applies to range. If the range begins at row 2, the formula should normally refer to row 2; if it begins at row 5, use row 5 as the starting reference.

Will new rows automatically receive the formatting?
Not always. It depends on how the range and data are set up. Check the rule’s Applies to range in Home > Conditional Formatting > Manage Rules, and extend it if new rows fall outside it. An Excel table may help keep formatting aligned as records are added, but verify the rule after expanding the table.

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.