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.
Select the range of the whole column that has student names.
Click the Home tab on the ribbon. >> Click the Conditional Formatting icon
Click On New Rule Option
In the New Formatting Rule dialog box, select Use formula to determine which cells to format.
Enter the following formula in the field: =$B2<35
Click on the Format button (to specify the formatting in which you want the names to be highlighted)
Select the formatting color.
After implementing the above steps would apply color to those Student names that have scored below 35.
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.