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 as the name itself suggests formatting is based on the conditions specified by the user.
Usually in excel when we are working we need to highlight some of the rows based on the value in the other cell. Highlighting certain rows based on a certain value manually will take a lot of time.
In excel we have a built-in tool called Excel Conditional Formatting in Pivot TableConditional Formatting which can highlight the row based on the specific criteria.
How to Apply Excel Conditional Formatting Based on Another Cell Value?
To apply conditional formatting in excel based on single and another cell value is very simple and easy to use. Let’s understand it by 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, I will explain one of the techniques.
Example #1
I have simple data of employees. This data includes Active and Left employees list.
Now I want to highlight all the employees who are left. Follow below steps to learn this technique.
Method #1 – Highlight Single Cell Value
Under this method, I 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. I 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, I 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 the new window for you. In this select Use a formula to determine which cells to format.
Step 4: Next under formula bar mention the formula as shown in the below image and then click on Format.
The formula read 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 absolute reference) then do the formatting.
Step 5: Select the formatting color by clicking on the Fill option and then click OK.
Step 6: Again click on OK to format the rows if the cell value is equal to the text Left.
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. In this example, I will explain to you how to highlight rows based on multiple values.
I have employee’s database with name, salary and their respective department. Here I want to highlight the departments of Marketing and IT.
In order to do this task, we need to apply the formula in the conditional formatting tab.
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 formula bar mention the formula as shown in the below image and then click on Format.
OR function tests whether the values in column C is 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 wanted to highlight. I have selected 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 the department is equal to either Marketing or IT.
Example #3 – Excel Conditional Formatting Based on Another Cell Value
Consider the same 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 formula bar mention the formula as shown in the below image and then click on Format.
Step 5: Select the color you wanted to highlight. I have selected below color as shown in the below image.
AND is a logical function which tests if the column C values are equal to the Marketing department and tests column B values are greater than 50000. If both the conditions are true then 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 we can directly apply the formula 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 how to apply conditional formatting in excel based on single and another cell value along with practical examples and downloadable excel template. You can also go through our other suggested articles –
