To conditionally format an entire row in Excel, select the rows and columns you want formatted, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that tests the relevant cell in each row. For example, if a status is in column C and your data starts on row 2, use =$C2="Overdue" and set the rule’s Applies to range to the full data area.
What row conditional formatting does
Conditional formatting changes a cell’s appearance when its value meets a condition. A row-level rule applies that appearance across several cells in the same record, making it easier to spot a whole order, task, or employee entry rather than just one status cell. The formatting is dynamic: if the value changes so that the condition is no longer true, Excel removes the rule’s formatting and displays the cell’s underlying formatting again.
The rule does not move, sort, or change data, and it does not create a permanent fill color. It only controls how cells covered by the rule are displayed. That distinction is useful when you want to keep the original data intact, but it also means that copying or exporting the data may not preserve its appearance in every destination.
Excel includes ready-made conditional formatting options for values, dates, duplicates, data bars, and color scales. Those are useful for formatting individual cells. To color multiple cells in a row based on one cell’s value, a formula rule is usually the most flexible choice.
How to apply a rule to complete rows
In Excel for Microsoft 365 on Windows, first identify the full block of cells to format, including the first data row but not the header. Suppose a table occupies A1:F100, with headers in row 1 and status values in column C. To format every cell in each matching record, select A2:F100 before creating the rule.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter the condition in the formula box.
- Select Format, choose the fill, font, or border you want, and confirm the formatting dialogs.
- Check that Applies to covers the complete data block, such as =$A$2:$F$100, then select OK.
For the layout just described—headers in row 1, records in A2:F100, and status in column C—the rule formula is =$C2="Overdue". It tests the status in column C for each row and, when the status is Overdue, formats the cells across that row in the Applies to range. If C2 contains Overdue, A2:F2 receives the chosen appearance; if C3 contains Complete, row 3 does not match.
For another layout, change the column letter and starting row to fit your sheet. The formula’s row number should refer to the top row of the selected Applies to range. If your data begins on row 5, a status in column C would use row 5 in the formula and in the range, rather than row 2.
How relative and absolute references control the rule
The dollar signs in a conditional-formatting formula are important. In =$C2="Overdue", the dollar sign before C locks the test to column C, while the missing dollar sign before 2 lets the row reference change as Excel evaluates the rule down the selected range. The first row is tested against C2, the next against C3, and so on. Without the locked column, the test could shift across columns as Excel formats each cell in the row.
Use a different reference pattern when the condition depends on the cell being formatted, rather than a single status column. For example, if your Applies to range begins at A2 and you want each cell formatted when its own value is below 10, the formula is =A2<10. The column and row are both relative, so the reference adjusts as the rule is applied to other cells.
To apply a rule to rows based on one column but leave a fixed row reference, use a dollar sign before both the column and row. That is uncommon for a rule intended to evaluate each record: =$C$2="Overdue" always tests C2, so it would give every row the same result. A common misconception is that selecting a whole row automatically makes Excel look at the relevant cell in that row. The formula’s references determine what gets tested; the Applies to range determines which cells can be formatted.
Useful examples for common row rules
For each example, make sure the range begins on the same row used by the formula. The formula itself returns TRUE or FALSE; Excel uses that result to decide whether to display the chosen formatting. These examples assume a regular worksheet range with a header row, rather than a structured table reference.
- Highlight a status: With records in A2:F100 and status in C, use
=$C2="Overdue". The formula matches rows whose column C value is exactly Overdue. Text comparisons in Excel are generally not case-sensitive. - Highlight a past-due, incomplete task: With due dates in D and task status in E, use
=AND($D2<>"",$D2<TODAY(),$E2<>"Complete"). This requires a nonblank date earlier than today and a status other than Complete. A task due today is not treated as past due by this test. - Highlight repeated IDs: With IDs in A2:A100 and the full record range A2:F100, use
=COUNTIF($A$2:$A$100,$A2)>1. Each row with an ID appearing more than once is highlighted. If blank cells should not be marked as duplicates, add a nonblank test:=AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1). - Highlight an amount above a threshold: With amounts in column F and rows in A2:F100, use
=$F2>1000. This marks the entire record when its amount is greater than 1,000; replace 1000 with the threshold appropriate to the worksheet.
For the date example, TODAY() returns the current date according to Excel’s date system, so the highlighted rows can change as days pass. The blank-date check prevents an empty due-date cell from being treated as an old date. The duplicate example counts occurrences in the stated range, including the current row; a count greater than one therefore indicates that the ID is repeated within that range.
Manage rule order and the Applies to range
When several conditional-formatting rules apply to the same cells, their order can affect the result. Open Home > Conditional Formatting > Manage Rules to review rules, edit them, and inspect their Applies to ranges. In the manager, use Show formatting rules for to choose whether to review the current selection or rules for the worksheet. A rule higher in the list has higher priority when rules conflict; Stop If True can prevent lower-priority rules from being evaluated for a cell when that option is available for the rule.
Priorities matter most when rules set the same formatting property, such as two different fill colors. A row might match both “Overdue” and “High priority,” for example. Decide which condition should take precedence, or design the rules so that their formatting does not conflict. The rule manager is also the first place to check if only part of a row changes: the Applies to range may cover a single column instead of all the columns in the records.
If data grows beyond the original range, new rows may not receive the rule. You can expand Applies to in the manager. If the range is an Excel table, adding rows within the table often carries formatting and rules forward, but check the rule’s range rather than assuming it covers every future row. A table style can also apply alternating bands, but that is separate from a condition-based rule.
Common problems and how to fix them
- Only the status cell changes: Expand Applies to to include the full row block, not just the status column.
- Every row has the same appearance: Check for a fixed row reference such as $C$2. For a rule that evaluates each record, the row should usually be relative, as in $C2.
- The wrong rows are highlighted: Match the formula’s row number to the first row in Applies to. A formula written for row 2 can be offset if the range begins at row 3.
- The formula is rejected or does not match: Check the spelling and spacing of the cell value, confirm the intended column, and ensure the formula begins with an equals sign. Text in a formula must be inside straight double quotation marks.
- Blank dates are unexpectedly highlighted: Add a blank check, as in the date example, and verify that the cells contain actual Excel dates rather than text that only looks like a date.
- Old formatting seems to remain: A manually applied fill can be confusing alongside conditional formatting. Check Manage Rules, the rule’s priority, and the underlying cell formatting. Conditional formatting can override the displayed appearance without deleting the original fill.
When diagnosing a formula, test it against a particular row using the same values and references the rule should evaluate. Also check for leading or trailing spaces in imported text. A cell that looks like Overdue may contain an extra space and fail an exact text comparison.
Choose a formula rule or another approach
A formula rule is a good fit when a row’s formatting depends on another column, multiple conditions, or a condition that should move with each record. For a simpler task—such as highlighting duplicate cells in one column or values above a threshold—Excel’s built-in Conditional Formatting options may be quicker. Those options are easier to set up but may not provide the same control over formatting a complete row based on a separate cell.
For visual organization that does not depend on values, consider an Excel table style or the table’s banded-row option. For a fixed report that will not be updated, ordinary fill formatting may be enough. Conditional formatting is most useful when the data changes and the visual cue should update automatically. Avoid assigning meaning to color alone: add a clear status label, use adequate contrast, and consider readers who may not distinguish particular colors.
Excel for Mac and Excel for the web
In Excel for Mac, conditional formatting is also available from the Home tab, and formula-based rules use the same general reference logic. Dialog labels or their arrangement can differ slightly from Windows, so look for the option to create a rule based on a formula and confirm the range it applies to. On Mac, the common undo shortcut is Command+Z; on Windows, it is Ctrl+Z. Undoing a formatting change is different from changing the rule itself, so review the rule manager if the result is not as expected.
Excel for the web has conditional-formatting controls in its ribbon, but the available editing options and interface can differ from the desktop application. If you cannot find a setting or need to review complicated rule priorities, open the workbook in the desktop version of Excel and use Home > Conditional Formatting > Manage Rules. Regardless of platform, the key principles remain the same: select the cells to format, use a formula that refers to the correct row, and verify the Applies to range.
Frequently Asked Questions
Can I conditionally format an entire row based on one cell?
Yes. Select the full data range, create a formula rule that tests the chosen column, and make that column reference absolute while leaving the row relative. For a status in C and a range beginning on row 2, =$C2="Overdue" is a typical pattern.
Do I select the whole worksheet or just the data?
Usually select the data area you want formatted, excluding the header. A defined block such as A2:F100 makes the rule’s scope clear. Selecting entire columns or a whole sheet can work, but may make the rule harder to manage and can apply formatting where there is no record.
Can one rule highlight rows for more than one status?
Yes. A formula can test more than one condition, or you can create separate rules. For example, a formula using OR can match either of two status labels. If separate rules set different fills, check their order in Manage Rules so overlapping conditions behave as intended.
Why does my formula work in one row but not the others?
The row reference may be locked, the formula may start on a different row from the Applies to range, or the Applies to range may omit some records. Check those three items first, then verify that the values being tested match the formula’s condition.
Will the formatting update when I change a cell?
Yes. Excel reevaluates conditional formatting as relevant values change. Rules using TODAY() can also change as the current date changes and Excel recalculates. The rule affects appearance only; it does not alter the underlying values or move the records.
Related
- Excel Flash Fill: Automatically Fill Repeating Patterns
- Automatically Merge Duplicate Cells in Excel? What Works
- Automatically Remove Duplicates in Excel: Methods and Risks
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Not Responding? Causes and FixesIf Excel says Not Responding, give it a short time to finish before closing it; the message can…
- Excel Group Rows With Matching Values in a ColumnTo group rows that share a value in Excel, first sort the list by that column, then use…
- How to Move All Emails to the Archive Folder in OutlookTo move emails into Outlook’s Archive folder in bulk, open the folder you want to clear, select its…
- Excel Consolidate: How to Combine Data Across SheetsExcel has no worksheet function named CONSOLIDATE. To combine ranges, use Data > Data Tools > Consolidate; if…
- Excel Insert Page Breaks: Steps, Options, and FixesTo insert a page break in Excel for Microsoft 365 on Windows, select the row below or column…
- Excel Linear Equation Solver for y = mx + b: A GuideTo calculate a value from y = mx + b in Excel, put the slope m, the intercept…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.