Conditional Formatting in Excel – One column based on another

Conditional Formatting in Excel

Conditional Formatting in Excel

Match1, Match2
A, B
D, A
E, E
F, D
K, K

Rule:	COUNTIF($B$2:$B$6,C2)

Read: If cell c2 value exists in the range B2:B6, then highlight the cells that I have selected in step 1.

The above will highlight All rows in column Match2 that exists in column Match1. In this case, A,E,D,K will be highlighted but B will not because it does not exist in Match1 column.


1. Select cells range that you want to conditionally highlight, in this case B2:B7
2. Select Conditional Formatting, New Rule then select Formula
3. Enter the formula, =COUNTIF($B$2:$B$6,C2) (remember to put equal in formula)
4. Click format button and choose whatever formatting you choose.
5. Select ok when done. Your cells will be highlighted.

Points to be aware of

When enter cell C2, make sure you type it as it is. Do not select cell C2 with the mouse. If you do, that will hard code that particular cell and will not work with all the cells.


If you want to highlight the entire row in your selection, then simply prefix the cell you want to highlight by $. In the example you have two columns of data selected, instead of saying A2 for example you would enter $A2. This would be the second argument for the countif function.

This entry was posted in Tutorials/Tips and tagged , . Bookmark the permalink.

1 Response to Conditional Formatting in Excel – One column based on another

  1. Dave says:

    Another way to look at it, the range come first in COUNTIF and then the smaller comes next


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s