Excel Conditional Formatting: Apply Rules to a Range

How-to basics · Updated November 18, 2026

To apply conditional formatting to a range in Excel for Microsoft 365 on Windows, select the cells, go to Home > Conditional Formatting, choose a rule type, and set its condition and format. Excel applies the rule to the selected cells; you can later change the range or rule in the rules manager.

What conditional formatting does

Conditional formatting changes how cells look when they meet a condition. It can highlight values, color a whole row based on one cell, mark duplicates, or display color scales and icons to make patterns easier to spot. The underlying cell values and formulas do not change; the formatting is a visual layer controlled by rules.

A rule can target a single cell, a rectangular range, or—in formula-based rules—a range whose cells are evaluated against a condition. For example, you might highlight overdue dates in a due-date column or shade every row in a task list when its status is “Late.” The important detail is that the range you select determines where Excel applies the formatting, while the rule determines which cells receive it.

Apply a standard rule to a range

For a quick highlight based on the values in the selected cells, use a built-in rule. These steps use Excel for Microsoft 365 on Windows:

  1. Select the cells to format. For example, select B2:B100 to format a sales or score column without including its header.
  2. Choose Home > Conditional Formatting.
  3. Choose a rule category, such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets.
  4. Choose a specific condition. For instance, under Highlight Cells Rules, choose Greater Than, enter a threshold, and select a format.
  5. Confirm the dialog or selection. Excel displays the formatting on cells that meet the rule.

As a concrete example, selecting B2:B100 and creating a “Greater Than” rule with a value of 500 highlights values above 500 in that column. If you intended to include 500 itself, choose a greater-than-or-equal condition if available, or use a formula rule. Choosing a preset color is quick, but Custom Format lets you choose a fill, font, border, or number format.

On Excel for Mac, the command is also on the Home tab under Conditional Formatting; dialog wording and layout can differ slightly. Excel for the web also provides conditional formatting from the Home tab, although some rule-editing options may be more limited than in the desktop app.

Choose the right rule type

Use the simplest rule that expresses the condition you need. Built-in rules are easier to create and maintain when the test is based directly on the value in each cell. Formula rules are useful when a condition depends on another column, multiple tests, or a pattern that the preset rules cannot describe.

  • Highlight Cells Rules: Compare values with a threshold, text, dates, or duplicates. These are useful for tasks such as marking amounts below a target.
  • Top/Bottom Rules: Highlight the highest or lowest values, including a specified number or percentage. These help identify outliers or leading results.
  • Data Bars: Add an in-cell bar that represents the relative size of each value. Bars are useful for comparing a column at a glance, but the numbers remain important for precise comparisons.
  • Color Scales: Shade cells along a gradient based on their values. A two-color or three-color scale communicates relative highs and lows; it does not by itself identify a fixed pass/fail threshold.
  • Icon Sets: Show symbols such as arrows or traffic-light icons according to value bands. Review the thresholds so that each icon means what readers will expect it to mean.
  • New Rule: Create a custom condition, including a formula-based rule, when the other categories do not fit.

For a fixed business threshold, a direct comparison rule is often clearer than a color scale. For ranking or relative magnitude, a scale or data bar may communicate more. Consider accessibility, too: do not rely on color alone when readers need to distinguish important categories. A text label, icon, or clear heading can reinforce the meaning.

Format an entire row with a formula

A common question is how to shade a full row when one cell in that row contains a particular status. Use a formula-based rule and apply it to all the columns in the rows you want formatted. The dollar sign fixes the status column, while the row number remains relative so Excel checks the matching row as it evaluates the range.

Assume the table has headers in row 1, task names in A2:A50, and status values in C2:C50; you want to shade A2:F50 whenever the status in column C is Late. Select A2:F50, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose the option to use a formula to determine which cells to format. Enter this formula:

=$C2="Late"

Choose Format, set the desired appearance, and confirm. Because the selected range begins on row 2, the formula refers to C2. Excel evaluates the condition for each row: the column stays C, while the row reference changes to 3, 4, and so on. If the selected range instead began on row 5, the formula should refer to C5. A mismatch between the top row of the range and the formula’s row reference is a frequent cause of formatting that appears shifted.

The formula compares the cell’s value with the text Late. If statuses may contain extra spaces or inconsistent wording, clean up the source data or use a more carefully designed condition. Conditional formatting follows the actual cell values; it does not infer that “Delayed” and “Late” mean the same thing.

Check and edit the range a rule applies to

