ThisCalculator

Everyday

Highlight Values Between: How It Works & When to Use It

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This guide explains how to highlight values between two numbers in spreadsheet programs and data tools, what the condition actually does, and how to avoid the common mistakes that make it return nothing. You will learn the exact rule syntax, how to set up the rule in major tools, and how to troubleshoot when highlighting unexpectedly fails.

Formula

The core condition for “between” is a compound comparison: a value v is between a lower bound L and an upper bound U if and only if v ≥ L AND v ≤ U. In spreadsheet syntax, this is written as AND(v>=L, v<=U) or (v>=L)*(v<=U) in array formulas. The bounds are inclusive by default; if you want exclusive bounds, replace >= with > and <= with <.

Variables:

  • v – the cell value you are testing (any real number).
  • L – the lower boundary (inclusive unless stated otherwise).
  • U – the upper boundary (inclusive unless stated otherwise).

The result is a Boolean: TRUE (highlight) or FALSE (no highlight). In the calculator above, the first output returns 1 if the test value falls within the bounds (inclusive) and 0 otherwise.

Worked Example

Using the defaults from the calculator: lower bound = 10, upper bound = 20, test value = 15.

  1. Check the lower bound: is 15 ≥ 10? Yes.
  2. Check the upper bound: is 15 ≤ 20? Yes.
  3. Both conditions are true, so the value is between.

The calculator returns 1 for “Value is between”, confirming the logic. The helper outputs show the margins: 15 – 10 = 5 (distance from lower bound) and 20 – 15 = 5 (distance from upper bound).

How to Set Up a Between Highlight Rule

Most spreadsheet tools offer a built-in “Between” rule, but the exact menu path varies. The general steps are:

  1. Select the range of cells you want to format (e.g., A2:A100).
  2. Open the conditional formatting menu (usually under Format or Home > Conditional Formatting).
  3. Choose “Highlight Cell Rules” then “Between” (in Excel) or create a custom formula (in Google Sheets).
  4. Enter the lower and upper values. These can be typed numbers, cell references, or formulas.
  5. Pick a fill color or text style and click OK.

In Google Sheets, the custom formula version is =AND(A2>=10, A2<=20) applied to the range A2:A100, with the formula written relative to the first cell in the selection. In Excel, the built-in “Between” dialog does not require a formula, but if you use a formula, it must be relative to the active cell.

Common Mistakes and Misconceptions

One frequent error is mixing up inclusive vs. exclusive bounds. If you want to exclude the endpoints, you must use > and <, not >= and <=. Another common mistake is forgetting that the lower bound must be less than or equal to the upper bound; if you swap them, the rule will highlight nothing because no number can be both greater than 20 and less than 10.

A third issue is using absolute vs. relative references incorrectly in custom formulas. When you write a formula for the first cell in the range, the tool will adjust it for each row. If you accidentally use $A$2 instead of A2, every cell will be tested against the value in A2, not its own value.

Finally, blank cells and text values are not highlighted by numeric between rules. If your range contains text, the rule will ignore those cells, which can make it appear that the highlighting is missing some rows.

How to Choose the Right Bounds

Choosing bounds depends on what you are trying to visualize. For data cleaning, you might set bounds to flag outliers (e.g., values below the 5th percentile or above the 95th percentile). For grading, bounds might represent pass/fail thresholds. For inventory, bounds could represent reorder points.

When the bounds are dynamic (e.g., based on a mean or a target cell), use cell references in the rule instead of hard-coded numbers. This way, if the target changes, the highlighting updates automatically. For example, in Excel, a rule like =AND(A2>=$B$1, A2<=$B$2) uses the values in B1 and B2 as the lower and upper limits.

Limitations and Edge Cases

The between condition is purely numeric; it does not work with dates or times unless you convert them to serial numbers (which spreadsheets do internally). For dates, the same logic applies but you must enter the dates as date values or use the DATE function. For text, there is no natural “between” unless you are comparing alphabetical order, which most tools do not support in conditional formatting directly.

Another limitation is performance. Applying a between rule to a very large range (hundreds of thousands of cells) can slow down recalculation, especially if the rule uses volatile functions like TODAY(). In such cases, consider using a helper column that computes a TRUE/FALSE value and then format based on that column.

Finally, be aware of how your tool handles rounding. A value like 10.0000001 is not equal to 10, so it will not be highlighted if the upper bound is exactly 10. If you need to include values within a tiny tolerance, add a small epsilon to the bound.

Frequently Asked Questions

Does “between” include the endpoints? Yes, by default the standard between condition is inclusive. If you want to exclude the endpoints, change the rule to use greater-than and less-than instead of greater-than-or-equal and less-than-or-equal.

Why is my between rule not highlighting anything? The most common cause is that the lower bound is greater than the upper bound. Double-check the order. Another cause is that the range you selected does not include the active cell, or the formula references the wrong cell. Also confirm that the values are numbers, not text stored as numbers.

Can I use a between rule with dates? Yes, but you need to enter the dates as date values or use the DATE function. In Excel, you can type =AND(A2>=DATE(2025,1,1), A2<=DATE(2025,12,31)). In Google Sheets, the same formula works.

How do I highlight values between two other cells? Instead of typing numbers, reference the cells that hold the bounds. For example, if the lower bound is in B1 and the upper bound in B2, use =AND(A2>=$B$1, A2<=$B$2). Make sure to use absolute references for the bound cells so they do not shift when applied to the range.

Can I highlight values between in a pivot table? Conditional formatting in pivot tables works differently. You can apply a rule to the values area, but the rule will apply to all cells in that field. A better approach is to create a calculated field that returns TRUE/FALSE and then format based on that field, or use a regular range alongside the pivot.

Related

Formula used
in low | Lower bound | - | 10 | 0
in high | Upper bound | - | 20 | 0
in val | Test value | - | 15 | 0
out between | Value is between | - | if(val >= low && val <= high, 1, 0) | 0
out low_ok | Lower bound check | - | val - low | 2
out high_ok | Upper bound check | - | high - val | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.