Conditional Formatting Between Two Values in Google Sheets

Sometimes while working on a dataset you need to highlight some specific data so that you get that data quickly. But completing this work manually is nearly impossible or if you manage to do it, it will cost you valuable time. In this case, you can your conditional formatting. Conditional formatting can highlight specific data very quickly. In this article, we will learn how to use conditional formatting between two values in google sheets.

Overview of conditional formatting between two values in google sheets

Here is the overview of this article. Hope you will learn more after you go through the whole article below.


A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.


What Is Conditional Formatting in Google Sheets?

While working on Google Sheets, your dataset may contain different types of products. Each category may have unique or duplicate values. The Conditional Formatting feature in Google Sheets allows us to format and highlight datasets properly based on specific rules or criteria. You can apply the Bold, Italic, Underline, and Strikethrough formats, change font colors, and highlight cells based on criteria using this feature.


3 Suitable Ways to Apply Conditional Formatting Between Two Values in Google Sheets

There are 2 types of datasets in this article. The first dataset contains Company Name, Sales for July and August, Highest Value, and Lowest Value. The highest value represents the maximum range of conditional formatting and the lowest value represents the lowest value of conditional formatting. We will conduct the method between these two values. Here we will use conditional formatting between 2 numbers using the following dataset.

Dataset of using conditional formatting between two values in google sheets with number

Now, the second dataset contains Company Name, Meeting date of July and August, Highest Value, and Lowest Value. The highest value represents the most previous date and the lowest value represents the most recent date to conduct conditional formatting. We will complete conditional formatting between these two dates. we will use conditional formatting between 2 dates using the following dataset.

Dataset of using conditional formatting between two values in google sheets with dates


1. Applying Is Between Criteria

We will use conditional formatting between two values in google sheets by applying the default Is Between criteria from conditional formatting.

1.1. Between Numbers

Here, we will execute the formula between two numbers.

๐Ÿ“Œ Steps:

  • First, select the range C6:D11 to apply the conditional formatting.

selecting range

  • Then, go to Format >> Conditional formatting from the menu bar.

selecting conditional formatting from menu bar

  • After that, Conditional format rules window will pop up with the selected range in Apply to rangeย box.

conditional formate rules

  • Now, select Is between from Format rules box as below.

selecting isbetween

  • This option will show two boxes to enter the formula.

value and formula box

  • Here, enter the following formula in the first Value or formula box.
=$G$5
  • Subsequently, insert the formula below in the last Value or formula box.
=$F$5

enter cell reference

  • Now, change the default color from the Formatting style box and click on Done.

selecting colour

  • Now, the output will be as below.

Final Output of using conditional formatting between two values in google sheets with number

Read More: How to Use IF Condition Between Two Numbers in Google Sheets


1.2. Between Dates

We will apply this formula to another dataset that represents the date.

๐Ÿ“Œ Steps:

  • Similarly, complete the first 4 steps already shown in the previous part.
  • Now, enter the formula in the first Value or formula box of Conditional rules format window.
=$G$5
  • Subsequently, insert the formula below in the last Value or formula box and change the color from the Formatting style.
=$F$5
  • Then click on Done to execute this formula.

cell reference

  • Here, is the output of using the same formula with the same process.

Final output of using conditional formatting between two values in google sheets with dates

Read More: How to Calculate Time Between Dates in Google Sheets (6 Ways)


Similar Readings


2. Using ISBETWEEN Function

Now, we will use conditional formatting between two values using ISBETWEEN Functionย in the conditional formatting.

2.1. Within Numbers

Moreover, we will use the dataset of numbers to execute this formula.

๐Ÿ“Œ Steps:

  • In the beginning, select the range and select conditional formatting from menu bar as before.
  • Therefore, the Conditional format rules box will pop up with the selected range.
  • Now, select Custom formula is from Format rules box.

select custom formula is

  • Now enter the formula in the formula box and change the default color from the Formatting style option.
  • After that, select Done to execute the formula.
=ISBETWEEN(C6,$G$5,$F$5)

enter formula

  • In the end, the output is below.

Final output of using conditional formatting between two values in google sheets with number

Read More: Generate Random Numbers or Text Between Limits in Google Sheets


2.2. Within Dates

Here, we will execute this formula using another dataset that represents date.

๐Ÿ“Œ Steps:

  • In the beginning, complete the initial 4 steps as these steps are similar in this case also.
  • Correspondingly, once the Conditional format rules window appears, enter the formula in Values or formula box and change the default color from Formatting style.
=ISBETWEEN(C6,$G$5,$F$5)
  • Finally, click on Done and complete the process similarly.

entering furmula

  • In the end, the final output is below.

Final output of using conditional formatting between two values in google sheets with date

Read More: How to SUMIF Between Two Dates in Google Sheets (3 Ways)


Similar Readings


3. Executing AND Function

Moreover, we will execute the AND Function using both the dataset that represents number and date.

3.1. Between Numbers

we will use conditional formatting between two values applying AND function similarly we applied different procedures before.

๐Ÿ“Œ Steps:

  • First of all, complete all the processes before entering the formula into Value or formula box of Conditional format rules As the process is already shown before.
  • Now, enter the AND Function with the following formula and change the default color from Formatting style.
=AND(C6>=$G$5, C6<=$F$5)
  • Finally, click Done to execute the formula.

Enter AND Function

  • In the end, the final output will be as follows.

Final output of using conditional formatting between two values in google sheets with number

Read More: Calculate Percentage Difference Between Two Numbers in Google Sheets


3.2. Between Dates

Now, we will apply the same formula using another dataset that contains a value representing the date.

๐Ÿ“Œ Steps:

  • As the first 4 steps are the same in every process, complete all the similar steps which are shown before.
  • After that, enter the formula below in Value or formula box, once the Conditional formatting rules window appears.
  • Subsequently, change the default color from Formatting style option, so that the formatting is more visible.
=AND(C6>=$G$5, C6<=$F$5)
  • Then, click on Done to execute this formula.

Enter AND function in format rules

  • Finally, the output is as follows.

Final output

Read More: How to Filter Between Two Dates in Google Sheets


Things to Remember

  • Always remember to enter the correct formula otherwise it will highlight wrong values.
  • Formate cells if represents condition so you can use any condition to execute conditional formatting.

Conclusion

In this article, we explained how to use conditional formatting between two values in google sheets with practical examples. Hopefully, the methods will help you to apply these formulas to your own dataset. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo