How to Compare Rows in Excel for Duplicates (8 Ways)

You may think that what can be the use of comparing rows in Excel. Well, actually there are countless applications if you know the best techniques for finding duplicates. I will show you seven nifty methods to compare two columns or entire rows for finding duplicate values in Microsoft Excel.

Quickest Way to Compare Rows in Excel for Duplicates

To quickly compare rows in Excel for duplicates,

🔗How to Find Duplicates in Two Columns in Excel (7 Methods)

Alternative Way #1: Show Result in a New Column to Compare Rows in Excel for Duplicates

I will use an additional column adjacent to the main data table to show results as ‘Same’ (if the cell contents within a single row are similar) or ‘Unique’ (if the cell contents of the same row are different).

The above dataset contains the employee salaries for the month of January and June. Now to find the duplicates in this table,

❶ Create a new column right next to the existing columns.

I have named the new column ‘Result’.

❷ Then write the formula in the top cell (C2) of that column:

=IF(A2=B2,"Same","Unique")

Formula Breakdown

• A2=B2 is a logical test. You have to modify the cell addresses while you apply this formula in your Excel sheets.
• If the logical test becomes true, the result will be ‘Same’,
• If the logical test becomes false, the result will be ‘Unique’.

❸ Press ENTER to see the result.

❹ Now put the mouse cursor on the Fill Handle icon and drag it to the end of the column (C2-C15).

As a substitute for the words ‘Same’ and ‘Unique’, you can use other words also. For example ‘Match and Mismatch’, ’Duplicate and Different’, ‘Positive and Negative’ etc.

🔗 How to Find Similar Text in Two Columns in Excel (5 Ways)

Alternative Way #2: Use New Rule Dialog Box to Compare Rows in Excel for Duplicates

Among all other techniques, highlighting the rows is my personal favorite technique for finding duplicates. Because it makes the results very easy to find. You can also save a lot of time if your worksheet is very long.

To highlight the duplicate cells in the same row, perform the following steps:

❶ Select your target columns first.

❷ Then go to the Home tab from the main ribbon.

❸ From the Styles group, click on the  Conditional Formatting drop-down menu.

❹ Now select ‘New Rules’ from the Conditional Formatting drop-down list.

A dialog box named New Formatting Rule will appear.

❺ From the dialog box, choose to Use a Formula to Determine Which Cells to Format.

❻ Then copy this formula inside the Format Values Where This Formula Is True bar:

=$D2=$E2

❼ Below there hit the Format button.

This will take you to the Format Cells dialog box.

❽ Go to the Fill tab and choose a Background Color as per your preference.

❾ Hit OK to save the changes.

❿ Again press OK in the New Formatting Rule dialog box to save all the changes.

Here is the final result in the image below.

🔗 5 Ways to Find Matching Values in Two Worksheets in Excel

Alternative Way #3: Compare Two Columns in Excel Row-by-Row

One common task while working with a spreadsheet is to compare all the rows from a long list. This task gets easy when you know to use the IF function for comparing two columns row-by-row.

❶ First, create a new column beside the other columns.

❷ Insert the formula of the IF function in the top cell of that column (C2):

=IF(A2=B2,"Same","")

❸ Press ENTER to see the result.

❹ After that place your mouse cursor at the right-bottom corner of cell C2. The Fill Handle icon will appear.

Now drag down the Fill Handle icon over the range of cells C2:C15.

Here is the final result:

🔗 5+ Formulas to Find Duplicates in One Column in Excel

Alternative Way #4: Find the Different Rows in Two Columns in Excel Row-by-Row

In the same way, you can screen out the unmatched rows. To do that,

❶ Write the following formula in the C2 cell:

=IF(A2<>B2,"Unique","")

❷ Press ENTER to save the changes.

❸ Now put the mouse cursor on the Fill Handle icon and drag over the range of cells C2:C15.

🔗 Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]

🔗4+ Methods to Filter Duplicate Values in Excel

Alternative Way # 5: Use the IF Formula to Compare All the Cells of a Row

You can compare multiple cells within the same row for matches using a formula combining the IF and AND functions in Excel. To do that,

❶  Create a new column adjacent to the existing columns.

I’ve created a column in column D and given it a header called Fully Matched/unmatched.

❷ Now write the following formula containing the IF function and the AND function in its first cell (D2):

=IF(AND(A2=B2, A2=C2), "Fully matched", "")

Formula Breakdown

• AND(A2=B2, A2=C2) returns TRUE if both conditions are true. If both or at least one of the conditions becomes false then the formula returns FALSE.
• IF(AND(A2=B2, A2=C2), “Fully matched”, “”) returns “Fully matched” if AND(A2=B2, A2=C2) returns TRUE. Otherwise, it returns blanks.

❸ Press ENTER.

❹ Now put the mouse cursor on the Fill Handle icon and drag over the range of cells D2:D15.

And there it is!

💡 Important: If you have a larger datasheet than the one I have shown you (a list of three columns), don’t worry. Add more commands in the formula this way:

=IF(AND(A2=B2, A2=C2,A2=D2),"Fully matched","")

🔗 How to Remove Duplicates in Excel [13 + Different Methods]

🔗 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel

Alternative Way #6: Use the Conditional Formatting Rules Manager to Find the Matches in All Cells of a Row

To find the matches in a row that have the same values in all its cells, take the following steps:

❶ Select the entire table.

❷ Click on the Conditional Formatting drop-down.

❸ Then select New Rule.

❹ Select Use a Formula to Determine which Cells to Format.

❺ Now write the following formula in the Format Values Where This Formula Is True bar:

=AND($A2=$B2,$A2=$C2,$A2=$D2)

❻ Click on the Format button.

❼ Go to the Fill tab and pick a Background Color as per your choice.

I have chosen light blue.

Alternative Way #7: Use the IF & SUM Functions to Compare Rows in Excel for Finding Duplicates

To find if all cells have the same values or not within a row, we are using the previous list of salaries from different months of some employees.

❶ Create a new column beside the existing columns.

I have named the new column ‘Result’.

❷ Then write this formula in the top cell of that column (F2):

=IF(SUM(B2:E2)/4=B2,"All equal salaries", "Imbalanced salaries")

❸ Then press ENTER.

If all the cells match, the result will be Match. If there is a mismatch, the result will be Mismatch.

❹ Now pull down the Fill Handle icon to the end of the column (F2:F15).

🔗 Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]

Conclusion

I have tried to cover all possible methods to compare two columns or entire rows for detecting duplicates in Microsoft Excel. But of course, there are many other ways to find the matching cells in rows. If you know of any other easy solutions, feel free to share them with us in the comment section. And also can inform us if you have any confusion regarding this topic.