Conditional Formatting Multiple Cells in Excel: Rules

Yes. In Excel for Microsoft 365, select the cells you want to format, then choose Home > Conditional Formatting and create a rule; Excel applies it across the selected range. You can also use a formula-based rule to format whole rows or different cells according to values elsewhere in the worksheet.

What conditional formatting does across multiple cells

Conditional formatting changes a cell’s appearance when a rule is true. The rule might compare a value with a number, identify text, check a date, or evaluate a formula. Applying one rule to many cells avoids creating a separate rule for every cell, and the formatting updates when the underlying values change.

The cells covered by a rule are its “Applies to” range. For instance, one rule can color every value in a column that is below a target, or color each row in a table when that row’s status is “Late.” The format affects appearance, not the cell’s value: it does not replace data or change calculations.

Excel includes ready-made rules for common comparisons, such as greater than, duplicate values, and text that contains a word. Formula-based rules are more flexible, particularly when the condition depends on another column or must be evaluated separately for each row.

Apply one built-in rule to a range

To format multiple cells with the same kind of condition in Excel for Microsoft 365 on Windows:

Select the target cells. For a column of values, select the data cells rather than the heading if the heading should remain unchanged.
Choose Home > Conditional Formatting, then select a suitable rule category, such as Highlight Cells Rules or Top/Bottom Rules.
Choose the condition, enter the comparison value if requested, and select a format or choose Custom Format.
Review the result across the range. Change a value that should meet the condition to confirm that the formatting responds as expected.

For example, to highlight every amount above 1,000, select the amount cells before choosing Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter 1000 and choose a format. Excel evaluates the condition for each cell in the selected range; it does not compare the whole range as a single value.

You can also create a rule first and adjust its target afterward. Choose Home > Conditional Formatting > Manage Rules, select the rule, and edit the Applies to box. This is useful when the range has grown or when you want the rule to cover newly added rows.

Use a formula to format cells based on other cells

A formula rule is useful when the cell being formatted is not itself the value to test. In Excel for Microsoft 365 on Windows, select the cells to format and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, choose the format, and confirm.

Suppose a task list has task names in A2:A100 and statuses in D2:D100. To shade the task details in A2:F100 whenever a row’s status is Late, select A2:F100 and use this rule. The dollar sign fixes the status column, while the row number stays relative so Excel checks the status on each row.

With statuses in D2:D100 and the target range A2:F100, use =$D2="Late". For a row where D contains Late, the rule evaluates TRUE and formats cells A through F on that row; a different status leaves that row unformatted.

The formula is interpreted relative to the top-left cell of the Applies to range. If the range begins at A2, a reference to D2 is aligned with that first row. If the range instead begins at A5, the formula should normally refer to D5. A reference that is offset by one row can produce a rule that appears to color the wrong records.

Understand relative and absolute references

Cell references determine how a formula changes as Excel evaluates different cells. A relative reference such as A2 can shift across columns and rows. An absolute reference such as $A$2 stays fixed. Mixed references fix only one part: $A2 keeps column A fixed while allowing the row to change, and A$2 keeps row 2 fixed while allowing the column to change.

Use the reference style that matches the job:

To test each cell in a selected range against its own value, use a relative reference to the first cell in the range.
To test each row against a value in a fixed column, lock the column and leave the row relative.
To compare cells with one fixed threshold stored elsewhere, lock both the column and row of the threshold cell.

For example, suppose values are in B2:B20 and a threshold is in F1. Select B2:B20 to format and use =B2>$F$1. The first comparison checks B2 against F1; as the rule evaluates down the range, the B reference moves to the corresponding row, while F1 remains fixed. If F1 contains 50, a value of 60 in B2 meets the condition, while a value of 40 does not.

Format a whole row or several columns

To make a record easier to scan, apply a formula-based rule to the entire row range but test a single column. For example, a project table might occupy A2:F100, with a due-date status in column D. Select A2:F100 and use a formula that checks column D on the current row. Locking the column keeps the check in D as Excel formats cells across A:F; leaving the row number relative makes each record use its own status.

Formatting a row based on a date requires choosing the intended condition carefully. A date stored as an Excel date value can be compared with another date or a date-producing function, but text that merely looks like a date may not behave the same way. Check that the source cells contain actual dates, especially when values were imported or pasted from another system.

For a simple condition based on the selected cells themselves, a built-in rule is often easier to maintain. Use a formula when the condition depends on another column, combines multiple tests, or needs a fixed reference. Keeping the rule as simple as possible makes it easier for someone else to inspect later.

Apply rules to separate ranges

A rule can cover one continuous block, such as B2:B100, or multiple selected areas. When the target cells are in separate columns or blocks, open Home > Conditional Formatting > Manage Rules and inspect Applies to. The range shown there determines which cells the rule can affect.

For nonadjacent ranges, a practical approach is to create a rule for one range, then extend its Applies to range or create another rule for the other area. This makes the relationship between the formula and each range easier to understand. If the separate blocks begin on different rows, the formula’s relative references may need different starting rows, so verify each block rather than assuming one reference will align automatically.

To select multiple nonadjacent areas on Windows, hold Ctrl while selecting additional ranges. On Mac, use Command while selecting additional ranges. Some actions and dialog layouts can differ slightly on Mac, but the Conditional Formatting controls are available from the Home tab. In Excel for the web, the core conditional-formatting options are available, although some advanced rule-management tasks may be easier in the desktop app.

Manage rule order and overlapping formats

More than one rule can apply to the same cell. For example, a cell might meet both a rule for values above a target and a rule for duplicate values. In that situation, the visible result depends on the rules’ order and on which formatting properties they set.

Choose Home > Conditional Formatting > Manage Rules to review rules, adjust their order, edit them, or change their ranges. The Show formatting rules for menu lets you inspect rules for the current selection or worksheet. If a rule has Stop If True selected, Excel stops evaluating lower-priority rules for a cell once that rule is true. Use that option deliberately; it can prevent later rules from contributing their formatting.

When two rules set different properties, such as one setting a fill and another setting a font color, both may be visible. If two rules set the same property, their priority can determine which one appears. When formatting seems inconsistent, inspect every rule that covers the cell rather than editing only the most obvious one.

Common problems and how to fix them

The wrong row is highlighted: Check that the formula’s row number matches the first row in Applies to. Also check whether a column or row was locked unintentionally with dollar signs.
Only one cell changes: The Applies to range may contain only one cell. Expand it in Manage Rules, or select the full target range before creating the rule.
New rows are not formatted: The rule may cover a fixed range that ends above the new data. Extend Applies to or use an Excel table so its data range can expand as rows are added; verify that the rule includes new rows.
The condition never matches: Check for numbers stored as text, extra spaces in text, or dates stored as text. The displayed value can look correct even when the underlying value has a different type.
Colors seem to conflict: Look for overlapping rules and review their order in Manage Rules. A higher-priority rule or Stop If True may affect what you see.

Clear formatting only if you mean to remove the rule or its visual effect. Home > Conditional Formatting > Clear Rules offers options for clearing rules from selected cells or the entire worksheet. Clearing rules does not delete the values, but it does remove the conditional formatting instructions in the chosen scope.

Frequently Asked Questions

Can one conditional-formatting rule apply to multiple cells?
Yes. Select the whole target range before creating the rule, or edit the rule’s Applies to range in Manage Rules. Excel evaluates the rule for each applicable cell or row, depending on the references in the rule.

Can I highlight an entire row when one cell contains specific text?
Yes. Apply a formula-based rule to the full row range and make the formula check the relevant column, with the column fixed and the row relative. Make sure the formula is aligned with the first row in the Applies to range.

Can one rule format nonadjacent cells?
It can be applied to multiple areas, but disconnected ranges can make relative-reference behavior harder to check. Use Manage Rules to inspect each area, and consider separate rules when the ranges start on different rows or need different formulas.

Does conditional formatting change a cell’s value?
No. It changes how a cell appears when a condition is met. The underlying value remains available to formulas, sorting, and other workbook features.

Why does a rule work for some cells but not others?
Common causes include a range that does not cover every intended cell, a formula reference shifted from the range’s top-left cell, text stored instead of numbers or dates, or another overlapping rule taking precedence. Check the value, Applies to range, and rule order.

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.