Conditional Formatting: Use More Than One Rule in Excel

Yes. In Microsoft Excel for Microsoft 365 on Windows, you can apply multiple conditional formatting rules to the same cells or to overlapping ranges. Excel evaluates those rules by priority, so rule order—and whether Stop If True is selected—determines what formatting appears when more than one condition is met.

What multiple conditional formatting rules mean

A conditional formatting rule tells Excel to change a cell’s appearance when a condition is true. For example, a sales table might use one rule to mark low sales in red, another to mark high sales in green, and a third to highlight overdue orders. A cell can be covered by several rules at once, whether they all apply to one selected range or reach it through overlapping ranges.

You are not limited to one rule per cell. The rules can use different kinds of tests, such as a value being below a threshold, a date falling within a period, duplicate entries, or a formula returning TRUE. They can also apply different formatting, including a fill color, font style, border, data bar, color scale, or icon set.

The important distinction is between a rule existing and its formatting being visible. A rule may be present and true, yet a higher-priority rule can determine the visible formatting for a conflicting format property. This is why a worksheet can appear to ignore a rule even though the rule is still listed in Excel.

How Excel evaluates rules

Excel assigns conditional formatting rules a priority. In the Rules Manager, the rule at the top has the highest priority, followed by the rules below it. When multiple rules are true for a cell, their order matters. If two rules specify different values for the same formatting property—for example, two different fill colors—the higher-priority rule takes precedence for that property.

Rules that format different properties may be able to contribute separate parts of the final appearance. For instance, one rule might apply a fill while another applies bold text. However, overlapping rules can make the result harder to interpret, especially when several rules use the same property. Check the cells themselves after changing priority rather than assuming every true rule will be apparent.

Stop If True adds another control. When this option is selected for a rule and that rule is true for a cell, Excel does not evaluate lower-priority rules for that cell. Rules above it are still considered. Use the option when a match should prevent later rules from changing the result; leave it off when lower rules should still contribute formatting.

For example, imagine a table where overdue orders should be red, while orders with a high value should be bold. If the overdue rule is above the value rule and uses Stop If True, an order that is both overdue and high-value will not receive bold formatting from the lower rule. If the overdue rule does not stop evaluation, the two formats may both appear if they do not conflict.

When to use more than one rule

Multiple rules are useful when a worksheet needs to communicate more than one kind of exception or status. A project tracker could show overdue tasks in red, tasks due soon in amber, and completed tasks in gray. A budget sheet might flag spending above a limit while also using data bars to compare amounts. Separate rules make each condition easier to understand and adjust than one complicated rule that tries to handle every case.

Before adding rules, decide what each visual signal means. Give important exceptions a clear priority, and avoid using the same color for unrelated situations. A reader should be able to tell whether a red cell means “overdue,” “over budget,” or something else without guessing. A short legend near the data can help when a sheet uses several colors or symbols.

Use separate rules for conditions that need different formatting or may need to be edited independently.
Put urgent or exceptional conditions higher in the priority list.
Use Stop If True only when a matching rule should block lower-priority rules.
Keep rule ranges focused on the cells that need formatting; broad, overlapping ranges are harder to maintain.

Create and arrange rules in Excel

To add a rule in Excel for Microsoft 365 on Windows, select the cells to format and choose Home > Conditional Formatting. Choose a built-in option such as Highlight Cells Rules or select New Rule for more control. Pick the condition, set the format, and confirm it. Repeat the process to add another rule to the same cells or to a range that overlaps them.

To review or reorder rules, select a cell in the affected range and choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, use the “Show formatting rules for” list to choose the relevant scope, such as the current selection or the worksheet. If you only inspect the current selection, rules applying elsewhere may not be visible, so switch to the worksheet scope when tracking down a rule.

Select a rule and use the up or down arrows to change its priority. The top rule is evaluated first. You can also edit a rule, delete it, or change its Stop If True setting in the manager. After editing, verify the “Applies to” range: an otherwise correct rule will not affect cells outside that range. On Mac, the Conditional Formatting controls are also on the Home tab, though the dialog layout can differ. Excel for the web has conditional formatting controls too, but some rule-management details may be easier to handle in the desktop app.

Example: highlight an entire row by status

Suppose a task list has task names in A, owners in B, status in C, due dates in D, and notes in E and F, with records beginning on row 2. You want every cell in a row to turn red when the status is Overdue. Select A2:F100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With status values in C2:C100, use =$C2="Overdue" as the rule formula and choose a red fill.

The dollar sign before C keeps the status check in column C as the rule is applied across the row. The row number is not locked, so the rule checks C2 for the first row, C3 for the next, and so on. The formula returns TRUE for a row whose status is exactly Overdue, and the selected format then applies across A:F for that row. If you add another rule for a different status, give it a separate format and check the priority if the conditions could overlap.

To add a second condition to the same range, create a new rule with the same “Applies to” range and a different formula or built-in condition. For example, a rule could highlight high-priority tasks in yellow. If an overdue task can also be high priority, both conditions may be true. Decide whether overdue red should take precedence, whether the two visual treatments can coexist, or whether one rule should stop lower-priority evaluation.

Common problems and how to fix them

When a rule seems not to work, first check its scope and priority. In Manage Rules, confirm the intended cells appear in “Applies to,” then look for another higher-priority rule that formats the same property. Also confirm whether Stop If True is preventing a lower rule from being evaluated. Testing one rule at a time can reveal which condition is causing the unexpected appearance.

The rule is missing from the list: Change “Show formatting rules for” from the current selection to the worksheet or other relevant scope.
The format appears on the wrong cells: Correct the “Applies to” range and check whether the rule’s cell references are relative or absolute as intended.
One color replaces another: Review priority; two rules that set different fills cannot both display their fill colors in the same cell.
A formula rule behaves inconsistently: Check that the formula is written for the top-left cell of the applied range and that locked references use dollar signs appropriately.
The worksheet has become difficult to manage: Remove obsolete or duplicated rules and use a smaller number of clearly named conditions where practical.

Copied cells can also bring conditional formatting into a new area, producing extra rules or expanding ranges. If the workbook has accumulated unexpected formatting, inspect rules for the relevant worksheet and remove duplicates carefully. To remove formatting without deleting cell values, select the cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Check the selection first: clearing rules from an entire worksheet removes conditional formatting across that sheet.

Multiple rules versus one complex rule

Several simple rules are often easier to read and maintain than one complicated formula. For example, separate rules for “due soon” and “overdue” make it straightforward to change either condition’s color. A single formula can be useful when one condition genuinely combines several tests, but it may be harder for another person to understand or troubleshoot.

There is no need to create a separate rule for every cell if the same condition and format can apply to a range. A shared rule reduces duplication and makes later changes simpler. Conversely, separate ranges may need separate rules when their logic differs. The right approach is the one that makes both the conditions and their priority clear, not simply the one with the fewest entries in the Rules Manager.

Conditional formatting is also different from manually applying a fill color. A manual fill is a direct cell format; a conditional format appears when its rule’s condition is met and can change as the data changes. If the cell’s color remains after its value changes, check for a manual fill as well as conditional formatting rules.

Frequently Asked Questions

Can one cell have multiple conditional formatting rules?
Yes. A cell can be covered by multiple rules, including rules applied directly to the same range and rules applied to overlapping ranges. Their priority and formatting properties determine the visible result.

How many conditional formatting rules can Excel apply?
Excel does not restrict a cell to just one rule. The practical limit depends on workbook complexity and available resources, so keeping rules purposeful and removing obsolete ones is better than relying on a particular rule count.

What does Stop If True do in conditional formatting?
It prevents Excel from evaluating lower-priority rules for a cell when the rule with this setting is true. It does not stop rules above it from being evaluated.

Why does only one of my conditional formatting colors show?
If two true rules set different fills for the same cell, priority determines which fill is visible. Check the rule order in Home > Conditional Formatting > Manage Rules, and review whether the rules overlap or use Stop If True.

Can I apply different rules to overlapping ranges?
Yes. Excel allows rules on overlapping ranges. Use the Rules Manager to check each rule’s “Applies to” range and priority, since a cell in the overlap can be affected by more than one rule.

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.