Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create Gauge Chart in Excel
  • How to calculate average last N values in a table in Excel
  • Reverse List in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Get column index in Excel Table

References

  • Get nth match with INDEX / MATCH in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel INDIRECT Function
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation unique values only
  • Excel Data validation date in specific year
  • How To Create Drop-down List in Excel
  • Excel Data validation allow weekday only

Highlight values not between X and Y in Excel

by

This tutorial shows how to Highlight values not between X and Y in Excel using the example below;

Formula

=OR(A1<lower,A1>upper)

Explanation

If you want to use conditional formatting to highlight cells that are NOT between two values (a lower and upper limit), you can use a simple formula that returns TRUE when a value meets that condition. For example, if you have numbers in the range B4:G11, and want to highlight cells with a numeric value not between 20 and 110, select B4:G11 and create a conditional formatting rule that uses this formula:

=OR(B4<lower,B4>upper)

It’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case.

Also note that because we are using less than (<) and greater than (<), the lower and upper limit will not be included.

How this formula works

When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as the active cell, the rule is evaluated for each of the 40 cells in B4:G11 because B4 is entered as a fully relative address. Because we are using OR with two conditions, the formula returns TRUE when either condition return TRUE, triggering the conditional formatting.

Using other cells as inputs

You don’t have to hard-code the numbers into the rule and, if the numbers will change, it’s better if you don’t.

To make a more flexible, interactive conditional formatting rule, use other cells like variables in the formula. For example, if you want to use cell E2 for the lower limit, and cell G2 for the upper limit, you can use this formula:

=AND(B4<$E$2,A1>$G$2)

You can then change the values in cells E2 and G2 to anything you like and the conditional formatting rule will respond instantly. You must use an absolute address for E2 and G2 to prevent these addresses from changing.

With named ranges

A better way to lock these references is to use a named ranges, since named ranges are automatically absolute. If you name cell E2 “lower” and the cell G2 “upper”, then you can write the conditional formatting formula like so:

=AND(B4<lower,B4>upper)

Named ranges allow you to use a cleaner, more intuitive syntax.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function example in Excel
  • Invoice status with nested if in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • IF function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Calculate date overlap in days in Excel
  • Convert date to month and year in Excel
  • How to get workdays between dates in Excel
  • How to show last updated date stamp in Excel
  • Get fiscal quarter from date in Excel

Grouping

  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel
  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Group times into 3 hour buckets in Excel

General

  • How to calculate total from percentage in Excel
  • How to count total columns in range in Excel
  • How to test a range for numbers in Excel
  • Subtotal invoices by age in Excel
  • Using Existing Templates in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning