Excel Conditional Formatting: Color Rows by Cell Text

Yes. In Excel for Microsoft 365, select the rows you want to format, create a formula-based conditional formatting rule, and use a formula such as =$C2="Overdue". If the text in column C is Overdue, Excel can apply the chosen format to every cell in that row within your selected range.

What conditional formatting does

Conditional formatting changes a cell’s appearance when a rule is true. Unlike manually applying a fill color, a conditional formatting rule recalculates as worksheet values change. If a status changes from Overdue to Complete, for example, the overdue color disappears and another rule may apply instead.

To color a whole row based on one cell, the rule needs two parts: a formula that checks the relevant cell and an Applies to range covering the row cells you want colored. The formula decides whether the rule is true; the range tells Excel where to show the format. These are separate settings, and confusing them is a common reason a rule colors only one cell.

This approach is useful for status trackers, inventory lists, schedules, review queues, and any table where a value in one column should make the rest of its row easier to scan. It does not change the cell values, sort the data, or permanently apply a color.

Set up a rule to color an entire row

For a concrete example, assume a task list has headers in row 1, task information in columns A through F, and status values in column C. The data begins in row 2, and you want every cell from A to F in a row marked Overdue to receive a colored fill.

Select the data area to format, such as A2:F100. Do not include the header unless you want the header included in the rule.
On the Windows ribbon, choose Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
Enter =$C2="Overdue" in the formula box. The formula refers to the status column while allowing the row number to change.
Choose Format, select a fill color or other visual style, and confirm the dialog.
Check the rule’s Applies to range. It should cover the full desired area, for example =$A$2:$F$100. Select OK to finish.

Now Excel evaluates the status in C2 for the first row of the selected range, C3 for the next row, and so on. When the status cell equals Overdue, the format appears across columns A through F of that row. If your data begins on a different row, use that starting row in the formula. For example, a range beginning in row 5 needs a formula referring to row 5, not row 2.

Why the dollar signs matter

The dollar sign locks a reference when Excel adjusts the formula as it checks other cells. In =$C2="Overdue", $C keeps the test in column C, while the unanchored row number 2 is allowed to become 3, 4, 5, and so on. This is exactly what a row-based rule needs: always inspect the status column, but inspect the status cell belonging to the current row.

If you wrote =C2="Overdue" with no dollar sign before C, Excel could shift the tested column across the selected range. Depending on the active cell and range, different cells might check different columns. If you wrote =$C$2="Overdue", both the column and row would be fixed, so every row would check C2. That would color every selected row based only on the first status cell.

A reliable habit is to select the range first and write a formula based on its top-left cell. For the range A2:F100, the top-left cell is A2, and the status cell on that row is C2. Keep the tested column fixed and leave the row relative.

Match exact text, partial text, or a different condition

The equality formula matches a complete cell value. With =$C2="Overdue", a cell containing Overdue is a match, but Overdue – call customer is not. Excel’s ordinary text comparison in this formula is not case-sensitive, so Overdue and OVERDUE are treated as equal.

For a phrase that can appear anywhere inside a cell, use a search formula. With status or notes in column C and the format applied to rows beginning at row 2, use =ISNUMBER(SEARCH("urgent",$C2)). SEARCH returns a position when it finds the word; ISNUMBER makes the rule TRUE when a position is returned and FALSE when there is no match. This finds urgent in text such as Urgent: call today. SEARCH is case-insensitive.

If capitalization itself matters, use EXACT for an exact, case-sensitive comparison. With the same layout, =EXACT($C2,"Overdue") is TRUE for Overdue but not for OVERDUE. EXACT checks the whole cell value, not just a portion of it.

You can also test another kind of value. With dates in column D, for example, a rule can compare the date in each row with a fixed date or with a date function. Make sure the formula tests the correct cell and that the cells contain real Excel dates rather than date-looking text. For rules involving dates, numbers, or blanks, choose a condition that reflects the actual data rather than relying on how a value happens to look on screen.

Use more than one condition

A row can be formatted only when multiple tests are true. Suppose the status is in column C and the assigned team is in column E. To highlight a row only when its status is Overdue and its team is Support, use =AND($C2="Overdue",$E2="Support"). AND returns TRUE only when both comparisons are true.

To highlight a row when either of two statuses appears, use =OR($C2="Overdue",$C2="Blocked"). OR returns TRUE when at least one condition is true. These formulas still need an Applies to range covering the cells in each row that should receive the format.

When a condition becomes complicated, break it into smaller parts before building the rule. Check that the column letters are anchored correctly, that text values match the entries in the sheet, and that commas and parentheses are balanced. A formula can be valid Excel syntax but still produce the wrong result if it points to the wrong column or starting row.

Choose colors and manage overlapping rules

Use a format that makes the rows easier to read without obscuring their contents. A light fill is often more legible than a very dark fill, especially when the worksheet already uses colored text. You can also use a font color, bold text, or borders instead of a fill. Conditional formatting changes only the visual display, not the underlying data or the order of rows.

If several rules apply to the same cells, Excel’s rule order and settings can affect the result. Open Home > Conditional Formatting > Manage Rules to inspect the rules, their Applies to ranges, and their order. The Show formatting rules for menu can be used to view rules for the current selection or the worksheet. If two rules set the same property, such as fill color, the higher-priority applicable rule generally determines the displayed format; the Stop If True setting can prevent lower rules from being evaluated.

Give each rule a clear purpose and avoid creating several rules that do the same job. If you want different colors for statuses such as Overdue, In Progress, and Complete, create a separate condition for each status, apply each to the same row range, and review their order if the formats overlap. Test the rules by changing a few sample status cells, then restore the original values.

Common problems and how to fix them

Only one cell changes color: The Applies to range may contain only the status column or the active cell. Edit the rule so the range includes every column you want formatted, such as =$A$2:$F$100.
The wrong rows are highlighted: The formula’s row number may not match the first row of the Applies to range. If the range starts at row 2, the formula should normally refer to row 2. Check the dollar signs as well.
Nothing matches: Verify the exact text, the column reference, and whether the cells contain extra spaces. A value that appears to say Overdue could include a leading or trailing space. You can inspect the cell’s contents in the formula bar or clean the source data if needed.
Every row receives the same color: A fully fixed reference such as =$C$2="Overdue" checks the same cell for every row. Remove the dollar sign before the row number so the test moves down the column.
The rule works for some rows but not others: Look for inconsistent text, blank cells, merged cells, or a range that ends before the later data. Also check whether another conditional formatting rule takes precedence.
A formula error appears: Confirm that the rule type is formula-based, the formula begins with an equals sign, and the formula is valid. In this guide, commas separate function arguments, as required by the stated formulas.

For a rule that uses partial text, remember that SEARCH may find the phrase inside a longer note. That is helpful for notes containing keywords, but it can create apparent false matches if the target letters occur inside an unrelated word. Use exact equality when the whole cell must match a status label.

Excel on Mac and Excel for the web

In Excel for Microsoft 365 on Windows, the route is Home > Conditional Formatting > New Rule. Excel for Mac also provides conditional formatting and formula-based rules through the Home tab’s Conditional Formatting controls; dialog wording and layout can differ slightly by release. The formula logic and the importance of the Applies to range are the same.

Excel for the web supports conditional formatting, though some advanced rule management and editing options can differ from the desktop app. If an existing formula rule is difficult to edit in the browser, open the workbook in the desktop version of Excel and use Home > Conditional Formatting > Manage Rules. Avoid assuming that a menu or dialog is identical across all platforms.

Frequently Asked Questions

Can I highlight a whole row based on one cell?
Yes. Apply a formula rule to the full row range and refer to the condition column with a fixed column and relative row, such as =$C2="Overdue" for data beginning in row 2.

Can conditional formatting find text within a cell?
Yes. Use =ISNUMBER(SEARCH("urgent",$C2)) when the relevant text is in column C. This finds the word within longer cell contents and ignores capitalization.

Does the formula need to use the top-left cell of the selected range?
It should be written for the first row of the Applies to range. If the range starts at A2, refer to row 2 in the formula; if it starts at A5, refer to row 5. Excel adjusts the relative row for the other rows.

Can I use different colors for different text values?
Yes. Create a separate rule for each value, such as one rule for Overdue and another for Complete, and set each rule’s format and Applies to range. Review rule order if conditions can overlap.

Will the color update when I change the cell text?
Yes. Conditional formatting recalculates when relevant values change, so the row’s appearance follows the condition. The rule does not alter the text or permanently paint the cells.

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.