Excel Conditional Formatting Based on Another Cell Value

Table of Contents

• Conditional Formatting Based On Another Cell
• How to Apply Excel Conditional Formatting Based On Another Cell Value?

Conditional Formatting Based On Another Cell

Conditional Formatting in excel can be used in various ways. To use conditional formatting based on another cell, select the New Rule option from the Home menu’s Conditional Formatting dropdown. Then select the last option, “Use a formula to determine which cells to format,” from the list. This will allow the user to select the cells whose value we want to relate to creating formatting. Then select the cell which we want to format from Edit Rule Description. We can also change the font and color as well.

How to Apply Excel Conditional Formatting Based On Another Cell Value?

To apply conditional formatting in excel based on single and other cell values is very simple and easy to use. Let’s understand it with some different examples.

Steps To Highlight Row Based On Single Cell Value

Conditional formatting is available under the Home tab.

 

Under conditional formatting, we have many features available. In this article, we will explain one of the techniques.

Example #1

We have simple data on employees. This data includes the Active and Left employees list.

Now we want to highlight all the employees who are left. Follow the below steps to learn this technique.

Method #1 – Highlight Single Cell Value

Under this method, we will show you how to highlight only the single cell value if the cell has the text Left.

Step 1: Select the entire data.

Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Text That Contains.

Step 3: Once you click on that option, it will open a new window for you. In this window, mention the text value that you want to highlight. We have mentioned the text as Left and chosen the formatting as Light Red Fill with Dark Red Text. Click on the OK button to complete the task.

Step 4: You can already see the preview of this task on the left-hand side of your window. So the output will be as below:

Method #2 – Highlight Entire Row Based on One Single Cell Value

In the previous example, we have seen how to highlight a single cell based on the cell value. Under this method, we will show you how to highlight an entire row based on the single-cell value.

Step 1: Mention the text Left in cell C1.

Step 2: Select the entire data. Go to Home > Conditional Formatting > New Rule.

Step 3: Once you click on that option, it will open a new window for you. This select Use a formula to determine which cells to format.

Step 4: Next, under the formula bar, mention the formula shown in the image below and then click on Format.

The formula reads like this – If the B2 cell is (this is not an absolute reference, but the only column is locked) equal to the value in the C1 cell (this is an absolute reference), then do the formatting.

Step 5: Select the formatting color by clicking on the Fill option and clicking OK.

Step 6: Again, click OK to format the rows if the cell value is equal to the left text.

So the output will be as below:

Now try changing the cell value in C1 from Left to Active. It will highlight all the Active row employees.

Example #2 – Excel Conditional Formatting Based On Another Cell Value

In our previous examples, we have learned how to highlight based on the single-cell value. This example will explain how to highlight rows based on multiple values.

We have an employee database with names, salaries, and respective departments. Here we want to highlight the departments of Marketing and IT.

We need to apply the formula in the conditional formatting tab to do this task.

Step 1: Select the entire data.

Step 2: Click on Conditional formatting and New Rule.

Step 3: Under this tab, select Use a formula to determine which cells to format.

Step 4: Next, under the formula bar, mention the formula shown in the image below and then click on Format.

OR function tests whether the values in column C are equal to Marketing or IT. If anyone of the value is found true, it will highlight the row for you.

Step 5: Click on Format and select the color you want to highlight. We have chosen the below color, as shown in the image below.

Step 6: Click on OK to complete the task.

Conditional Formatting has highlighted the departments if they are equal to either Marketing or IT.

Example #3 – Excel Conditional Formatting Based On Another Cell Value

Consider the exact data for this example as well. Here I want to highlight all the Marketing department if the salary is more than 50,000.

Step 1: Select the entire data.

Step 2: Click on Conditional formatting and New Rule.

Step 3: Under this tab, select Use a formula to determine which cells to format.

Step 4: Next, under the formula bar, mention the formula shown in the image below and then click on Format.

Step 5: Select the color you want to highlight. We have selected the below color as shown in the below image.

AND is a logical function that tests if the column C values are equal to the Marketing department and tests column B values are greater than 50000. If both conditions are true, it will highlight the row for us.

Step 6: Click on OK.

It will highlight all the rows if the department is equal to Marketing and the salary is more than 50000.

Look at the above image yellow marked rows department is also Marketing, but since the salary is less than 50000 AND function excludes from the formatting.

Things to Remember about Excel Conditional Formatting Based On Another Cell Value

• There are many options available under conditional formatting.
• We cannot only highlight the cells, but we can also give data bars.
• We can either enter our criteria in one cell or directly apply them to the formatting itself.
• We can also clear the rules once the formatting is applied.

Recommended Articles

This has been a guide to Conditional Formatting Based On Another Cell. Here we discuss applying conditional formatting in excel based on single and other cell values, along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

1. Excel Data Formatting
2. Excel Data Validation
3. AND Function in Excel
4. OR Excel Function