Conditional Formatting Based On Another Cell Range in Excel

Here’s an overview of using Conditional Formatting based on another cell range.

conditional formatting based on another cell range

We have two data tables. The first table has Sales values for different years for some products and the second table contains Order Date, Delivery Date and Sales for some items of a company.

conditional formatting based on another cell range

Method 1 – Conditional Formatting Based on Another Cell Range with the Equal to Operator

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.

Conditional Formatting Based On Another Cell Range for Equal to Operator

Step 1:

Select the cell range on which you want to apply the Conditional Formatting (We have selected the column Sales of 2019).
Go to the Home tab, select Conditional Formatting, and choose New Rule.

excel conditional formatting based on another cell range

The New Formatting Rule Wizard will appear.

Select Use a formula to determine which cells to format.

using Equal to operator

Click on Format.

using Equal to operator

The Format Cells dialog box will open up.

Select the Fill tab.
Choose any Background Color.
Click on OK.

using Equal to operator

The Preview will be shown.

excel conditional formatting based on another cell range

Step 2:

Use the following formula in the Format values where this formula is true box:

=$D5=$C5

When the cells of Column D are 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:

Conditional Formatting Based On Another Cell Range for Equal to Operator

Method 2 – Conditional Formatting Based on Another Cell Range for Not Equal to

You can apply Conditional Formatting to the cells of the Sales of 2019 column by using Not Equal to Operator.

Conditional Formatting Based On Another Cell Range for Not Equal to Operator

Steps:

Follow Step 1 of Method 1.
You will get the following New Formatting Rule dialog box.

using Not Equal to operator

Use the following formula in the Format values where this formula is true box.

=$D5<>$C5

When the cells of Column D are 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:

Conditional Formatting Based On Another Cell Range for Not Equal to Operator

Method 3 – Conditional Formatting Based on Another Cell Range with a Greater than Operator

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.

Conditional Formatting Based On Another Cell Range for Greater than Operator

Step 1:

Follow Step 1 of Method 1. You will get the following New Formatting Rule dialog box.

using Greater than operator

Use the following formula in the Format values where this formula is true box:

=$D5>$C5

When the cells of Column D are 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:

Conditional Formatting Based On Another Cell Range for Greater than Operator

Method 4 – Using the 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.

Using Less than Operator for Conditional Formatting Based On Another Cell Range

Step 1:

Follow Step 1 of Method 1. You will get the following New Formatting Rule dialog box.

using Less than operator

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

=$D5<$C5

When the cells of Column D have a value 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:

Using Less than Operator for Conditional Formatting Based On Another Cell Range

Method 5 – Conditional Formatting Based on Another Cell Range with the Greater than Or Equal to Operator

We will highlight the cells of the Sales of 2019 column which are Greater than or Equal to the corresponding cell ranges of the Sales of 2018 column.

Conditional Formatting Based On Another Cell Range for Greater than Or Equal to Operator

Step 1:

Follow Step 1 of Method 1. You will get the following New Formatting Rule Dialog Box.

using Greater than or Equal to operator

Use the following formula in the Format values where this formula is true box.

=$D5>=$C5

When the cells of Column D are 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:

Conditional Formatting Based On Another Cell Range for Greater than Or Equal to Operator

Method 6 – Using the 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.

Using Less than Or Equal to Operator for Conditional Formatting Based On Another Cell Range

Step 1:

Follow Step 1 of Method 1. You will get the following New Formatting Rule Dialog Box.

using Less than or Equal to operator

Use the following formula in the Format values where this formula is true box:

=$D5<=$C5

When the cells of Column D are 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:

Using Less than Or Equal to Operator for Conditional Formatting Based On Another Cell Range

Method 7 – Conditional Formatting for Multiple Conditions Using the AND Function

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

Conditional Formatting for Multiple Conditions Using AND Function

Steps:

Follow Step 1 of Method 1. You will get the following New Formatting Rule Dialog Box.

using AND function

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

=AND($D5>$C5,$D5>$E5)

When the cells of Column D are 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:

Conditional Formatting for Multiple Conditions Using AND Function

Method 8 – Conditional Formatting for Multiple Conditions Using the OR Function

We’ll 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 or the Sales of 2020 column.

Conditional Formatting for Multiple Conditions Using OR Function

Steps:

Follow Step 1 of Method 1. You will get the following New Formatting Rule Dialog Box.

using OR function

Use the following formula in the Format values where this formula is true box:

=OR($D5>$C5,$D5>$E5)

When the cells of Column D are Greater than the corresponding cells of Column C or Column E, the Conditional Formatting will appear in that cell of Column D.

Press OK.

using OR function

Result:

Conditional Formatting for Multiple Conditions Using OR Function

Method 9 – Conditional Formatting Based on Another Cell Range for Empty Cells

We want to highlight the Order Dates corresponding to the Delivery Dates which are empty.

Conditional Formatting Based On Another Cell Range for Empty Cells

Step 1:

Follow Step 1 of Method 1. You will get the following New Formatting Rule Dialog Box (We have changed the Background Color and selected the Order Date column for Conditional Formatting).

for empty cells

Use the following formula in the Format values where this formula is true box:

=$D5=""

When the cells of Column D are Equal to Blank, the Conditional Formatting will appear to the corresponding cells of Column C.

Press OK.

for empty cells

Result:

Conditional Formatting Based On Another Cell Range for Empty Cells

Method 10 – Conditional Formatting Based On Another Cell Range for Non-Empty Cells

We’ll highlight the Order Dates corresponding to the Delivery Dates which are non-empty.

Conditional Formatting Based On Another Cell Range for Non-Empty Cells

Steps:

Follow Step 1 of Method 1. You will get the following New Formatting Rule Dialog Box.

for non-empty cells

Use the following formula in the Format values where this formula is true box:

=$D5<>""

When the cells of Column D are Not Equal to Blank, the Conditional Formatting will appear to the corresponding cells of Column C.

Press OK.

for non-empty cells

Result:

Conditional Formatting Based On Another Cell Range for Non-Empty Cells

Method 11 – Conditional Formatting Based on Another Cell Range for Texts

We’ll highlight the cells of the Sales column for Jacket2 (or any other item name) in the Item column.

Conditional Formatting Based On Another Cell Range for Texts

Steps:

Follow Step 1 of Method 1 (We have selected the Sales column for Conditional Formatting).
You will get the following New Formatting Rule Dialog Box.

for texts

Use the following formula in the Format values where this formula is true box:

=$B5="Jacket2"

When the cells of Column B are Equal to “Jacket2”, the Conditional Formatting will appear to the corresponding cells of Column E.

Press OK.

for texts

Result:

Conditional Formatting Based On Another Cell Range for Texts

Method 12 – Conditional Formatting Using the SEARCH Function for Texts

We’ll use a partial match Jacket in the Item column to highlight the corresponding cells in the Sales column.

Conditional Formatting Using SEARCH Function for Texts

Steps:

Follow Step 1 of Method 1. You will get the following New Formatting Rule Dialog Box.

using SEARCH function

Use the following formula in the Format values where this formula is true box:

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

When the cells of Column B contain “Jacket”, the Conditional Formatting will appear to the corresponding cells of Column E.

Press OK.

using SEARCH function

Result:

Conditional Formatting Using SEARCH Function for Texts

Download the Workbook

Conditional Formatting Based On Another Cell Range.xlsx

Conditional Formatting Based On Another Cell Range in Excel

Method 1 – Conditional Formatting Based on Another Cell Range with the Equal to Operator

Method 2 – Conditional Formatting Based on Another Cell Range for Not Equal to

Method 3 – Conditional Formatting Based on Another Cell Range with a Greater than Operator

Method 4 – Using the Less than Operator for Conditional Formatting Based on Another Cell Range

Method 5 – Conditional Formatting Based on Another Cell Range with the Greater than Or Equal to Operator

Method 6 – Using the Less than Or Equal to Operator for Conditional Formatting Based on Another Cell Range

Method 7 – Conditional Formatting for Multiple Conditions Using the AND Function

Method 8 – Conditional Formatting for Multiple Conditions Using the OR Function

Method 9 – Conditional Formatting Based on Another Cell Range for Empty Cells

Method 10 – Conditional Formatting Based On Another Cell Range for Non-Empty Cells

Method 11 – Conditional Formatting Based on Another Cell Range for Texts

Method 12 – Conditional Formatting Using the SEARCH Function for Texts

Further Readings

Leave a reply Cancel reply