Conditional Formatting Between Two Values in Excel

In Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home > Conditional Formatting > Highlight Cells Rules > Between and enter the lower and upper limits. For limits stored in cells, use a formula-based rule such as =AND(ISNUMBER(A2),A2>=$B$1,A2<=$C$1); it highlights numeric values in A2:A100 that fall between the limits in B1 and C1, including both endpoints.

What “between two values” means

A between rule checks whether each selected cell’s value falls within a lower and an upper boundary. If the boundaries are 20 and 50, values from 20 through 50 qualify; 19.99 and 50.01 do not. In Excel’s built-in Between rule, the endpoints are included. The rule changes how qualifying cells look, not the contents of the cells or the results of calculations that use them.

The limits can represent many kinds of comparable data. Common examples include sales within a target range, dates in a reporting period, test scores within an acceptable band, or inventory quantities that are neither too low nor too high. For date and time values, Excel compares their underlying numeric values, so a correctly entered date can be used as a boundary.

People sometimes mean a different task when they say “between two values”: they may want to compare two cells, highlight values that are outside the range, or mark values between a different pair of limits on every row. The built-in rule is convenient for fixed limits; formula-based formatting is more flexible for those variations.

Apply the built-in Between rule

For one fixed lower limit and one fixed upper limit, the built-in rule is usually the quickest option. On Windows, select the cells to check, then follow these steps:

Choose Home > Conditional Formatting > Highlight Cells Rules > Between.
Enter the lower and upper values in the two boxes. For example, enter 20 and 50.
Choose a preset format, or select Custom Format to set a fill, font, border, or number format.
Select OK and check a few cells at and just beyond the limits.

The selection matters: Excel applies the rule to the cells that were selected when the rule was created. To change that range later, open Home > Conditional Formatting > Manage Rules, select the rule, and inspect its Applies to range. If the worksheet has several rules, choose the appropriate display option in the manager so you can see the relevant rules.

Excel for Mac has the corresponding Conditional Formatting commands on the Home tab; the menu wording is generally similar, though dialog layout can differ by release. Excel for the web also supports conditional formatting, but available options and dialog details can vary. If you cannot find a particular rule type or need more control, open the workbook in the desktop app.

Use cell references for changing limits

A built-in rule is less convenient when the thresholds change often, or when each row has its own lower and upper values. A formula rule makes those relationships explicit. On Windows, select the target range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for the cells that should receive the format, choose the format, and select OK.

For a fixed pair of limits, assume the values to check are in A2:A100, the lower limit is in B1, and the upper limit is in C1. Apply the rule to A2:A100 and use =AND(ISNUMBER(A2),A2>=$B$1,A2<=$C$1). With B1 equal to 20, C1 equal to 50, and A2 equal to 42, the formula returns TRUE, so A2 is formatted. The ISNUMBER check prevents blank cells and text from being highlighted accidentally. The dollar signs lock the two threshold cells as the rule evaluates each row, while A2 adjusts to A3, A4, and so on.

For row-specific limits, assume the value to check is in A2:A100, each row’s lower limit is in B2:B100, and its upper limit is in C2:C100. Apply the rule to A2:A100 and use =AND(ISNUMBER(A2),A2>=B2,A2<=C2). If row 2 contains 42 in A2, 30 in B2, and 45 in C2, the rule returns TRUE for A2. The relative references move together on each row, so A3 is compared with B3 and C3. Do not lock the row references if each row needs its own limits.

Formula rules are evaluated relative to the top-left cell of the Applies to range. If the selected range begins at A2, the formula should normally refer to A2, not A1. A mismatch is a frequent cause of highlighting the wrong row. When editing the rule later, verify both the formula and the Applies to range.

Choose the right reference pattern

Reference style determines whether the rule follows one shared pair of limits or a different pair for each record. Keep the threshold references fixed when every value is measured against the same cells. Use relative references for row-by-row comparisons. Mixed references can be useful for a two-dimensional table: for example, a reference might keep the header row fixed while allowing the column to change.

One pair for the whole range: lock both the column and row of the threshold cells, as in $B$1 and $C$1.
One pair per row: let the comparison cells move with the value, as in B2 and C2.
One pair per column: arrange the limits in headers and lock the relevant row while allowing the column reference to adjust.

If users may enter the limits in reverse order, a simple lower-versus-upper test will return FALSE for every value until the limits are corrected. Decide whether to require the lower limit to be no greater than the upper limit or make the rule accommodate either order. Explicitly checking the input cells is often easier to understand and maintain than hiding that behavior inside a more complex formatting formula.

Examples for dates, scores, and exceptions

For a score sheet, a fixed range of 70 to 85 can identify results that meet a defined band. The rule includes scores of exactly 70 and 85. If the purpose is to flag results below a minimum or above a maximum instead, a between rule is not the right condition; create separate rules for the low and high exceptions, or use one formula that tests for either condition.

For dates, set the two thresholds to actual Excel dates, not text that merely looks like a date. For instance, a reporting window could run from the first day of a month through its last day. If a date cell contains a time as well, a value later on the final day may compare as greater than a boundary stored at midnight. To include every time on that final date, use a boundary at the start of the following day and an exclusive upper comparison in a formula-based rule. This distinction matters for timestamp data, but not for cells containing dates with no time component.

For inventory, a team might highlight quantities between a reorder point and a storage cap. If the goal is to see only exceptions, format quantities below the reorder point and above the cap instead. A visual rule should communicate what action is needed: a single color for the acceptable band and a separate warning color for exceptions can be clearer than making every ordinary value stand out.

Common problems and how to fix them

If nothing is highlighted, check that the values are numbers rather than text. A cell can display digits while storing them as text, especially after importing data. Also verify that the lower limit is not greater than the upper limit, that the rule applies to the intended cells, and that the formula’s first reference matches the top-left cell in that range.

If the wrong cells are highlighted, inspect relative and absolute references. A missing dollar sign can cause a shared threshold reference to shift as the rule moves down the range. Conversely, locking every reference in a row-specific formula can make every row compare against the same cells. Check the Applies to field for an offset or an unintended extra range.

If a correct-looking rule appears to have no visible effect, the chosen format may be hard to distinguish from existing formatting, or a different conditional formatting rule may take precedence. Open Manage Rules and review rule order, the ranges, and the formats. Excel evaluates rules in order; when rules conflict, priority can affect the displayed result. The Stop If True option can prevent lower-priority rules from being evaluated for cells where a higher-priority rule applies, so use it only when that is intended.

Conditional formatting is a display layer, not a filter. It does not sort qualifying values, hide the others, or provide a dependable way to calculate how many cells qualify. Use Excel’s filtering or sorting tools to inspect records, and use a counting formula or a PivotTable when you need a numerical summary.

Built-in rule or formula rule?

Use Highlight Cells Rules > Between when the condition is a simple range with fixed endpoints and you want to set it up quickly. It is easy to review later and does not require a formula. Its main limitation is that more complicated logic, such as row-specific thresholds or excluding text, calls for another approach.

Use a formula rule when the boundaries live in cells, vary by row, or need extra tests. Formula rules can combine conditions, but they are easier to misconfigure because reference behavior depends on the Applies to range. A good practice is to test the logic in a worksheet cell first if it is complex, then check several boundary cases after creating the formatting rule. Remember that conditional formatting uses the formula’s TRUE or FALSE result; it does not display that result in the formatted cell.

Frequently Asked Questions

Does Excel’s Between rule include the two endpoints?
Yes. A value equal to either entered limit is included. Use a formula rule with strict greater-than or less-than comparisons if you want to exclude one or both endpoints.

Can the two limits come from worksheet cells?
Yes. Use a formula-based rule with references to the threshold cells, and lock those references if the same limits apply throughout the selected range. This also makes it possible to change a limit without editing the rule.

How do I highlight values outside the two limits?
Create rules for values below the lower limit and above the upper limit, or use a formula rule that checks whether either condition is true. Keep the endpoints in mind: a value equal to a limit is not outside the range.

Why is a blank cell being formatted?
A formula rule may treat a blank in a comparison in an unexpected way, especially when blank cells are mixed with other data. Add an ISNUMBER check for numeric values, or an appropriate nonblank test for the kind of data being evaluated.

Does conditional formatting change the cell’s value?
No. It changes appearance only. The original value remains available to formulas, sorting, filtering, and other worksheet operations.

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.