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

Data Analysis

  • Excel Line Chart
  • Conditional Formatting New Rule with Formulas in Excel
  • Excel Bar Chart
  • How To Create Frequency Distribution in Excel
  • Randomize/ Shuffle List in Excel

References

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to use Excel ROW Function
  • To count total rows in a range in Excel
  • Two-column Lookup in Excel
  • Count rows with at least n matching values

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation exists in list
  • Excel Data validation must begin with
  • Excel Data validation must not contain
  • Excel Data validation no punctuation

Highlight values between in Excel

by

This tutorial shows how to Highlight values between in Excel using the example below;

Formula

=AND(A1>=lower,A1<=upper)

Explanation

Note: Excel contains many built-in “presets” for highlighting values above / below / between / equal to certain values, but if you want more flexibility you can apply conditional formatting using your own formula as explained in this article.

If you want to use conditional formatting to highlight cells that are “greater than X and less than Y”, you can use a simple formula that returns TRUE when a value meets those conditions. For example, if you have numbers in the range B4:G11, and want to highlight cells with a numeric value over 60 and less than 90, select B4:G11 and create a conditional formatting rule that uses this formula:

=AND(B4>=60,B4<=90)

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.

Worked Example:   Highlight unique values in Excel

Also note that we are using greater than or equal to, and less than or equal to in order to include both the lower and upper values.

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 AND with two conditions, the formula returns TRUE only when both conditions return TRUE, triggering the conditional formatting.

Worked Example:   Excel Data validation whole percentage only

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.

Worked Example:   Check multiple cells are equal in Excel

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:

Highlight top values in Excel

Next Post:

Highlight values greater than in Excel

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

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to calculate Day of the Year in Excel
  • Basic Overtime Calculation Formula in Excel
  • How to determine year is a leap year in Excel
  • Dynamic date list in Excel
  • Count birthdays by month in Excel

Grouping

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

General

  • AutoRecover file that was never saved in Excel
  • How to generate random number weighted probability in Excel
  • Lock Cells in a Worksheet Excel
  • Excel Default Templates
  • Cell References: Relative, Absolute and Mixed Referencing Examples
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning