Conditional Formatting Rows in Excel: How to Highlight

To conditionally format entire rows in Excel, select the rows or range you want formatted, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that tests a column in each row. For example, if status is in column C and your data starts on row 2, use =$C2="Overdue", choose a format, and make sure the rule applies across all the columns in each row.

What conditional formatting rows means

Conditional formatting changes a cell’s appearance when a specified condition is true. A rule can format just the cell containing the value, or it can format a wider range—including every cell in a row—based on a value in one column. The latter is useful when you want to scan a table and immediately see which records need attention.

For instance, a task list might have task names in column A, owners in column B, status in column C, and due dates in column D. A rule based on column C can color columns A through D on every row where the status is Overdue. The status remains the trigger; the formatting is applied to the whole row.

There is no separate “format entire row” switch in the formula-rule dialog. The effect comes from two choices working together: the range you tell the rule to format, and a formula that evaluates the right condition for each row in that range.

Set up a rule for an entire row

In Excel for Microsoft 365 on Windows, first select the cells to which the color should apply. If the table runs from columns A to F and rows 2 to 100, select A2:F100. Then:

Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter the formula that identifies rows to highlight.
Select Format, choose options such as a fill color or font style, and confirm with OK.
Check the Applies to range in the rule manager if the formatting does not cover the intended columns or rows.

With status values in C2:C100 and the selected range A2:F100, enter =$C2="Overdue". This tests the status in column C for each row and formats A:F on rows whose status is exactly Overdue. The dollar sign before C keeps the test in column C as Excel evaluates the rule across the row. The row number is not locked, so Excel tests C3 for the next row, C4 for the one after that, and so on.

On Excel for Mac, the same kind of formula rule is available from Home > Conditional Formatting > New Rule; the dialog layout can differ from Windows. Excel for the web also offers conditional formatting, though some rule-management options and dialog details may vary. The key is the same in all cases: select the full target range and use a formula tied to its first row.

Understand the dollar signs and starting row

Formula references determine how Excel adjusts a test as it applies formatting to cells in the selected range. In =$C2="Overdue", $C fixes the column, while 2 is a relative row reference. That combination is important: every cell in a given row looks at the same status column, but each successive row checks its own status.

The row number in the formula should match the first row of the Applies to range. If the selected range starts at row 2, refer to row 2. If the range starts at row 5, change the formula to =$C5="Overdue". A mismatch can make the highlight appear one or more rows above or below the record that meets the condition.

Locking both the column and row, as in =$C$2="Overdue", makes every cell in the range check only C2. That is rarely right for a row-by-row rule. Leaving the column unlocked can also cause problems: as Excel evaluates cells across A:F, a reference such as C2 may shift to D2 or E2. Use a fixed column and a changing row when the condition comes from one field in each record.

Useful row-highlighting formula examples

Formula rules can test text, numbers, dates, or combinations of conditions. In every example, adjust the column letters and starting row to fit your sheet, and apply the rule to the full set of cells you want colored.

Match a status: With statuses in C2:C100 and the target range A2:F100, use =$C2="Complete". Rows marked Complete receive the chosen format.
Flag a high priority: With priority labels in D2:D50 and the target range A2:F50, use =$D2="High". Only rows labeled High are highlighted.
Highlight past-due dates: With due dates in D2:D100 and the target range A2:F100, use =AND($D2<>"",$D2<TODAY()). This formats rows with a nonblank date earlier than today. TODAY updates as the workbook recalculates.
Match either of two labels: With labels in C2:C100 and the target range A2:F100, use =OR($C2="High",$C2="Urgent"). A row is formatted if its label is either High or Urgent.
Highlight a whole row when a number exceeds a limit: With amounts in E2:E100 and the target range A2:F100, use =$E2>1000. This marks rows with an amount greater than 1,000; a value of exactly 1,000 does not meet the condition.

Text comparisons such as these generally ignore capitalization, so a cell containing overdue can match a test for Overdue. Extra spaces, however, can prevent an exact match. If the source values are inconsistent, clean them or choose a condition that accounts for the data you actually have.

Choose the right range and format

“Entire row” usually means the full width of the table, not every column in the worksheet. Applying a rule to A2:F100 keeps the highlight within a six-column data table. Applying it to A2:XFD100 would cover every worksheet column, which can create an unwieldy rule and make the sheet harder to work with.

