If you are looking for some of the easiest ways to do Conditional Formatting based on another cell range in Excel, then you are in the right place. Sometimes it becomes necessary to highlight a cell range based on another cell range for some conditions while working with a large dataset in Microsoft Excel. This article will help you to know the ways of doing Conditional Formatting based on another cell range.
Here, I have the two data tables to demonstrate the ways of Conditional Formatting based on another cell range in Excel. The first table has Sales value for different years for some products and the second table contains Order Date, Delivery Date and Sales for some items of a company.
For this purpose, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.
1. Conditional Formatting Based On Another Cell Range for Equal to Operator
Here, we will highlight the cells of the Sales of 2019 column based on the cell ranges of the Sales of 2018 column. For Conditional Formatting, the condition would be that the cells of the Sales of 2019 column will be Equal to the cell ranges of the Sales of 2018 column.
Step-01:
➤Select the cell range on which you want to apply the Conditional Formatting (Here, I have selected the column Sales of 2019)
➤Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.
Then the New Formatting Rule Wizard will appear.
➤Select Use a formula to determine which cells to format option.
➤Click on Format Option.
After that, the Format Cells Dialog Box will open up.
➤Select Fill Option.
➤Choose any Background Color.
➤Click on OK.
After that, the Preview Option will be shown as below.
Step-02:
➤Write the following formula in the Format values where this formula is true: Box
=$D5=$C5When the cells of Column D will be Equal to the corresponding cells of Column C, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
After that, you will get the cells of the Sales of 2019 column highlighted whose values are Equal to the corresponding cells of the cells range of the Sales of 2018.
Read More: How to Do Conditional Formatting Based on Another Cell in Excel
2. Conditional Formatting Based On Another Cell Range for Not Equal to Operator
You can apply Conditional Formatting to the cells of the Sales of 2019 column by using Not Equal to Operator.
Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$D5<>$C5When the cells of Column D will be Not Equal to the corresponding cells of Column C, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
Afterward, you will get the cells of the Sales of 2019 column highlighted whose values are Not Equal to the corresponding cells of the cells range of the Sales of 2018.
Read More: Conditional Formatting Based on Multiple Values of Another Cell
3. Conditional Formatting Based On Another Cell Range for Greater than Operator
Now, we will highlight the cells of the Sales of 2019 column which will be Greater than the corresponding cell ranges of the Sales of 2018 column.
Step-01:
➤Follow Step-01 of Method-1.
Then, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$D5>$C5When the cells of Column D will be Greater than the corresponding cells of Column C, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
In this way, you will get the cells of the Sales of 2019 column highlighted whose values are Greater than the corresponding cells of the cells range of the Sales of 2018.
Read more: Excel Highlight Cell If Value Greater Than Another Cell
4. Using Less than Operator for Conditional Formatting Based On Another Cell Range
You can apply Conditional Formatting to the cells of the Sales of 2019 column by using Less than Operator.
Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$D5<$C5When the cells of Column D will be Less than the corresponding cells of Column C, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
Afterward, you will get the cells of the Sales of 2019 column highlighted whose values are Less than the corresponding cells of the cells range of the Sales of 2018.
Read more: How to Compare Two Columns Using Conditional Formatting in Excel
5. Conditional Formatting Based On Another Cell Range for Greater than Or Equal to Operator
Here, we will highlight the cells of the Sales of 2019 column which will be Greater than or Equal to the corresponding cell ranges of the Sales of 2018 column.
Step-01:
➤Follow Step-01 of Method-1.
Then, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$D5>=$C5When the cells of Column D will be Greater than or Equal to the corresponding cells of Column C, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
Afterward, you will get the cells of the Sales of 2019 column highlighted whose values are Greater than or Equal to the corresponding cells of the cells range of the Sales of 2018.
6. Using Less than Or Equal to Operator for Conditional Formatting Based On Another Cell Range
You can apply Conditional Formatting to the cells of the Sales of 2019 column by using Less than or Equal to Operator.
Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$D5<=$C5When the cells of Column D will be Less than or Equal to the corresponding cells of Column C, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
Then, you will get the cells of the Sales of 2019 column highlighted whose values are Less than or Equal to the corresponding cells of the cells range of the Sales of 2018.
7. Conditional Formatting for Multiple Conditions Using AND Function
While dealing with multiple conditions you can use the AND function, this will make sure that all the conditions are fulfilled.
Let’s say, you want to highlight the cells of the Sales of 2019 column whose values are greater than the corresponding values of the cells of the Sales of 2018 column and the Sales of 2020 column, then you can use the AND function.
Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=AND($D5>$C5,$D5>$E5)When the cells of Column D will be Greater than the corresponding cells of Column C and Column E, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
Then, you will get the cells of the Sales of 2019 column highlighted whose values are Greater than the corresponding cells of the cells range of the Sales of 2018 and Sales of 2020.
8. Conditional Formatting for Multiple Conditions Using OR Function
You can use the OR function as well to deal with multiple conditions, unlike AND this function will trigger the operation if any of the criteria meet.
For highlighting the cells of the Sales of 2019 column whose values are greater than the corresponding values of the cells of the Sales of 2018 column or the Sales of 2020 column, then you can use the OR function.
Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=OR($D5>$C5,$D5>$E5)When the cells of Column D will be Greater than the corresponding cells of Column C or Column E, then the Conditional Formatting will appear in that cell of Column D.
➤Press OK.
Result:
Afterward, you will get the cells of the Sales of 2019 column highlighted whose values are Greater than the corresponding cells of the cells range of the Sales of 2018 or Sales of 2020.
9. Conditional Formatting Based On Another Cell Range for Empty Cells
If you want to highlight the Order Dates corresponding to the Delivery Dates which are empty which means not delivered yet, then you can follow this method.
Step-01:
➤Follow Step-01 of Method-1 (Here, I have changed the Background Color and selected the Order Date column for Conditional Formatting)
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$D5=""When the cells of Column D will be Equal to Blank, the Conditional Formatting will appear to the corresponding cells of Column C.
➤Press OK.
Result:
Then, you will get the cells of the Order Date column highlighted when the corresponding cells of the column Delivery Date will be empty.
Read More: How to Apply Conditional Formatting for Blank Cells in Excel
10. Conditional Formatting Based On Another Cell Range for Non-Empty Cells
For highlighting the Order Dates corresponding to the Delivery Dates which are non-empty, then you can follow this method.
Step-01:
➤Follow Step-01 of Method-1
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$D5<>""When the cells of Column D will be Not Equal to Blank, the Conditional Formatting will appear to the corresponding cells of Column C.
➤Press OK.
Result:
Then, you will get the cells of the Order Date column highlighted when the corresponding cells of the column Delivery Date will be non-empty.
11. Conditional Formatting Based On Another Cell Range for Texts
If you want to highlight the cells of the Sales column for the corresponding cells of the Item column where Jacket2 (or any other item name) presents, then you can follow this method.
Step-01:
➤Follow Step-01 of Method-1 (Here, I have selected the Sales column for Conditional Formatting)
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=$B5="Jacket2"When the cells of Column B will be Equal to “Jacket2”, the Conditional Formatting will appear to the corresponding cells of Column E.
➤Press OK.
Result:
Then, you will get the cells of the Sales column highlighted when the corresponding cells of the column Item will have Jacket2.
Read more: Excel Conditional Formatting with Formula If Cell Contains Text
12. Conditional Formatting Using SEARCH Function for Texts
If you want to highlight the cells of the Sales column for the corresponding cells of the Item column where Jacket (or any other item name) presents, then you can use the SEARCH function.
Step-01:
➤Follow Step-01 of Method-1
After that, you will get the following New Formatting Rule Dialog Box.
➤Type the following formula in the Format values where this formula is true: Box
=SEARCH("Jacket",$B5)>0When the cells of Column B will be Equal to the “Jacket”, the Conditional Formatting will appear to the corresponding cells of Column E.
➤Press OK.
Result:
After that, you will get the cells of the Sales column highlighted when the corresponding cells of the column Item will have an item name starting with Jacket.
Download Workbook
Conclusion
In this article, I tried to cover the easiest ways to do Conditional Formatting based on another cell range in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.
