To highlight an entire row in Excel, select the data range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that checks the row’s key value. With headers in row 1, records in A2:F100, and each row’s status in column C, use =$C2="Overdue"; choose a fill color and make sure the rule applies to A2:F100. Excel will highlight each full row whose status is Overdue.
How the row-highlighting rule works
Conditional formatting changes the appearance of cells when a condition is true. To format a whole row based on one cell, the rule’s formula must refer to that cell while Excel evaluates the formula separately for each row. The formula above checks column C for each record, then applies the chosen appearance to all the cells in that record’s selected range.
The dollar sign before C is important: $C locks the reference to column C as the rule moves across the row. The row number, 2, is not locked, so Excel adjusts it as it evaluates the next row. For example, the rule checks C2 for the first row, C3 for the next, and C4 after that. This combination of a fixed column and a changing row is often called a mixed reference.
By contrast, $C$2 locks both the column and row, so every row would be judged by the value in C2. A reference such as C2 has neither part locked and can shift across columns as well as down rows. That is usually not what you want when the condition is always in one specific column.
Step-by-step setup in Excel for Windows
- Arrange the data in rows, ideally with one header row and one record per row. Identify the column containing the value that should control the highlighting.
- Select the cells you want Excel to format. For example, select A2:F100 to include all six columns of the records but not the header row. You can select the range by dragging or by typing it in the Name Box to the left of the formula bar.
- On the ribbon, choose Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
- Enter a formula that evaluates the first row in the selected range. If the range begins at row 2 and the status is in C, enter
=$C2="Overdue". - Select Format, choose the desired fill, font, border, or number format, and select OK. Select OK again to create the rule.
Keep the starting row in the formula aligned with the first row of the selected range. If you select A5:F50, for instance, the formula should refer to row 5, not row 2. Excel uses the top-left cell of the selection as the reference point when it adjusts the formula.
The same general conditional-formatting feature is available in Excel for Mac, though dialog details can vary by release. In Excel for the web, the command is also under Home > Conditional Formatting, but the interface and available rule options can differ from the Windows desktop app.
Useful formulas for common row rules
A formula-based rule can check text, numbers, dates, or more than one condition. In each example below, select the complete range to be formatted first, and make the formula refer to the first data row. The formula itself decides whether the row qualifies; the selected range decides which cells receive the format.
- Highlight a status: With records in A2:F100 and status values in C2:C100, use
=$C2="Complete". Rows with Complete in column C receive the chosen formatting. Text in a direct comparison is not case-sensitive in ordinary Excel comparisons. - Highlight a number above a threshold: With sales amounts in D2:D100 and the whole record in A2:F100, use
=$D2>1000. A row is highlighted when its amount in column D exceeds 1,000; an amount exactly equal to 1,000 does not meet the greater-than test. - Highlight overdue items that are not paid: With due dates in D2:D100 and payment status in E2:E100, use
=AND($D2<TODAY(),$E2<>"Paid"). The row is formatted when the due date is before today and the status is not Paid. TODAY updates as the workbook recalculates, so the result can change from day to day. - Highlight repeated IDs: With IDs in A2:A100 and records across A:F, use
=COUNTIF($A$2:$A$100,$A2)>1. Every row whose ID occurs more than once in the listed range is highlighted. This marks all instances of a repeated ID, not just the later occurrence.
For the date example, blank dates may need special treatment depending on the worksheet, because a blank cell can be interpreted as a date-like zero in comparisons. If blank due dates should never trigger formatting, add a check that the date cell is not blank. With due dates in D and payment status in E, use =AND($D2<>"",$D2<TODAY(),$E2<>"Paid"). This requires a nonblank date as well as the other two conditions.
Selecting the right range and reference
The formula and the Applies to range perform different jobs. The formula answers, “Does this row meet the condition?” The Applies to range answers, “Which cells should receive the format?” If the formula checks C but Applies to contains only C2:C100, only the status cells will change appearance. To highlight the full record, Applies to must cover all its columns, such as A2:F100.
Exclude the header row unless you intentionally want it evaluated as data. Including it can produce an unexpected highlight because the formula will test the header cell as though it were a record. Likewise, avoid including entire columns without a reason: applying a rule to a very large range can make a workbook harder to manage and may affect performance, particularly when many complex rules are involved.
For a range that starts in row 2, use row 2 in the formula, even if the condition column is elsewhere. To check column G while formatting A:F, for example, keep the column fixed as $G and use the first data row in the reference. The condition cell does not have to be inside the formatted range.
Common problems and how to fix them
- Only one cell is highlighted: Check the rule’s Applies to range. It should include all the columns in the rows you want formatted, not just the condition column.
- Every row gets the same result: Check whether the row number was accidentally locked, as in
$C$2. For a rule that should test each row, the row number normally remains relative:$C2. - The wrong column is checked: A missing dollar sign can cause the column reference to move as Excel evaluates the other cells in a row. Lock the condition column with a dollar sign, such as
$C2. - The first row seems offset: The formula’s row number should match the first row in Applies to. If the range begins at A3, a rule referring to row 2 is misaligned.
- Text that looks identical does not match: The cell may contain extra spaces, a different spelling, or a different underlying value. Check the source data and whether the condition should use an exact text match or a broader test.
- A rule appears not to work: Open Home > Conditional Formatting > Manage Rules and inspect the rule’s formula and Applies to range. In the dialog, set Show formatting rules for to the relevant worksheet if needed.
Rules can also conflict. If two rules apply to the same cells and set the same formatting property, the rule order and the Stop If True setting can affect which result is visible. In Manage Rules, review the order and the settings rather than assuming the most recently created rule always wins. Use distinct visual properties or simplify overlapping conditions when possible.
Editing rules and keeping them useful
To change a rule, select a cell in the affected range and go to Home > Conditional Formatting > Manage Rules. Choose the rule, then edit its formula, formatting, or Applies to range. This is also the place to remove obsolete rules and find multiple rules that may be formatting the same cells.
Use a format that makes the condition easy to notice without making the sheet difficult to read. A light fill often preserves readable text better than a strong fill; a border or font change may be enough when the sheet already uses color to communicate other information. Conditional formatting is a visual cue, not a replacement for clear column labels or accurate underlying data.
If you sort or filter the list, the rule remains tied to cell references and its Applies to range. Excel displays the formatting with the rows that meet the condition in their current positions. If you add records beyond the range, inspect whether the new rows are included; a rule limited to A2:F100 will not necessarily cover a new row below 100. Extend Applies to when needed.
Using row highlighting with Excel tables
Excel tables are useful for lists that grow over time because they provide structured columns, filter buttons, and consistent table formatting. You can create a table by selecting the data and choosing Insert > Table, or by using Ctrl+T on Windows. Conditional formatting can still be used to highlight complete table rows based on a column value.
When setting up a formula-based rule for a table, select the relevant table data cells and confirm the Applies to range after the rule is created. Table expansion and formatting behavior can depend on how the rule was initially applied, so do not assume a rule will cover every future row without checking. A quick test is to add a sample row and see whether the rule applies, then adjust the range if necessary.
Tables also help reduce range-selection errors when data is added, but the conditional-formatting formula still needs the correct relative row reference for its first row. If the table is sorted, the formula’s condition should continue to be evaluated for the row at each position, provided its references and range are set correctly.
Conditional formatting versus manual highlighting
Manual fill color is appropriate when you want to mark a specific row once, regardless of its contents. Conditional formatting is better when the visual cue should follow a rule—for example, when a status changes to Overdue or a value crosses a threshold. With a rule, changing the source value can automatically add or remove the highlight.
Conditional formatting does not change the cell values, sort the list, or prevent someone from editing a record. It only controls appearance while its condition is true. If a highlight communicates an important business status, keep the status in a real data column as well; color alone can be missed, printed differently, or be difficult for some readers to distinguish.
Frequently Asked Questions
Can Excel highlight a whole row based on one cell?
Yes. Select the row range to format, create a formula-based conditional-formatting rule, and use a mixed reference such as =$C2="Overdue" when column C determines the result. Include every desired column in the rule’s Applies to range.
Why does the formula use a dollar sign before the column?
The dollar sign fixes the condition to one column as Excel evaluates cells across the selected row. Leaving the row number unlocked lets the rule check the corresponding condition cell in each successive row.
Can I highlight a row when a cell contains part of a word?
Yes. Use a text-search condition rather than an exact comparison. With the text to inspect in C2:C100 and records in A2:F100, a rule such as =ISNUMBER(SEARCH("late",$C2)) highlights rows where column C contains late, regardless of capitalization. SEARCH returns a number when it finds the text and an error when it does not; ISNUMBER turns that into a true-or-false result.
Can I use more than one condition?
Yes. The AND function requires all included tests to be true, while OR requires at least one. For example, an overdue-and-unpaid rule can combine the date and status checks in one formula. Make sure each condition refers to the intended columns and uses the correct first data row.
Will the highlight update when I change a value?
Normally, yes. Excel recalculates conditional-formatting rules when relevant cells change. Rules that use volatile functions such as TODAY can also change as the workbook recalculates, which is useful for date-based statuses but means the displayed result is not a permanent mark.
Related
- Building a Heat Map in Excel: Steps, Scales, and Tips
- Binary Excel Solver: How to Build and Solve 0-or-1 Models
- Excel Power Query and Business Intelligence Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Slicer: Connect One Slicer to Multiple PivotTablesYes. In Excel for Microsoft 365 on Windows, one slicer can filter multiple PivotTables when they share a…
- Excel Time Clock: Track Shifts, Breaks, and OvertimeAn Excel time clock is a workbook for recording employees’ clock-in and clock-out times, calculating shift hours, and…
- Microsoft Excel: How to Change the Print AreaTo change a print area in Microsoft Excel for Microsoft 365 on Windows, select the cells you want,…
- Excel Get Data From a Semantic Model: Connect and AnalyzeYes. In Excel for Microsoft 365 on Windows, connect to a Power BI semantic model through Data >…
- Google Sheets Dropdown Multiple Selection ExplainedYes. Google Sheets supports multiple selections in a dropdown chip in the same cell: select the dropdown, open…
- Add an Auto Reply to a Shared Mailbox in Outlook 365To add an automatic reply to a shared mailbox, open that mailbox in Outlook on the web and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.