# How to Do Conditional Formatting if Between Two Values in Excel

This guide will explain how to do conditional formatting if between two values in Excel.

Since it has several built-in functions and tools, we can easily perform complex tasks in Excel. Excel is a powerful tool for different purposes and situations. For example, we can highlight certain values in our dataset based on different conditions using conditional formatting.

So conditional formatting is an excellent feature in Excel that allows us to easily identify specific values in our data set. Moreover, it is extremely helpful when we need to highlight values in our data set that fit certain conditions or criteria.

In this case, we want to apply conditional formatting for cells if the value falls between two values. Luckily, it is easy for us to perform this task as Excel already has built-in conditional formatting rules we can utilize.

Moreover, we can also create a new rule and apply the `AND` function to apply conditional formatting between two values.

Let’s take a sample scenario wherein we need to do conditional formatting if between two values in Excel.

Suppose you are a teacher inputting the students’ grades in Excel. And you have set the passing score to 75 and above. So you want to identify the students who failed the test. To do this, you applied conditional formatting for cells with scores between 0 and 74.

Before we move on to a real example of doing conditional formatting if between two values in Excel, let’s learn more about the `AND` function.

## The Anatomy of the AND Function

The syntax or the way we write the `AND` function is as follows:

`=AND()`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• AND() is our `AND` function. And this function is used to check whether all arguments are TRUE and returns TRUE if all arguments are TRUE.
• logical1 is a required argument. So this refers to 1 to 255 conditions we want to test that can either be evaluated as TRUE or FALSE. Furthermore, we can input logical values, arrays, or cell references.
• logical2 is an optional argument. And this refers to 1 to 255 conditions we want to test that can either be evaluated as TRUE or FALSE. So this serves as a supplement to the first required argument.

Great! Now we can dive into a real example of doing conditional formatting if between two values in Excel.

## A Real Example of Doing Conditional Formatting if Between Twp Values in Excel

Let’s say we have a data set containing students’ test scores. So we have two columns showing the student’s name and their test score. And our initial dataset would look like this: For instance, we set the passing score for the test to be 75 and above. Thus, any score between 0 and 74 is considered a failing score. So we want to easily identify the students who failed the test. To do this, we can apply conditional formatting in our data set.

So there are two ways we can apply conditional formatting to our data set to highlight the students who failed the test. Firstly, we can simply use the built-in rule created by Excel. Then, we can choose the between rule to make the condition highlight cells containing scores between 0 and 74.

Secondly, we can also choose to create a new rule and apply the `AND` function to highlight cells containing scores between 0 and 74. So the `AND` function will check whether all cells contain a value between 0 and 74. If TRUE, the cell will be highlighted. If FALSE, the cell will remain as is.

Moreover, we can choose different formats, such as font color and cell color, allowing us to customize the formatting with whatever fits the situation. Our final data set looks like this: Amazing! Now we can proceed and explain the process of how to do conditional formatting if between two values in Excel.

## How to Do Conditional Formatting if Between Two Values in Excel

In this section, we will explain the step-by-step process of how to do conditional formatting if between two values in Excel. Additionally, each step contains detailed instructions and pictures to help you along the process.

To apply this method to our work, we can simply follow the steps below.

1. Firstly, we need to highlight our data set. Then, we will go to the Home tab and select Conditional Formatting in the Styles section. Next, we will choose Highlight Cells Rules in the dropdown menu. Lastly, we will click Between. 2. Secondly, a pop-up menu will appear at the side where we can input our condition. In this case, we will input “0” as our lower value and “74” as our upper value. Additionally, we can select the format we want to highlight the cells. Lastly, we will click Done to apply the changes. 3. And tada! We have successfully applied conditional formatting if between two values in Excel. 4. Alternatively, we can create a new rule and input a formula using the `AND` function. To do this, we will select our data set and go to the Home tab. Then, we will click Conditional Formatting. Next, we will select New Rule in the dropdown menu. 5. In the Conditional Formatting menu that appears at the side, we first have to select Formula in the Rule Type. Next, we will input the formula “=AND(C2>0, C2<74)”. Next, we can select the format we want to apply to the cells. Lastly, we will click Done to apply the changes. 6. And tada! We have successfully created a new rule that highlights cells if between two values in Excel. And that’s pretty much it! We have successfully explained how to do conditional formatting if between two values in Excel. Now you can choose any of the methods and apply them to your work whenever necessary.

Are you interested in learning more about what Excel can do? You can now use the `AND` function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us. Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

##### You May Also Like ## How to Convert Scientific Notation to Number or Text in Excel

This guide will explain how to convert scientific notation to numbers or text in Excel. Excel is a… ## How to Calculate Sample Size in Excel

This guide will explain how to calculate the sample size in Excel using two simple methods. The rules… ## How to Apply Different Types of Conditional Formatting in Excel

This guide will discuss how to apply different types of conditional formatting in Excel. Conditional formatting in Excel… ## How to Create a Win-Loss Sparkline Chart in Excel

This guide will explain how to create a Win-Loss sparkline chart in Excel to visualize positive and negative…  