# How to Compare Rows in Excel for Duplicates (7 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")

Here,

**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 #****: 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**

**Method 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 **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", "")

Here,

**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 and SUM Function 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 you can inform us if you have any confusion regarding this topic.

Visit our website **Excelgraduate** to get more articles on Microsoft Excel.