Rules have an Applies to range that controls which cells Excel evaluates and formats. To inspect it in Windows, select a cell covered by the rule and go to Home > Conditional Formatting > Manage Rules. In the rules manager, use the “Show formatting rules for” list to view rules for the current selection or the worksheet, then inspect or edit the range shown under Applies to. If you do not see the expected rule, change that viewing scope before concluding it is missing.

You can adjust the range in the manager when the rule should cover more or fewer cells. For example, change B2:B100 to B2:B200 if new records have been added. Take care not to include a header accidentally: a threshold rule may treat the header as text, but an incorrectly anchored formula can make a row-based rule offset by one. After editing the range, check a few cells at the top, middle, and bottom to confirm that the condition follows the intended rows.

If you create a rule while a range is selected, Excel normally sets its scope to that selection. If you create it with only one cell selected, the rule may apply only to that cell. Selecting the intended range first is the most reliable way to avoid that surprise. You can also copy cells with conditional formatting, but copying may bring rules or formats into a destination where they overlap existing rules; inspect the rules manager if results become confusing.

Rules, priority, and overlapping formats

More than one conditional formatting rule can apply to the same cell. In that case, the rules manager shows their order, and priority can affect which formatting is visible. When rules set different properties—for example, one sets a fill and another sets a font color—both may be visible. When multiple rules set the same property, priority matters. The Stop If True option can prevent lower-priority rules from being evaluated once a higher-priority rule is true, where applicable.

When two rules seem to conflict, open Manage Rules, show the rules for the relevant worksheet or selection, and check both their order and their Applies to ranges. Move a rule higher or lower when that better reflects your intended priority, and use Stop If True only when lower rules genuinely should not contribute formatting. Removing an obsolete rule can be better than adding another rule to counteract it.

Conditional formatting is also different from a regular cell fill. A manually applied fill remains until changed, whereas a conditional fill appears or disappears as the cell values or rule conditions change. If a cell keeps a color after its value changes, check whether the color is a manual fill or is coming from another rule rather than assuming the conditional rule failed.

Common problems and practical checks

When a rule does not produce the expected result, check the scope and the condition before rebuilding it. These issues account for many apparent failures:

  • Wrong range: Confirm the Applies to address includes the intended cells and excludes headers or unrelated columns.
  • Misaligned formula: For a formula rule, make sure the relative row reference matches the first row of the applied range. Check which references use dollar signs: $C2 locks the column, while C$2 locks the row.
  • Text stored differently: A cell may contain a different word, an extra space, or a value of a different type from the one the rule expects. Compare the displayed value with the actual source data.
  • Overlapping rules: Inspect their order, conditions, and formatting properties in Manage Rules.
  • Manual formatting: A normal fill or font setting can make it harder to tell which appearance comes from a rule. Check the rule itself and remove unneeded manual formatting if appropriate.
  • New rows outside the range: A rule applied to a fixed address will not necessarily cover records added below it. Extend the range, or consider structuring the data as an Excel table and confirm the rule covers the table’s data rows.

For a table that will grow, using an Excel table can make data management and expansion easier, but always verify the rule’s applied range after adding rows. Do not assume that every copied row or pasted value will preserve the exact scope you intended. A quick check of the rules manager is more reliable than judging only by the visible color.

Frequently Asked Questions

How do I apply conditional formatting to an entire column?
Select the cells in the column that should be formatted, then create the rule from Home > Conditional Formatting. If the column has a header, usually select the data cells below it rather than the whole column. In Manage Rules, confirm that Applies to covers the intended rows.

Can conditional formatting highlight a row based on one cell?
Yes. Select the full row range you want formatted, create a formula-based rule, and refer to the condition column with a fixed column reference and a relative row reference. Make the formula’s row number match the first row in the selected range.

How do I copy conditional formatting to another range?
You can use Format Painter or copy and paste cells, but check the destination rule scope afterward. Copying may alter or add rules, especially when the destination already has conditional formatting. The rules manager lets you verify the resulting Applies to addresses.

Why is conditional formatting not working?
Check that the rule covers the cells in question, that its condition matches the underlying value, and that formula references are aligned with the range’s first row. Also check for higher-priority overlapping rules and manual formatting that may be obscuring the result.

Does conditional formatting change the cell value?
No. It changes the cell’s appearance when a rule is true; it does not edit the value or replace a formula in the cell. If you need to change data itself, use an appropriate editing method or formula rather than conditional formatting.

Related

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.