Excel Format by Number Range with Conditional Formatting

How-to basics · Updated November 19, 2026

To format Excel cells by number range, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Between, enter the range’s endpoints, and choose a style. For multiple ranges or more tailored conditions, create separate conditional-formatting rules; they change how values look without changing the values stored in the cells.

What “format by number range” means in Excel

The phrase can refer to two different Excel features. Most people want conditional formatting: for example, making scores below 50 red, scores from 50 to 79 yellow, and scores of 80 or more green. Excel checks each cell’s value and applies a visual format when a rule matches.

A second possibility is a custom number format, which changes how a value is displayed according to limited numeric conditions. That can be useful when you want different display styles but do not need separate fills, fonts, or borders. It does not replace the underlying value, and it has more restrictions than conditional formatting.

Choose conditional formatting when the goal is to make ranges easy to spot, especially in a changing report. Choose a custom number format when you want the cell’s displayed number itself to vary—for example, showing negatives in parentheses—without adding visual highlighting. Neither feature changes a value used in calculations.

Apply a simple range rule

For one continuous range, Excel’s built-in Between rule is the quickest route. In Microsoft Excel for Microsoft 365 on Windows:

  1. Select the cells to format, such as B2:B100. Include only the data cells if the heading should not receive the format.
  2. Choose Home > Conditional Formatting > Highlight Cells Rules > Between.
  3. Enter the lower and upper values, then pick a preset format or choose Custom Format to set a fill, font, border, or number style.
  4. Select OK to apply the rule.

The Between rule includes both endpoints. For example, a rule from 50 to 79 applies to 50 and 79 as well as values between them. If you need a different boundary—for instance, values greater than 50 but less than 80—use a formula-based rule or combine appropriate less-than and greater-than rules.

Excel for Mac also provides conditional formatting, though the ribbon and dialogs can look different depending on the version. In Excel for the web, conditional formatting is available too, but some advanced rule editing options may differ from the Windows desktop app.

Build several bands without gaps

To classify a score column into three bands, apply one rule for each band to the same cells. For example, use less than 50 for low scores, 50 through 79 for middle scores, and greater than or equal to 80 for high scores. The cutoffs meet cleanly: every numeric score belongs to exactly one band, so there is no gap or overlap.

Select the data range and create the first rule through Home > Conditional Formatting > Highlight Cells Rules. Choose Less Than for the low band, Between for the middle band, and Greater Than or Equal To for the high band. Choose a distinct format for each rule, such as a red fill, amber fill, and green fill. If a rule type or comparison is not available in the menu as needed, create a formula rule instead.

Use clearly defined boundaries before formatting. Decide whether each threshold is inclusive, and check how values such as 49.9, 50, 79.9, and 80 should appear. For amounts with cents, specify whether the cutoff is based on the exact decimal value or a rounded display. A value displayed as 50 may actually be 49.995, depending on the data and number format.

Use a formula for custom ranges

Formula rules are useful when a built-in comparison does not express the condition you need, or when you want a range test combined with another condition. Suppose the values are in B2:B100 and a middle-band rule should identify values from 50 up to, but not including, 80. Select B2:B100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter the formula below. Set the style with Format, then confirm the rule.

With the values in B2:B100 and B2 as the active top cell in the selected range, use: =AND(B2>=50,B2<80)

Excel evaluates the formula for each cell in the selected range. The B2 reference is relative, so it adjusts to B3, B4, and the other cells as the rule is applied down the range. If the selected range starts at B5, use B5 in the formula instead. A frequent mistake is to use the wrong starting cell, which shifts the formatting by one or more rows.

For a rule that checks two conditions, AND requires both to be true. For example, you could use an additional condition to format only rows marked as active. If your condition is an either-or test, OR is the relevant function. Keep the formula focused on returning TRUE or FALSE; choose the appearance separately in the rule’s Format settings.

Conditional formatting versus custom number formats

Conditional formatting is the right tool for most visual range highlighting. It can apply fills, font styles, borders, and number formats based on a value, and its rules can be revised as thresholds change. A custom number format is instead a display instruction: it can show a number differently without changing the stored number, but it cannot produce the same flexible set of independently managed rules.

To create a custom number format in Windows, select the cells, open the Format Cells dialog from the Number group on the Home tab, choose the Number tab, and select Custom. A format such as [>=1000]#,##0.00;[<0](#,##0.00);0.00 displays values of 1,000 or more with commas and two decimal places, negative values in parentheses, and other values with two decimal places. For example, 1250.5 displays as 1,250.50, while -12.5 displays as (12.50). The underlying values remain 1250.5 and -12.5.

That format illustrates a limitation: custom number formats support numeric conditions in only the first two sections, with a remaining section used as the fallback. They are not a substitute for any number of conditional rules, and they do not let you set a different fill for every band. If the goal is a traffic-light report or a changing visual alert, use conditional formatting.

Choose an approach that fits the data

Before building rules, consider what the numbers represent and how the workbook will be used. A threshold that makes sense for test scores may not make sense for inventory, dates, or percentages. A percentage displayed as 20% is stored as 0.2, so a formula comparing the stored value to 20 would not test for 20 percent. For a 20% cutoff, the underlying threshold is 0.2.

  • Use built-in comparison rules for a straightforward single cutoff or a simple interval.
  • Use formula rules for combined conditions, nonstandard boundaries, or rules that depend on another cell.
  • Use color scales when you want a gradual visual comparison across a range rather than fixed categories. A color scale does not mean that each color represents a precise named band.
  • Use a custom number format when the desired change is how a value is displayed, not a fill or other visual alert.

For reports shared with others, include a legend or label that explains what each color means. Color alone may be unclear to someone viewing a printed copy, using a screen reader, or seeing the workbook with different display settings. A text label in a neighboring column can make categories easier to interpret.

Common problems and how to fix them

If the wrong cells are formatted, inspect the rule’s Applies to range. Open Home > Conditional Formatting > Manage Rules and check that the range begins and ends where intended. A rule may be scoped to only the initially selected cells, or it may accidentally include headings or totals.

If formula-based formatting appears one row off, check the cell reference in the formula against the first cell in Applies to. Also check whether a reference was locked with dollar signs. A reference such as $B$2 stays fixed; that can be useful when every row should compare against one constant cell, but it is usually not what you want when each row should test its own value.

If a value that looks like a number does not trigger a rule, it may be stored as text. Check the cell contents and data import process, and convert text values to numbers where appropriate. Also confirm that the threshold uses the stored value rather than the rounded value shown on screen. Changing decimal places in a number format changes display, not the value Excel compares.

When several conditional-formatting rules overlap, use Manage Rules to review their order and settings. Rule order can matter when rules apply to the same cells, especially when Stop If True is enabled. Simplify overlapping conditions where possible, and test boundary values before relying on the result in a report.

Frequently Asked Questions

Can Excel format a cell automatically when its value enters a range?
Yes. Conditional formatting recalculates as the cell value changes. Create a rule for the desired range and apply it to the cells that may change.

How do I color numbers between two values in Excel?
Select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Between. Enter the lower and upper limits and choose a format. The rule includes the endpoints.

Can I use more than three number ranges?
Yes. Add a separate conditional-formatting rule for each category, or use formula rules when the conditions are more complex. Review the rules in Manage Rules so their ranges and priorities are clear.

Does conditional formatting change the number in a cell?
No. It changes the cell’s appearance, not its stored value. Calculations continue to use the underlying value. A custom number format also changes display rather than the stored value.

Why does a percentage range rule give the wrong result?
Excel stores percentages as decimal numbers. For example, 20% is stored as 0.2, so set the rule’s threshold to the equivalent stored value rather than 20 when testing for 20%.

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.