Go Back

How to Highlight Values Between a Range in Excel

When working in Excel, you may want to highlight only certain cells. This tutorial will show you how you can highlight only the cells that fall within your specified range. 

Highlight values between a range in Excel

Generic Formula

=AND(cell>=lowervalue,cell<=uppervalue)

AND is a conditional function that returns TRUE when all conditions are met. With the use of Conditional Formatting, you can highlight the cells containing values in a certain range with AND function. To use Conditional Formatting, continue as follow

  1. Select an applied range, for example, B5:G7.
  2. Go to Home, Conditional Formatting, New Rule, Use a formula to Determine which cells to format.
  3. Insert the formula =AND( B5 >= 30, B5 <= 70).
  4. Go to Format, Fill and Choose the highlight color.

Note

The inserted cell must be an “active cell” in the selected range, in this case, B5.

Figure 1. Highlight values between 30 and 70 with AND in Conditional Formatting

Explanation of formula

Conditional Formatting allows the inserted rule to be applied to all cells in the selected range. In the example above, B5 is a relative address to range B5:G7, so the AND function is applied to every cell in B5:G7. The first argument collects the values above 30 and the second argument collects those under 70. AND function combines the arguments to collect the values between 30 and 70; then the Conditional Formatting will highlight the values between them.

Lock in the Ranges

If you want to change the upper and lower limit values without having to go to Conditional Formatting again, you can reference the limits. For example,

=AND(B5>=$E$3,B5<=$G$3)

Now you can freely change the values inside E3 and G3, and the Conditional Formatting rule will be automatically applied.

Another way of doing this is to rename the limit cells E3 and G3. On the Formula Bar, there is a name box where you can set a name to the selected cell and use it without having to set a reference.

Figure 2. Using name box to lock reference

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

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm trying to use conditional formatting to highlight cells with time values between (or including) 0:00 and a value in another cell, but this is not working.
Solved by I. H. in 12 mins
I want to highlight unique values between two workbooks. I have a list of all poeple who have take a course in one book, and thise that still remain members of the assicuation in another
Solved by A. J. in 14 mins
i need a formula that will highlight an entire row when TODAY between two dates
Solved by I. C. in 23 mins

Leave a Comment

avatar