Highlight Cell If Value Exists in Another Column in Excel

One of the best things about Microsoft Excel is that it can be customized to suit your needs and preferences.

Excel has Conditional Formatting that allows you to format a cell based on the value in it. But with a little bit of formula magic, you can also highlight a cell or range of cells based on whether a value exists in some other columns or not.

For example, if you have a dataset with a person’s name and their country, and you want to highlight all the names that are from the US, you can easily do that using conditional formatting.

In this tutorial, I will show you how to highlight a cell or range of cells if the value exists in another column in Excel.

So, let’s get started!

Highlight Cells Based on Value in Another Column

Suppose you have a data set as shown below and you want to highlight all the names in column A if the country in column B is ‘US’.

Dataset

Below are the steps to do this using Conditional Formatting:

  1. Select the column in which you want to highlight the cells (the Names column in our example)
  2. Click the Home tabClick the Home tab
  3. In the Styles group, click on Conditional FormattingClick on Conditional Formatting
  4. In the options that show up, click on the New Rule option. This will open the New Formatting Rules dialog boxClick on New Rule
  5. In the ‘Select a Rule type’ options, click on ‘Use a formula to determine which cells to format’Select Use a formula to determine which cells to format
  6. Enter the following formula in the field below: =B2=”US”Enter the formula in the field
  7. Click on the Format buttonClick on the Format button
  8. In the Fill tab, choose the color in which you want to highlight the cellsSelect the color with which you want to fill the cell
  9. Click OK
  10. Click OK

The above steps would highlight only those names where the country in the adjacent column is the US.

Final result where cells have been highlighted

Note that in this example, I have hard-coded the country name in the formula (=B2=”US”). in case you have the country name in a cell, you can also use the reference of the cell instead of using the exact name.

How Does this Work?

When you select a range of cells and apply conditional formatting on it, Excel would go through each of the cells in the selected range and check it using the condition that you have specified (which in our case was the formula =B2=”US”)

When it analyzes cell A2, it checks whether the formula would return True or not. So, it would check cell B2 and see if the value is equal to the US or not.

If the formula returns True, then the cell in column a would get highlighted with the specified format. And if the formula turns FALSE, then nothing would happen.

Similarly, it keeps analyzing all the cells in the Names column and highlights all those where the country name in column B is the US.

Also read: Highlight Cells with Formulas in Excel

Highlight Entire Rows Based on Value in One Column

Another scenario where you can use the same concept is when you want to highlight the entire row based on the value in any one column.

For example, suppose you have a data set as shown below and you want to highlight all the records where the country is ‘US’

Dataset

We can use a similar concept as we used in the example above, but instead of applying conditional formatting to one column, we’ll have to apply it to the entire data set.

Below are the steps to do this:

  1. Select the entire dataset
  2. Click the Home tab
  3. In the Styles group, click on Conditional Formatting
  4. In the options that show up, click on the New Rule option. This will open the New Formatting Rules dialog box
  5. In the ‘Select a Rule type’ options, click on ‘Use a formula to determine which cells to format’
  6. Enter the following formula in the field below: =$B2=”US”
  7. Click on the Format button
  8. In the Fill tab, choose the color in which you want to highlight the cells
  9. Click OK
  10. Click OK

The above steps would highlight all those records where the country in column B is ‘US’

Entire record is highlighted

How Does this Work?

When you select a range of cells (including multiple rows and columns), conditional formatting would go through each cell and check the result of the formula that you have specified.

If the result is TRUE, it would highlight the cell, and if the result is FALSE then it would do nothing.

In our example, I would like to highlight all the cells in a row where the country in column B is the US.

This means that no matter what cell is being analyzed in a row, the formula that needs to be analyzed should always check whether the country is the US or not.

And this is done using the below formula:

=$B2="US"

By adding a dollar sign before the column letter B, I have made this a mixed reference. this means that no matter which sale is being analyzed in the row, conditional formatting would always check the cell value in column B and see if that is equal to the US or not.

So when cell A2 is analyzed by conditional formatting, it checks whether the value in B2 is the US or not. and then cell B2 is analyzed, it does the same.

And now when cell A3 is analyzed by conditional formatting, it would check the country name in cell B3, and so on.

This is how you can highlight cells in Excel based on whether the value exists in another cell or not. and you can also highlight an entire record based on the value in one specific column.

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment