How to Do Conditional Formatting Based On Another Cell Range in Excel

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 Excel. This article will help you to know the ways of doing Conditional Formatting based on another cell range.

Download Workbook

12 Ways to Do Conditional Formatting Based On Another Cell Range in Excel

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.

excel conditional formatting based on another cell range

excel conditional formatting based on another cell range

 

Method-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.

excel conditional formatting based on another cell range

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.

excel conditional formatting based on another cell range

Then the New Formatting Rule Wizard will appear.
➤Select Use a formula to determine which cells to format option.

using Equal to operator

➤Click on Format Option.

using Equal to operator

After that, the Format Cells Dialog Box will open up.
➤Select Fill Option
➤Choose any Background Color
➤Click on OK.

using Equal to operator

After that, the Preview Option will be shown as below.

excel conditional formatting based on another cell range

Step-02:
➤Write the following formula in the Format values where this formula is true: Box

=$D5=$C5

When 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.

using Equal to operator

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.

excel conditional formatting based on another cell range

Read more: Conditional Formatting Based On Another Cell in Excel

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.

using Not Equal to operator

➤Type the following formula in the Format values where this formula is true: Box

=$D5<>$C5

When 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.

using Not Equal to operator

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.

excel conditional formatting based on another cell range

Read more: Excel Conditional Formatting Based on Multiple Values of Another Cell

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1.
Then, you will get the following New Formatting Rule Dialog Box.

using Greater than operator

➤Type the following formula in the Format values where this formula is true: Box

=$D5>$C5

When 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.

using Greater than operator

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.

excel conditional formatting based on another cell range

Read more: Excel Highlight Cell If Value Greater Than Another Cell

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.

using Less than operator

➤Type the following formula in the Format values where this formula is true: Box

=$D5<$C5

When 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.

using Less than operator

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.

excel conditional formatting based on another cell range

Read more: How to Compare Two Columns Using Conditional Formatting in Excel

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1.
Then, you will get the following New Formatting Rule Dialog Box.

using Greater than or Equal to operator

➤Type the following formula in the Format values where this formula is true: Box

=$D5>=$C5

When 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.

using Greater than or Equal to operator

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.

excel conditional formatting based on another cell range

 

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.

using Less than or Equal to operator

➤Type the following formula in the Format values where this formula is true: Box

=$D5<=$C5

When 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.

using Less than or Equal to operator

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.

excel conditional formatting based on another cell range


Similar Readings:


Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.

using AND function

➤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.

using AND function

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.

using AND function

 

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1.
After that, you will get the following New Formatting Rule Dialog Box.

using OR function

➤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.

using OR function

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.

excel conditional formatting based on another cell range

 

Method-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.

excel conditional formatting based on another cell range

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.

for empty cells

➤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.

for empty cells

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.

excel conditional formatting based on another cell range

Read more: Conditional Formatting for Blank Cells in Excel

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1
After that, you will get the following New Formatting Rule Dialog Box.

for non-empty cells

➤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.

for non-empty cells

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.

excel conditional formatting based on another cell range

 

Method-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.

excel conditional formatting based on another cell range

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.

for texts

➤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.

for texts

Result:
Then, you will get the cells of the Sales column highlighted when the corresponding cells of the column Item will have Jacket2.

for texts

Read more: Excel Conditional Formatting with Formula If Cell Contains Text

Method-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.

excel conditional formatting based on another cell range

Step-01:
➤Follow Step-01 of Method-1
After that, you will get the following New Formatting Rule Dialog Box.

using SEARCH function

➤Type the following formula in the Format values where this formula is true: Box

=SEARCH("Jacket",$B5)>0

When 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.

using SEARCH function

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.

excel conditional formatting based on another cell range

 

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice

 

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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo