Yes. In Excel for Microsoft 365 on Windows, you can use conditional formatting to highlight an entire row based on a value in one of its cells: select the rows or columns to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula such as =$C2="Overdue". The dollar sign fixes the check to column C while the row number adjusts for each row.
What row-based conditional formatting means
Conditional formatting changes a cell’s appearance when a rule is true; it does not change the cell’s contents or rearrange the data. A rule can format one cell, a group of cells, or all the cells in each data row. For row-based formatting, the formula typically checks one column and Excel applies the chosen fill, font, border, or other format across the range you selected.
For example, a task list might have task names in column A, owners in B, status in C, and due dates in D. If a task’s status is Overdue, you may want the whole row to stand out, not just the word in column C. A formula-based conditional formatting rule lets you check C for each row and apply the result across columns A through D.
Usually, it is better to select the data columns you want colored rather than every column in the worksheet. Formatting a range such as A2:D100 keeps the heading row untouched and avoids coloring far beyond the useful part of the table. You can select complete worksheet rows when that is genuinely what you want, but the formatting range should match your intended display.
Create a rule that formats complete rows
Suppose your list has headers in row 1, records in rows 2 through 100, and status values in column C. To shade each row whose status is Overdue:
- Select the cells to receive formatting, such as A2:F100. The selected range is the rule’s target; it does not have to be the same column as the value being checked.
- On the Home tab, choose Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- With status values in C2:C100 and the intended formatting range A2:F100, enter
=$C2="Overdue"in the formula box. - Choose Format, set the fill or other appearance you want, and select OK. Select OK again to create the rule.
The formula checks the status cell in column C for each row. When the rule is evaluated for row 2, it checks C2; for row 3, it checks C3, and so on. Because the rule applies to A2:F100, the resulting format appears across those six columns whenever the corresponding status matches.
If you want the format across all columns in selected rows, select the row numbers for rows 2 through 100 before creating the rule. The same formula pattern works, provided the first row in the selection is row 2. In most data lists, though, selecting just the table’s columns is easier to read and manage.
Understand the dollar signs and row references
The key to a row rule is using the right mix of relative and absolute references. In =$C2="Overdue", the dollar sign before C locks the check to column C. The row number has no dollar sign, so Excel can adjust it as it evaluates the rule down the selected range. This is why the formula can check one status column while formatting several columns in the same record.
If you wrote =C2="Overdue" for a range spanning A:F, the column reference could shift as Excel evaluates cells across the range. That might cause cells in a row to test different columns instead of all relying on status in C. If you wrote =$C$2="Overdue", the rule would keep checking only C2 for every row. That can be useful for a single fixed reference, but it is usually wrong when each record should be evaluated independently.
Check that the row number in the formula matches the first row of the Applies to range. For a range beginning at row 2, write a reference beginning with row 2. If the target range begins at row 5, base the formula’s relative row reference on row 5 instead. A mismatch is a frequent reason a rule appears to format the row above or below the intended one.
Useful examples for common row rules
The same approach works for other conditions. In each case, select the full range you want formatted and make the formula’s row reference correspond to the first row in that range.
- Flag a sales target: With sales figures in E2:E100 and rows A2:F100 as the format range, use
=$E2>=1000to highlight rows where sales are at least 1,000. - Highlight past-due dates without coloring blank dates: With due dates in D2:D100 and rows A2:F100 as the format range, use
=AND($D2<>"",$D2<TODAY()). The first test excludes blanks; the second checks whether a date is earlier than today. - Mark a specific region: With region names in B2:B100 and rows A2:F100 as the format range, use
=$B2="West"to format rows for the West region.
For the sales example, the comparison is inclusive: a value of 1,000 qualifies, while 999 does not. For the date example, a date equal to today is not earlier than today, so it is not highlighted by this condition. Excel stores dates as serial values, which lets a comparison with TODAY() work as a date comparison when the cells contain real Excel dates.
Set and check the Applies to range
Every conditional formatting rule has an Applies to range, which determines where Excel displays the format. To inspect or edit it, use Home > Conditional Formatting > Manage Rules. In the Rules Manager, select the rule and check the range shown. Depending on the current selection and the dialog settings, you may need to change Show formatting rules for to This Worksheet to see rules beyond the selected cells.
A rule can have the right formula and still appear ineffective if its Applies to range excludes the rows you expected to format. Conversely, a range that includes the header or blank rows can produce unwanted shading. After creating a rule, check both parts: the formula should evaluate the intended cell on each row, and the Applies to range should cover exactly the cells to format.
If you add records later, verify that the rule’s range includes them. An Excel table can make growing lists easier to manage, and formatting may extend as you add table rows, but inspect the rule rather than assuming its range has expanded correctly. If necessary, edit the Applies to range in Rules Manager. Use a sensible bounded range for a normal list instead of applying a rule to every worksheet cell, which can make a workbook harder to manage.
Rule order and overlapping formats
More than one conditional formatting rule can apply to the same cells. For example, one rule might shade overdue rows red while another shades high-priority rows yellow. If both conditions are true, the displayed result depends on the rules and the formats they set. In the Rules Manager, you can change rule order; where available for the rule, Stop If True can prevent lower-priority rules from being evaluated once a higher rule is true.
When a color does not appear as expected, check whether another rule applies to the same range and uses a conflicting fill or font format. Also look for duplicate rules created by copying and pasting cells. Rules Manager helps identify their ranges and order. Keep rules focused on distinct conditions where possible, and remove obsolete rules instead of layering new ones over them.
Common problems and how to fix them
- Only the condition cell changes color: The Applies to range may cover only that cell or column. Expand it to all the columns you want formatted, such as A2:F100.
- The wrong rows are highlighted: Match the formula’s relative row number to the first row of the Applies to range. Also confirm that the formula uses the intended check column.
- Every row gets the same result: A fully fixed reference such as
=$C$2always checks C2. Remove the dollar sign before the row number when each row should use its own status cell. - Some expected matches do not work: The cell might contain an extra space, a different spelling, or a value of a different type. Check the source data and compare the displayed text with the actual value.
- Blank dates look overdue: Use a condition that explicitly excludes blank cells, as in the AND example above, and check whether the cells contain genuine dates rather than text that looks like a date.
- The rule seems to stop at the last old record: Check the Applies to range in Rules Manager and extend it to include the new rows.
To diagnose a rule, temporarily apply an obvious fill color and test a small range with a few known values. This makes it easier to separate a formula problem from a format-range problem. Once the logic works, choose the final appearance and review the rule on both a matching and a nonmatching row.
Formula rules compared with built-in highlighting
Excel’s built-in conditional formatting commands are convenient for straightforward cell-level tests. For example, Highlight Cells Rules can identify cells that contain particular text, dates, or values. Those commands are quick when the cell being highlighted is also the cell being tested.
For a full-row result based on a value elsewhere in the row, a formula rule is more flexible. You can select the entire data range and make the formula test a chosen column. This avoids setting up separate rules for every column and keeps the logic in one place. The trade-off is that you must understand relative and absolute references and choose the correct Applies to range.
Conditional formatting is also different from sorting or filtering. A highlight makes qualifying records easier to spot but leaves their positions unchanged. Use a filter if you want to temporarily show only matching records, or sort if you want to reorder them. These features can be combined: a row-based highlight can make important records stand out while filtering lets you focus on a subset.
Excel for Mac and Excel for the web
In Excel for Microsoft 365 on Windows, the path for creating a rule is Home > Conditional Formatting > New Rule. Excel for Mac also has conditional formatting commands on the Home tab, though dialog layouts and some wording can differ by release. The formula approach and reference logic are the same; check the rule’s range and formula in the available rule-management dialog.
Excel for the web includes conditional formatting features, but its editing controls can differ from the desktop application. If a particular formula-based rule is difficult to create or manage in the browser, open the workbook in the desktop version of Excel and use the Windows steps above. When collaborating, remember that the format is part of the workbook rule, not a separate visual annotation that changes the underlying data.
Frequently Asked Questions
Can conditional formatting color an entire row based on one cell?
Yes. Select the row range to format, create a formula-based rule, and use a fixed column with a relative row reference. For example, a status column C can control formatting across columns A through F.
What formula highlights a row when a cell says Done?
With status values in C2:C100 and a format range of A2:F100, use =$C2="Done". Choose the desired format in the New Rule dialog. The text in the formula must match the status value in the cells.
Can I highlight the whole worksheet row instead of just my table?
Yes. Select the relevant row numbers before creating the rule, or edit the Applies to range to cover the full rows. For ordinary lists, formatting only the used columns is usually cleaner and avoids applying a rule across unnecessary cells.
Why does my rule highlight the row above or below?
The formula’s row reference may not match the first row of the Applies to range. If the range starts at row 2, make the formula evaluate row 2 first; Excel then adjusts the relative reference for subsequent rows.
Will the rule include new rows I add?
Not necessarily in every workbook setup. Check the Applies to range after adding records. If the new rows fall outside it, extend the range or use a table and confirm that the rule covers the added table rows.
Related
- Defining Print Area in Excel: Setup and Printing Tips
- DDSolver Excel Add-in: What It Does and How to Use It
- Data Validation in Excel: Lists, Rules, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Where to Insert a Table of Contents in WordIn Word, insert a table of contents (TOC) where you want it to appear—usually near the beginning of…
- Excel File Corrupted? Repair and Recover Your WorkbookIf an Excel file is corrupted, first make a copy of it, then try Excel for Microsoft 365…
- Google Sheets Date Format Not Changing? FixesIf a date format in Google Sheets is not changing, the cell may contain text rather than a…
- VALUE Excel Cell Reference: Convert Text to NumbersIf A1 contains number-like text such as 42, enter =VALUE(A1) to convert it into the numeric value 42.…
- How to Ensure Track Changes Is On in Microsoft WordTo ensure Track Changes is on in Microsoft Word, open the document, select Review > Track Changes, and…
- Named Range Sheets: How Excel Scope WorksIn Excel, a named range is a label for a cell or range, and its scope determines whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.