Conditional Formatting Excel Whole Row: A Practical Guide

How-to basics · Updated November 18, 2026

Yes. To format a whole row in Excel based on one cell, select the rows’ data range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that locks the test column but leaves the row relative. For records in A2:F100 with a status in column D, use =$D2="Overdue", then choose a format.

What “format the whole row” means

Conditional formatting applies a visual style when a rule is true. A rule can test one cell in a record and apply its fill, font, or border across several cells on that same row. For example, a status value in column D can determine whether cells A through F are shaded. The status cell does not have to be the cell that receives the formatting.

The key is the rule’s Applies to range. If that range is A2:F100, Excel evaluates the rule for the cells in that range and displays the formatting across those columns. A rule that applies only to D2:D100 may highlight the status cells, but it will not color the rest of each record. “Whole row” usually means the whole row of your table or report, not all 16,384 columns of the worksheet.

Using a defined data range is generally more practical than applying a rule to entire worksheet rows. It keeps the formatting focused, makes the rule easier to manage, and avoids including hundreds of unused columns. If you truly need to shade every worksheet column, you can set an appropriately broad Applies to range, but consider whether users will benefit from that much formatting.

Create a formula-based rule in Excel

In Excel for Microsoft 365 on Windows, set up a rule as follows:

  1. Select the full area to format, such as A2:F100. Start with the first data row, not the header, if you do not want the header formatted.
  2. Choose Home > Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog, choose “Use a formula to determine which cells to format.”
  4. Enter the rule formula, choose Format, select the desired formatting, and confirm.
  5. Open Home > Conditional Formatting > Manage Rules if you need to verify or edit the Applies to range.

For the status example, with records in A2:F100 and the status in D2:D100, the formula is =$D2="Overdue". The formula is written for the first row of the selected range. Excel adjusts the row reference as it evaluates subsequent rows, while the dollar sign before D keeps the test in the status column. The test is not case-sensitive for ordinary text comparisons, so values such as “overdue” also match.

On Excel for Mac, the Conditional Formatting commands are also on the Home tab, and the New Rule dialog offers formula-based rules. Dialog layout can differ slightly from Windows. Excel for the web has a more limited interface in some workbooks; if you cannot find the formula rule option or manage the rule as needed, open the workbook in the desktop app.

How the formula and cell references work

A conditional-formatting formula must return TRUE or FALSE. Excel checks it relative to the top-left cell of the Applies to range, then adjusts references as it moves through the range. That is why the row number in the usual whole-row formula should correspond to the first data row selected.

In =$D2="Overdue", $D is an absolute column reference: it always checks column D. The row reference, 2, is relative: it changes to row 3, 4, and so on for each record. This combination makes the rule test the right status cell while formatting across the row. If you instead use =$D$2="Overdue", every row checks the single cell D2, so all rows may receive the same formatting.

The range and formula need to line up. If the Applies to range begins at row 5, the rule normally needs to start by referencing row 5. If it begins at B2, the formula can still test column D, but the row reference should be 2. A mismatch often makes a rule appear one or more rows out of place.

Useful whole-row examples

Once the reference pattern is clear, you can use it for many common spreadsheet tasks. In each case, select the full row area you want formatted, not just the column used by the test.

  • Flag high-value records. With data in A2:F100 and each record’s amount in E, apply the rule to A2:F100. Use =$E2>=1000 to highlight records with an amount of at least 1,000.
  • Mark overdue unpaid items. With due dates in E2:E100 and payment status in F2:F100, apply the rule to A2:F100. Use =AND($E2<TODAY(),$F2<>"Paid",$E2<>""). This checks that the date is earlier than today, the item is not marked Paid, and the date cell is not blank.
  • Find records missing an owner. With an ID in column A and an owner in column C, apply the rule to A2:F100. Use =AND($A2<>"",$C2="") so that rows without an ID are not flagged simply because their owner cell is empty.

For the overdue example, TODAY() returns the current date, so the result can change as the calendar advances and Excel recalculates. The formula assumes the due-date cells contain real Excel dates rather than text that only looks like a date. If the workbook uses a different definition of “overdue,” such as including items due today, adjust the comparison deliberately.

Choose the right range and reference style

Before creating a rule, decide exactly which columns should receive the style and which cell or cells determine whether the rule is true. A useful way to plan is to identify the first data row, the test column, and the last column of the record. Then select that complete rectangle and write the formula using the first data row.

Use a locked column when every cell in a row should test the same field, such as status in D or due date in E. Keep the row relative so each record uses its own value. For a rule that depends on a different column as it moves across the sheet, you may need a different reference pattern; do not add dollar signs automatically without considering what should change.

Also decide whether the header belongs in the Applies to range. Most lists should start at the first data row so the header is unaffected. If a header needs its own style, format it separately or make a separate rule. Including the header in a data rule can cause confusing results because its text is evaluated as if it were another record.

Common problems and how to fix them

  • Only one column changes color. Check Home > Conditional Formatting > Manage Rules and expand the Applies to range to cover every column you want formatted.
  • The wrong row is highlighted. Make the formula’s starting row match the first row of the Applies to range. If the range starts at A2, the rule should normally refer to row 2.
  • Every row gets the same result. Look for a dollar sign before the row number. A reference such as $D$2 locks the test to one cell; use a relative row reference when each record should be checked separately.
  • The rule never triggers. Check for trailing spaces, unexpected spelling, or numbers and dates stored as text. A rule comparing a cell with “Overdue” will not match a value such as “Overdue ” with an extra space. Confirm that the rule’s formula is valid and that the condition is actually true for at least one row.
  • Another color appears instead. There may be multiple rules applying to the same cells. Review their order and formatting in Manage Rules, and check whether a higher-priority rule has Stop If True selected.

When testing, use a record whose values clearly meet the condition, then inspect the rule in Manage Rules. This is often faster than changing several formulas at once. If the data is pasted in from another system, checking the underlying cell value can reveal spaces, text-formatted dates, or other differences that are not obvious from the display.

Rule order, formatting, and maintenance

A conditional-formatting rule can set a fill, font, border, or number format. For a whole-row visual cue, a light fill is often easier to read than a strong color across many columns. Choose a format with enough contrast to keep the cell contents legible, especially if the sheet will be printed or viewed by people with different display settings.

Excel can have more than one conditional-formatting rule on a cell. In Manage Rules, you can review rules for the current selection or the worksheet, change their order, and edit their ranges. If two rules set the same formatting property, their priority can affect what appears. Stop If True can prevent lower-priority rules from being evaluated for a cell when a higher-priority rule is true; use it only when that behavior is intended.

Rules also need a sensible plan for new records. If you add rows below a formatted range, the new rows may not be included automatically in every situation. Check the Applies to range after adding data. Converting a consistently structured range into an Excel Table can make it easier to extend and manage records, but verify that the conditional-formatting rule still covers the rows you expect.

Formula rules compared with built-in rules

Not every row-highlighting task requires a custom formula. Built-in options under Home > Conditional Formatting can highlight cells that meet basic conditions, such as values greater than a threshold, duplicate values, or text that contains particular characters. Those options are quick when the cells being formatted are also the cells being tested.

A formula-based rule is more flexible when one column controls formatting across several others, when a condition combines multiple fields, or when you need to exclude blank records. For instance, a built-in “Greater Than” rule can mark large values in the amount column, but a formula rule is the direct choice when the entire record should be highlighted based on that amount.

Frequently Asked Questions

Can I highlight an entire row based on one cell in Excel?
Yes. Select the full data range to format, create a formula-based conditional-formatting rule, and lock the test column while leaving the row relative. For a status in D and data beginning on row 2, =$D2="Overdue" is a typical pattern.

Why does conditional formatting highlight the row above or below?
The row reference in the formula may not match the first row of the Applies to range. If the range begins at A2, use row 2 in the formula; if it begins at A3, use row 3. Also check whether the Applies to range itself starts on the intended row.

Can I highlight rows based on multiple conditions?
Yes. A formula can combine tests with functions such as AND or OR. For example, the overdue-and-unpaid rule above requires an earlier due date and a status other than Paid. Make sure each condition refers to the correct row and column.

Will the rule include new rows automatically?
Not necessarily. It depends on how the data is arranged and how the rule’s range is extended. After adding records, check Manage Rules and confirm that Applies to includes them. Using a Table may help with a growing list, but it is still worth verifying the rule.

Can I highlight a whole worksheet row instead of just the table columns?
Yes, by setting the Applies to range across the columns you want, but applying a rule to every worksheet column is often unnecessary. For a report with data in A:F, applying it to A:F keeps the highlight useful and avoids formatting a large empty area.

Related

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.