Conditional Formatting:Highlighting Values Between Two Given Values

If we want to highlight the values which are present between two given values then we can use Conditional Formatting.

YouTube Video

For example we have a database of 05 students for marks obtained by them in three subjects Maths, English and Science.

Now we want to highlight the marks which are between "65" to "85". To do so we can use Conditional Formatting. Let us see detailed explanation as below:

1. Select all the cells from which we want to highlight the marks.

2. Go to Home Tab. Select "Conditional Formatting", then select “Highlight Cells Rules”, then select “Between” option from the list of appeared list.

3. Now, you will see “Between Window” on your screen. Enter 1st value and 2nd value between which we want to highlight the values. Also select the formatting style from the list of list of appeared style. Below is the image for your reference.

4. We have entered 1st value as “65” and 2nd value as “85” as we want to highlight the marks between "65" and "85". Also we have selected formatting pattern “Green Fill with Dark Green Text”.

5. Once we click on “OK” button we can see that values between "65" to "85" are highlighted with “Green Fill with Dark Green Text”.

We can use different formatting style as per our choice.

Dynamic Conditional Formatting to Highlight Values Between Two Given Values

But every time we have to go to “Conditional Formatting” options and enter 1st value and 2nd value. This is little bit time consuming. Can we give some dynamic effect to this?

It means we have to just enter 1st value and 2nd value in the cells in excel sheet itself and our conditional formatting result will change as we change the values from excel cells.

As shown in below image we have to enter 1st value and 2nd value in the blank cells highlighted with yellow.

We have to follow all the steps as listed above. The only difference is Step 4. In step 4 above, we have entered 1st value and 2nd value directly in the “Between Window”. But here instead of inserting direct values, we have to give cell address of cells from excel sheet where we are going to enter the 1st value and 2nd value i.e. the cell address of the yellow highlighted cells from above image.

We will enter the same values as we entered above. 1st value as “65” and 2nd value as “85” to verify our result. We will find similar result as shown in below image.

Let’s take another example by changing 1st value and 2nd value. In this example we will enter 1st value as “80” and 2nd value as “90”.

Let’s take another example by changing 1st value and 2nd value. In this example we will enter 1st value as “50” and 2nd value as “60”.