How to Compare Rows in Excel for Duplicates

Highlighting Duplicate Values in the Same Row

Today I will be showing how you can compare rows in Excel for finding duplicates.


Download Practice Workbook


How to Compare Rows in Excel for Duplicates

Let us first have a look at this data set. We have the employee records of a company named Jupyter Group.

A data set in Excel

We have the First Names of the employees, the Last Names, their Starting Salaries and Present salaries in columns B, C, D, and E respectively.

Now we will try to compare duplicate data in various rows and columns.


Case 1: Duplicates in the Same Row but Different Columns

First of all, let us try to compare the duplicate data in the same row, but in different columns.

Read more: Find Duplicates in Two Columns in Excel

Let us try to find out which rows in columns D and E have the same data.

That means, which employees have the same starting salaries and present salaries.

We can do it in two ways.

  • By examining each row and then deciding whether the two salaries are the same or not in a new column.
  • By highlighting the rows with the same salaries.

Option 1: Deciding in a New Column

First of all, let’s try to decide on a new column.

To accomplish that, take a new column and write this formula in the first cell of the column. I take a new column F, name it as Decision Column and write this formula in the cell F4:

=IF(D4=E4,"Same","Not Same")

Formula for matching duplicates in the same row

Then drag the Fill Handle to decide for the rest of the rows.

Dragging a formula through Fill Handle in Excel

See, you have identified pretty conveniently which rows have the same two salaries and which rows have not.

Note: Obviously, you can use anything other than “Same” and “Not Same” in the formula. Like “True” and “False”, “Match” and “Not Match” etc.


Option 2: Highlighting the Particular Rows

  • First of all select the column you want to compare. I select columns D and E.

Selecting two columns in Excel

  • Then go to Home>Conditional Formatting>New Rule in the Excel Toolbar.

Conditional Formatting options in Excel

  • You will get the New Formatting Rule dialogue box. Select Use a formula to determine which cells to format. Then in the formula box, write this formula:
=$D4=$E4

Then click on Format.

New Formatting Rule dialogue box in Excel

  • You will get the Format Cells Box. Choose any format you like. I chose the color Red from the Fill menu.

Format Cells Dialogue box in Excel

  • Then click OK. You will find the cells where the salaries are the same highlighted in red.

Format Cells Dialogue box in Excel


Case 2: Duplicates in Different Rows and Different Columns

Now consider a different scenario. Jupyter group has scheduled an interview session for some candidates.

A new data set in Excel

Now we will try to find out whether any candidate called on the first week has been mistakenly called on the second week too.


Option 1: Deciding in a New Column

Like section 1.1, take a new column. I take a new column C in between the two columns

Then insert this formula in the first cell of the new column:

=IF(COUNTIF($D$4:$D$13,B4)>0,"Match","Don't Match")

Formula to find duplicates in different columns in Excel

Then drag the Fill Handle to execute it for the rest of the candidates.

Dragging Fill Handle in Excel

If you want, instead of deciding whether it matches or not, you can directly determine how many times each name of the first week appears in the second week.

The formula will be:

=COUNTIF($D$4:$D$20,B4)

Formula to find the number of duplicates

See, the formula determines how many times each name of the first week appears in the second week.


Option 2: Highlighting the Cells with Duplicates

Like section 1.2, select column B (Only column B, not B and C), then go to Home>Conditional Formatting>New Rule option in the Excel Toolbar.

In the New Formatting Rule box, choose to Use a formula to determine which cells to format.

Read more: How to Find, Highlight & Remove Duplicates in Excel

Then in the formula box, write this formula:

=COUNTIF($C$4:$C$20,B4)>0

A formula in Conditional Formatting

Then click on Format and select your desired format.

And then click OK.

You will get the names in column B also appeared in column C highlighted like this.

Names that apear in another column are highlighted


Case 3: Duplicates in the Same Column

Now we come back to our original data set, the Employee Record of Jupyter Group.

This time we focus on finding out the duplicates in the same column.

For example, first, we try to sort out which names have duplicates in the First Name column.


Option 1: Deciding in a New Column

Take a new column and insert this formula in the first cell of the column:

=IF(COUNTIF(B$4:B$20,B4)>1,"Have Duplicates","Don't Have Duplicates")

Formula to find duplicates in the same column

Then drag the Fill Handle to decide for the rest of the names.

Dragging the Fill Handle in Excel

Now if you wish, you can drag the Fill Handle row-wise to decide for the Last Names too.

Dragging the Fill Handle row wise in Excel

And again, if you want, you can count how many duplicates each name has in the columns rather than taking only a decision.

To execute that, enter this formula in cell F4 and then drag the Fill Handle through the rows and columns.

Fomula to count duplicate values in Excel

See, we have got the number of duplicates of both the first names and the last names.


Option 2: Highlighting the Cells with Duplicates

First select columns B and C.

Then like section 1.1, go to Home>Conditional Formatting>New Rule.

And then in the New Formatting Rule box, select Use a formula to determine which cells to format.

Then in the formula box, write this formula:

=COUNTIF(B$4:B$20,B4)>1

A formula in the Conditional Formatting box in Excel

Then click on Format. Choose your desired format.

After that, click OK.

You will find the duplicate values in each column have been highlighted like this.

Duplicate values in each column highlighted


Case 4. Duplicates in the Whole Dataset

Finally, if you wish you can highlight the duplicate values in the whale data set. This is pretty simple.

First, select the whole data set.

Then go to Home>Conditional Formatting>Highlight Cells Rules>Duplicate Values.

Conditional Formatting options in Excel

Select Duplicate Values. You will get the Duplicate Values dialogue box.

Then select Duplicate. And then select any format you like. I am selecting Red Text.

Duplicate Values Dialogue Box in Excel

You will get the duplicate values from the whole data set highlighted like this.

Duplicate values highlighted in Excel


Conclusion

Using these methods, you can use any value in a set of data to search for duplicates in both rows and columns. Do you know any other method? Let us know in the comment section.


Further 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.

3 Comments
  1. Yoyo. Good stuff.

  2. How do I get the largest number in the first column of Excel?

Leave a reply

ExcelDemy
Logo