How To Conditional Formatting based on another column

Conditional Formatting In Excel

Conditional formatting lets you format cells based on the values ​​in the cell. In most cases, you apply conditional formatting to the same cell where you are analyzing the value.

In some cases, you may want to apply conditional formatting to a cell or column based on the value in another column.

If you want to apply conditional formatting based on the values in another column, you can create a rule based on a simple formula. 

I will show you how to apply conditional formatting thru below example.

Apply Conditional Formatting Based on Another Column

Below is a set of data showing the names of the students and their grades in two separate columns and I want to highlight the names of the students who scored less than 40.

Conditional Formatting

Select the range of the whole column that has student names.

Select Names Column

Click the Home tab on the ribbon. >> Click the Conditional Formatting icon

Home Tab > Conditional Formatting

Click On New Rule Option

Click On New Rule

In the New Formatting Rule dialog box, select Use formula to determine which cells to format.

Select Option

Enter the following formula in the field: =$B2<35

Conditional Formatting in Excel

Click on the Format button (to specify the formatting in which you want the names to be highlighted)

Select the formatting color.

Excel Conditional Formatting

Click Ok.

After implementing the above steps would apply color to those Student names that have scored below 35.

Conditional Formatting

Because conditional formatting is dynamic, if you change the score in column B, the conditional formatting rule will be checked again, and if the score is less than 35, this name will be highlighted in your specified color.

Each cell in this order is checked against the above formula, and if it returns TRUE, then the cell named is highlighted in a specific format, and if it returns FALSE, no format is available.

So I hope we have covered everything about conditional formatting in this article and you also found this article useful. If so please don’t forget to drop a comment below and share this article with your friends.


Abhijit currently working with India's No. 1 Ecommerce Company Flipkart. He having 6 years experience in Excel and he is also expertise in various Advance Excel formulas. He awarded by his organization for his outstanding contribution in Analytical Project.