Decide whether the header belongs in the range. If row 1 contains column names and records start in row 2, exclude row 1 from both the Applies to range and the formula’s starting row. For a growing list, you can expand the Applies to range when new records are added. If the data is an Excel table, table formatting and rule behavior can be easier to maintain as rows are appended, but check the rule manager after changing the table or its layout.

Use a format that improves reading rather than obscuring the data. A light fill, bold text, or a colored font can be easier to scan than a saturated background. Keep enough contrast for the text to remain legible, especially if the workbook may be printed or viewed by people with different display settings.

Manage rules and conflicting formats

Open Home > Conditional Formatting > Manage Rules to inspect existing rules, edit formulas, change the Applies to range, or remove rules. In the manager, choose the relevant worksheet if the rule is not visible in the current selection. This is also a useful place to diagnose a row that appears formatted unexpectedly: more than one rule may be affecting the same cells.

When rules overlap, Excel evaluates them according to their order in the rules manager. A rule with Stop If True can prevent lower-priority rules from being applied to a cell when its condition is met. This matters when, for example, an urgent row should remain red even though another rule would otherwise color completed rows green. Review both the order and the formats before assuming a formula is wrong.

Conditional formatting is a visual layer, not a change to the cell value. A colored row does not automatically sort to the top, change a status, or travel with the data as a permanent label. You can sort or filter using the underlying values, but the rule itself only controls how matching cells look.

Common problems and how to fix them

The wrong row is colored: Check that the formula’s row number matches the first row of the Applies to range. A rule applied to A2:F100 should normally begin by testing row 2.
Only one cell changes color: The Applies to range may cover only the trigger column. Edit it to cover all columns you want formatted, such as A2:F100.
Every row gets the same result: Check for an accidentally absolute row reference such as $C$2. For a row-by-row test, use $C2.
Formatting shifts across columns: Lock the condition column with a dollar sign, as in $C2, so cells throughout a row all check column C.
Some expected matches are missed: Confirm the cell contains the expected value, without leading or trailing spaces, and check whether the rule tests a different column or range than intended.
A blank row is highlighted by a date rule: Add a nonblank test, as in the AND example above, so empty date cells are excluded.

To inspect a rule, select a cell that should be affected and open Home > Conditional Formatting > Manage Rules. Compare the formula, Applies to range, and rule order with the layout of the data. If several old rules have accumulated, removing obsolete ones can make the workbook easier to troubleshoot.

Formula rules compared with built-in rules

Built-in options under Home > Conditional Formatting, such as Highlight Cells Rules and Data Bars, are convenient when you want to format the value cells themselves. For example, a highlight rule can mark numbers above a threshold in one column without requiring a formula. To color the entire record based on that value, a formula rule is usually more direct because it lets the condition inspect one column while the Applies to range covers several.

There is also a difference between conditional formatting and manually filling rows. A manual fill stays until someone changes it; it does not automatically respond when a status or date changes. Conditional formatting recalculates its appearance when relevant values change, which is helpful for live trackers but means the visible color depends on the rule remaining intact.

Frequently Asked Questions

Can Excel highlight an entire row based on one cell?
Yes. Select the full row range you want formatted, create a formula-based conditional formatting rule, and use a relative row reference with the trigger column locked. For example, a rule checking =$C2="Overdue" can color A:F when the status is in column C.

Why does my conditional formatting formula use a dollar sign?
The dollar sign fixes a column or row reference as Excel applies a rule across a range. In $C2, the column stays C while the row changes. That lets every cell in a record test the same field on its own row.

Can I highlight rows based on a date that has passed?
Yes. Apply a formula rule to the full data range and test the date column against TODAY. Include a check that the date cell is not blank if empty cells should remain unformatted. The result updates as the workbook recalculates.

Can one rule highlight rows for multiple statuses?
Yes. Use OR in the formula when any of several labels should trigger the same format, or create separate rules if each label needs a different color. Keep the column fixed and use the correct first row for the selected range.

Does conditional formatting change the cell value?
No. It changes the cell’s appearance when a rule is true; it does not replace the text, date, or number, or automatically alter the row’s order. The underlying value remains available for formulas, sorting, and filtering.

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.