Yes. In Excel for Microsoft 365, select the cells in each record that should change color, then create a formula-based conditional formatting rule. With headers in row 1, records in A2:F100, and a status in column C, use =$C2="Overdue" to format each selected row when its status is Overdue.
What “format an entire row” means
Conditional formatting changes a cell’s appearance when a rule is true. To make a whole record stand out, the rule is applied to all the cells across that record, while its condition checks one or more key cells. For example, a status value in column C can determine whether the name, date, amount, and notes in columns A:F all receive a fill color.
In most spreadsheets, “entire row” should mean the full width of the data you want to emphasize, not every column on the worksheet. If your records occupy A:F, applying the rule to A:F gives the useful visual effect without coloring unrelated columns far to the right. You can apply it to worksheet rows if you really need that, but a bounded range is easier to manage and keeps the rule focused on the data.
The rule changes formatting, not the underlying cell values. A status remains text, a date remains a date, and a number remains a number. The formatting is recalculated when relevant values change, so a row can lose its highlight after its status is updated.
How the formula works
Excel evaluates a formula-based rule for every cell in the selected range. The formula must be written with references that behave correctly as Excel evaluates different rows. In the example =$C2="Overdue", the dollar sign before C locks the column reference, while the row number has no dollar sign and can adjust. That means Excel checks column C on the same row as the cell being formatted.
The formula’s starting row must match the first row of the range covered by the rule. If the selected range begins at A2, write the condition using row 2. As Excel moves down the range, it checks C3, C4, and so on. If the rule applies to A3:F100 but the formula starts with row 2, the condition is offset and can highlight the wrong records.
Dollar signs are not decoration: they control how references move. In =$C2="Overdue", $C stays in column C, but the row changes. By contrast, =C2="Overdue" allows both the column and row to shift. Because the rule is applied across multiple columns, the latter can end up checking D2, E2, or another column as Excel evaluates cells farther across the range.
Set up a whole-row rule in Excel
For Excel for Microsoft 365 on Windows, use a formula rule to define which records should be formatted:
- Identify the first data row and the columns that make up each record. For example, use A2:F100 if row 1 contains headers.
- Select the full data range to format, such as A2:F100. Do not include the header row unless you want the rule to affect it too.
- Choose Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog, choose “Use a formula to determine which cells to format.”
- Enter the condition. With statuses in C2:C100 and the selected range starting on row 2, enter
=$C2="Overdue". - Select Format, choose the desired options on the Fill, Font, or Border tabs, and confirm the dialog.
- Check that the rule applies to the intended range. Home > Conditional Formatting > Manage Rules lets you review the rule and its Applies to range.
On Excel for Mac, the equivalent route starts at Home > Conditional Formatting > New Rule; the labels and arrangement in the rule dialog can vary by release. Choose the formula-based rule, enter the same relative-reference formula, and set the format. The formula logic is the same on Windows and Mac.
Choose the range and references carefully
The correct range depends on what readers should perceive as one record. If your worksheet has fields in A:F, apply the rule to A2:F100. If a record also has a comment in G, include that column only if the comment should share the highlight. Selecting just C2:C100 would highlight the status cells alone, even if the condition refers to a status there.
It helps to distinguish three parts of the rule: the range being formatted, the cell or cells being checked, and the condition being tested. The “Applies to” range controls the first part; the formula controls the other two. Selecting a broad range does not automatically make the formula check the intended column, and a correct formula cannot color cells outside the Applies to range.
When the source column is fixed, lock its letter with a dollar sign, as in $C2. Keep the row relative so each record checks its own status. If the condition refers to a fixed cell instead—for example, a threshold stored in H1—you may lock both parts of that reference as $H$1. Relative and absolute references can be combined in one formula to match the layout.
Useful whole-row examples
Assume the rule’s Applies to range is A2:F100 unless an example says otherwise. Change the column letters and starting row to match your own sheet.
- Highlight a status: With the status in C2:C100, use
=$C2="Complete". Every cell in a record’s A:F range is formatted when that row’s status is Complete. - Flag overdue, unpaid items: With due dates in D2:D100 and payment status in E2:E100, use
=AND($D2<TODAY(),$D2<>"",$E2<>"Paid"). The date must be earlier than today, the date cell must not be blank, and the payment status must not be Paid. The blank-date check prevents empty date cells from being treated like old dates. - Highlight a priority or large amount: With priority in C and amount in D, use
=OR($C2="Urgent",$D2>=1000). The row is formatted if either condition is true. The threshold of 1000 is only an example; use a value appropriate to the sheet. - Mark rows with a missing owner: With owner names in B2:B100, use
=$B2="". This detects cells that appear empty, including formulas that return an empty string. If spaces may have been entered, clean the data or use a formula that accounts for them rather than assuming a visually blank cell is truly empty.
For each example, the formula’s row number must match the first row of the Applies to range. If the range starts on row 5, change references such as $D2 to $D5.
Common mistakes and how to fix them
A frequent problem is highlighting one row above or below the intended record. Compare the first row in Applies to with the row number in the formula. For example, a range beginning at A2:F100 should use a formula referencing row 2. The formula does not need to list every row; Excel adjusts a relative row reference as it evaluates the range.
Another common issue is forgetting to lock the condition column. If the formula uses =C2="Overdue" while the rule covers A:F, the reference may shift across columns as Excel evaluates the rule. Use =$C2="Overdue" to keep the condition in column C while allowing the row number to change.
Check for differences between the condition and the actual cell contents. Text such as “Overdue ” with an extra trailing space may not match “Overdue.” Likewise, a date that looks like a date but is stored as text may not compare as expected. Test the source data directly, and check whether a blank cell is part of the condition. A formula using <>"Paid" will also be true when the status is blank unless you explicitly exclude blanks.
If the rule seems correct but no formatting appears, open Home > Conditional Formatting > Manage Rules and inspect the Applies to range. Confirm that the chosen format is visible against the existing cell style, and check whether another rule with higher priority is taking precedence. Temporarily choose a strong fill color as a diagnostic, then restore the preferred style once the rule behaves as intended.
Rule order, edits, and maintenance
A worksheet can have several conditional formatting rules that affect the same cells. In Manage Rules, review the rule order and the Applies to ranges. Where rules conflict, priority matters; the “Stop If True” option can prevent lower-priority rules from being evaluated after a matching rule. Use it only when that stopping behavior is intended. For instance, a red overdue rule and a green paid rule may need distinct conditions so a paid item does not also meet the overdue rule.
When updating a rule, make sure you are viewing rules for the correct worksheet or selection in the Manage Rules dialog. Editing one rule’s formula does not automatically fix an incorrectly sized Applies to range. If you copy and paste formatted cells, Excel may create or adjust rules, so it is worth checking the rule list after major layout changes. Removing an obsolete rule can also prevent confusing results when multiple rules overlap.
Conditional formatting is useful for scanning, but it should not be the only way important information is conveyed. A color-only signal can be hard to interpret for people who cannot distinguish certain colors or when a worksheet is printed without color. Keep the status text visible, use clear labels, and choose formatting with sufficient contrast.
Using rules with Excel tables
Excel tables are useful when records are added or removed regularly. You can select the table’s data columns and create a formula-based conditional formatting rule, using the first data row in the formula. Tables also provide built-in banded row styles, which alternate formatting for readability without testing a condition.
Do not assume every change to a table will preserve the exact rule range you intended. Check the Applies to range after adding records, inserting rows, or copying cells, particularly if the rule was created before the table or if formatting was pasted from elsewhere. If the rule must cover new records, confirm that the Applies to range expands as expected in your workbook. A table style is often the simpler choice when the goal is just alternating bands; a formula rule is appropriate when the color should depend on status, date, amount, or another value.
When to use another approach
Conditional formatting is a good fit when the highlight should respond automatically to cell values and the underlying data should remain unchanged. It is not necessary for every visual task. If all you need is alternating row shading, format the data as a table using Home > Format as Table, or use a table style if the range is already a table. For a one-time visual change that does not need to update, ordinary cell formatting may be simpler.
If a workbook has many large conditional formatting ranges and becomes difficult to maintain, narrow the ranges to the actual data columns and rows, remove duplicate rules, and simplify overlapping conditions. A helper column can make complicated logic easier to audit: calculate a clear label such as “Review” in that column, then base the row rule on the label. That adds a visible calculation to the sheet, so use it when clarity matters more than keeping the worksheet visually minimal.
Frequently Asked Questions
Can I highlight an entire row based on one cell?
Yes. Apply the rule to all columns in the record and use a formula that checks the key cell on the same row. For example, for status in column C and a range beginning on row 2, use =$C2="Overdue".
Why does conditional formatting highlight the wrong row?
Most often, the formula’s row number does not match the first row in the Applies to range. If the range starts at row 2, the formula should refer to row 2; if it starts at row 5, use row 5. Also check that the condition column is locked with a dollar sign.
Can I format the whole worksheet row?
Yes. You can apply a rule to worksheet rows, but for ordinary records it is usually better to select the used data columns. A bounded range avoids formatting unrelated cells and makes the rule easier to review.
Can a row be highlighted when two conditions are met?
Yes. Use the AND function in a formula rule. For example, a due-date-and-payment condition can require an old due date, a nonblank date, and a status other than Paid. Use OR instead when any one of several conditions should trigger the format.
Will the rule apply to rows I add later?
It may, depending on how the range and worksheet are structured. After adding records, check Manage Rules and confirm that the Applies to range includes the new rows. Excel table behavior and pasted formatting can affect how rules extend, so verify rather than relying on an assumed expansion.
Related
- Excel New Comment Shortcut: Windows, Mac, and Notes
- Excel Next Sheet Shortcut for Windows, Mac and Web
- Excel Shortcut Keyboard Mat: What It Is and How to Choose
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel PivotTable Testing: Checks, Steps, and Common FixesTo test an Excel PivotTable, compare its results with a small source dataset whose totals you can verify…
- How to Add Multiple Audio Files in PowerPoint SlidesTo add multiple audio files in PowerPoint, insert each file separately using Insert > Audio, then choose how…
- How to Get Around a Circular Reference in Excel SafelyTo get around a circular reference in Excel, first find the cell or formulas creating the loop, then…
- Google Sheets FILTER Function Syntax, Examples, and TipsGoogle Sheets uses the FILTER function to return rows or columns that meet one or more conditions. Its…
- Can You Put a Page Break in Excel? Here’s HowYes. In Excel for Microsoft 365 on Windows, select the cell below and to the right of where…
- Google Sheets Filter Custom Formula: How It WorksYes. In Google Sheets, choose Data > Create a filter, open a column’s filter menu, then select Filter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.