How to Highlight Duplicates in Two Columns in Excel

Today I’ll show how to highlight duplicates in two columns in Excel with proper examples and illustrations.


Download Practice Workbook


2 Quick Ways to Highlight Duplicates in Two Columns in Excel

1. Highlight Duplicates in Two Columns of the Same Row

Here we’ve got a data set with the Names of some employees, and their Starting and Present salaries of a company called Rennata Group.

Data Set to Highlight Duplicates in Two Columns in Excel

Our objective is to highlight the employees whose salaries didn’t increase, that means the starting salary and the present salary are the same.

Step 1:

Select the whole data set (Without the Column Headers).

Go to the Home>Conditional Formatting tool in the Excel toolbar.

Conditional Formatting Tool in Excel Toolbar

Step 2:

Click on the drop-down menu associated with Conditional Formatting.

From the options available, select New Rule.

Options with Conditional Formatting in Excel

Step 3:

Click on New Rule. You will get a dialogue box called New Formatting Rule.

Select Use a formula to determine which cells to format.

Then in the Format values where this formula is true box, insert this formula:

=$C4=$D4
  • Here $C4 and $D4 are the first cells of my two columns. You use your one. But don’t change the Mixed Cell Reference as presented.

Formula to Highlight Duplicates in Two Rows in Excel

Step 4:

Click on Format.

You will get the Format Cells dialogue box. Choose any format that you like to highlight the cells with duplicate values.

Here I have selected Yellow color under the option Fill. This will fill the cell background in yellow.

Choosing Format to Highlight Cells with Duplicates

Step 5:

Click on OK. You will come back to the New Formatting Rule box.

Again click on OK. You will find the rows that have the same joining and present salaries have been highlighted in yellow.

Highlight Duplicates in Two Columns of the Same Row


2. Highlight Duplicates in Two Columns of Different Rows

Here we’ve got another data set with the Candidates of two weeks for an interview in Rennata Group.

Data Set to Highlight Duplicates in Two Columns in Excel

Today our objective is to find out if any candidate is mistakenly put into both the weeks or not.

We will highlight the names that appear in both weeks.

Step 1:

Select the first column (Except the Column Header)

Then go to the Home > Conditional Formatting tool in Excel Toolbar.

Conditional Formatting Tool in Excel Toolbar

Step 2:

Click on the drop-down menu associated with Conditional Formatting.

Then from the options available, select New Rule.

Options with Conditional Formatting in Excel

Step 3:

Click on New Rule. You will get a dialogue box called New Formatting Rule.

Select Use a formula to determine which cells to format.

Then in the Format values where this formula is true box, insert this formula:

=COUNTIF($C$4:$C$13,B4)>0
  • Here $C$4:$C$13 is my second column. You use your one.
  • But don’t change the Absolute Cell Reference as presented.
  • B4 is the first cell of my first column. You use your one.

Formula to Highlight Duplicates in Two Columns in Excel

Step 4:

Click on Format. You will get the Format Cells dialogue box.

Choose any format that you like to highlight the cells with duplicate values. Here I have selected Yellow color under the option Fill. This will fill the cell background in yellow.

Choosing Format to Highlight Cells with Duplicates

Step 5:

Click on OK. You will come back to the New Formatting Rule box.

Then again click on OK. You will find the names in your first column that have duplicates in the second column highlighted in yellow.

Highlight Duplicates in the First Column of Excel

Step 6:

We have highlighted the names in the first week that appear in the second week.

And to highlight them in the second week also, repeat the same procedure. Select the second column, go to Conditional Formatting, select new Rule.

Options with Conditional Formatting in Excel

Step 7:

Select Use a formula to determine which cells to format.

Then in place of the formula, use this formula:

=COUNTIF($B$4:$B$13,C4)>0

  • Here $B$4:$B$13 is my first column. You use your one.
  • But don’t change the Absolute Cell Reference as presented.
  • C4 is the first cell of my second column. You use your one.

Formula to Highlight Duplicates in Two Columns in Excel

Step 8:

Click on Format. Choose any format you like.

Like the earlier one, I am choosing Yellow color from the Fill Option.

Choosing Format to Highlight Cells with Duplicates

Step 9:

Click on OK twice. You will find that the duplicate names have been highlighted in the second column too.

Highlight Duplicates in Two Columns in Excel


Conclusion

Using this method, you can highlight duplicates in two columns in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Readings:

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo