But in some cases, you may want to apply conditional formatting to a cell or column based on values in another column. A simple example of this could be when I have the names of students and their scores in two separate columns, and I want to highlight those names where the students have scored less than 35. In this tutorial, I will show you to apply conditional formatting to one column based on the values in another column or cell.
Apply Conditional Formatting Based on Another Column
Below I have a data set with student names and their scores in two separate columns, and I want to highlight the names of those students that have scored less than 35. Here are the steps to do this: The above steps would apply color to those names that have scored less than 35. Since 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 then that name would get highlighted in yellow color. How does this work? We have used a formula (=$B2<35) in Conditional Formatting, which is checked for each of the cells in the selected range. In our example, we selected the range A2:A14. Every cell in this range is checked for the above formula, and if returns TRUE, then the cell with the name gets highlighted in the specified format (yellow color), and if it returns FALSE, no formatting is applied.
For the first cell, formula would be =$B2<35For the second cell, formula would be =$B3<35For the third cell, formula would be =$B4<35
You get the idea!
Apply Conditional Formatting to Entire Column Based on Value in a Cell
Another example where you can use the same concept is when you want to highlight all the cells in a range based on the value in one specific cell. Below I have a data set where I have the names of the Sales Rep in column A, their sales values in column B, and the sales target in cell D2. For this to work, I will have to compare the sale value with the sales target, and if the sale value is higher than the Sales target value, then I want to highlight the name of the Sales Rep. Below are the steps to do this: The above steps will compare the sales figures for all the Sales Rep with the value in cell D2, and if the sale value is higher than the value in cell D2, it will highlight the names. In case you change the value in cell D2, Conditional Formatting will update and highlight names where the sales value is more than the specified value in cell D2. Understanding the Formula In the above formula, I have compared all the sales values in column B with values in one single cell (D2). For this, I have used $D$2 in the formula. When you add a dollar sign before the column alphabet or the row number, it makes sure that when the formula is copied to other cells, this value does not change. For example, when the formula in Conditional Formatting compares the value in cell B2, the formula it assesses is =$B2>=$D$2 For B3, the formula becomes =$B3>=$D$2 For B4, it becomes =$B4>=$D$2 This way, all the sales values in column B are compared with the same value in cell D2. So this is how you can use a simple formula to apply conditional formatting based on another column or another cell value in Excel. I hope you found this tutorial useful. Other Excel tutorials you may also like:
How to Select Entire Column (or Row) in Excel – ShortcutHow to Apply Formula to Entire Column in ExcelHow to Copy Conditional Formatting to Another Cell in ExcelHighlight Rows Based on a Cell Value in Excel (Conditional Formatting)How to Apply Conditional Formatting in a Pivot Table in ExcelHighlight EVERY Other ROW in Excel (using Conditional Formatting)Excel Quick Analysis Tool – How to Best Use it?
