Apply Conditional Formatting Based on Another Column in Excel

Conditional formatting allows you to apply a format to a cell based on the value in it. in most cases, you will apply conditional formatting to the same cell for which you are analyzing the value.

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.

Data with Names highlighted using conditional formatting

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.

Score and Name Dataset

Here are the steps to do this:

  1. Select the range that has the names
Select the names you want to highlight
  1. Click the Home tab in the ribbon
Click the Home tab
  1. Click on the Conditional Formatting icon (in the Styles group)
Click on Conditional formatting
  1. Click on ‘New Rule’ option
Click on New Rule option
  1. In the New Formatting Rule dialog box, select – “Use a formula to determine which cells to format” option
Select Use a Formula to determine which cells to format
  1. Enter the following formula in the field: =$B2<35
Enter the formula based on which to highlight names
  1. Click on the Format button (to specify the formatting in which you want the names to be highlighted)
  2. Select the formatting (I will go with yellow color)
Select the color to format
  1. Click OK

The above steps would apply color to those names that have scored less than 35.

Names are highlighted based on score

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<35
  • For the second cell, formula would be =$B3<35
  • For 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.

Sales Data with Target in a cell

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:

  1. Select the cells that have the names
  2. Click the Home tab
  3. Click on the Conditional Formatting icon
  4. Click on New Rule option
Click on New Rule option
  1. In the New Formatting Rule dialog box, select – “Use a formula to determine which cells to format” option
  2. Enter the following formula in the field: =$B2>=$D$2
Enter formula in Conditional Formatting Rule dialog box
  1. Click on the Format button
  2. Specify the formatting in which you want to highlight the names that have more than $25,000 commission
  3. Click OK

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.

Names with less than value in a cell are highlighted

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster