Conditional Formatting is a feature in Excel that allows us to change the format of cells based on a set of rules or conditions. We can apply the AND function with conditional formatting to get highlighted the values in the Excel spreadsheet. In this tutorial, we will see how to highlight the values between a range in Excel. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
General Formula
Here, we come up with a simple formula to highlight values between a specific range in your Excel worksheet.
=AND(A1>=lower,A1<=upper)
Syntax Explanations
- AND – This function will check multiple conditions and returns TRUE if all the conditions are evaluated as true, otherwise returns FALSE. Read more on the AND Function.
- A1 – It represents the input value.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
How to Apply Formula to Highlight Values Between a Range?
Sometimes, we want to highlight some values on the spreadsheet in order to identify them based on some criteria. This formula can make use of the AND function for doing that. For instance, we will have a group of people of various ages, and we want to extract certain people based on the specific age group. To do so, select the input range where you want to apply the formula.
We will apply the above-mentioned formula with conditional formatting in order to highlight the values in the range. For that, go to the Home tab, select the Conditional Formatting option under the Styles section.
You have to select the New Rule option from the conditional formatting drop-down button.
It will open the New Formatting Rule wizard, where you have to select Use a formula to determine which cells to format option under the Select a Rule Type section. Under the Edit the Rule Description section, you have to enter the formula in the given field.
=AND(C3>=22, C3<=29)
Then, you need to click on the Format button to view the Format Cells dialogue. Go to the Fill tab and select the Background Color as you wish from the provided color palette. You will need to click on the OK button twice.
Finally, you will see the highlighted values in the selected range, as shown in the below image.
Alternate Example
Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.
- In the below example image, you can see the list of books and their prices in the range. And we want to highlight the prices which are between 100 and 300.
- First, you will need to select the input range.
- Go to the Home tab, and select the Conditional Formatting option under the Styles section.
- Select the New Rule option from the conditional formatting drop-down button.
- It will open the New Formatting Rule wizard, where you have to select Use a formula to determine which cells to format option under the Select a Rule Type section.
- Under the Edit the Rule Description section, you have to enter the formula in the given field.
- Then, you need to click on the Format button to view the Format Cells dialogue.
- Go to the Fill tab and select the Background Color as you wish from the provided color palette.
- You will need to click on the OK button twice.
- Finally, you will see the highlighted values in the selected range, as shown in the below image.
Wrap-Up
This tutorial described the simple formula to highlight the values between a range in Excel. If you have any issues regarding this article or any unresolved query, please comment in the comment box below. We will assist you. Thank you for visiting our site!! To learn more, check out AawExcel and Excel Formulas!
Video Tutorial
The following video will show you how to apply an Excel formula to highlight the values between a range in Excel.
Read Ahead:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Deepak is an Excel enthusiast and data analyst with over 5 years of experience in the field. I’m a renowned author for AAW Excel who connects with a committed group of Excel users by imparting his professional knowledge and helpful advice. No matter your level of Excel proficiency, my articles and tutorials will teach you something new and beneficial. Connect me via my social links!… Mastodon & More!
