How to Compare Rows in Excel for Duplicates [6 Examples]

Follow these steps to compare rows in Excel for duplicates:

Select cell C2.
Write the following formula: =IF(A2=B2,”Same”,””)

Press ENTER to see the result.
Now drag the Fill Handle icon from cell C2 to C15.

This formula returns “Same” if it finds any duplicates. Otherwise, it leaves the cell blank.

Compare Rows in Excel for Duplicates.xlsx

Table of Contents

Compare Rows in Excel for Duplicates with IF Function

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 months of January and June.

=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’.

To find the duplicates in this table by comparing rows in Excel, follow these steps:

Create a new column (Result) right next to the existing columns.
Then write the formula in the top cell (C2) of that column: =IF(A2=B2,”Same”,”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)

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

Compare Rows for Duplicates in Excel Using Conditional Formatting

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.
From the New Formatting Rule 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.
In 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.

Compare Two Columns Across the Rows for Duplicates with IF Function

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 how to use the IF function for comparing two columns row-by-row.

Follow these steps to compare two columns across the rows and find duplicates with IF function in Excel:

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

5 Ways to Find Matching Values in Two Worksheets in Excel

Compare 3 Columns Across the Rows for Duplicates with IF & AND functions

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

Formula

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

To compare three columns across the rows and find duplicates in Excel using IF and AND functions, follow these steps:

Create a new column (Fully Matched/unmatched) adjacent to the existing columns (Salary in December).
Write the following formula in cell (D2): =IF(AND(A2=B2, A2=C2), “Fully matched”, “”)

Press ENTER.
Drag the Fill Handle over the range of cells D2:D15.

And there it is!

Note: 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","")

Compare 4 Columns Across the Rows for Duplicates by Conditional Formatting

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 in the Format Cells dialog box.

I have chosen light blue.

Compare 4 Columns in Rows for Duplicates with IF & SUM Functions

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.

To compare four columns in the rows for duplicates using IF & SUM functions in Excel:

Create a new column (Result) beside the existing columns.

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.
Now pull down the Fill Handle icon to the end of the column (F2:F15).

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

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. Also can inform us if you have any confusion regarding this topic?

Frequently Asked Questions

How do I find matching cells in Excel?

To find matching cells in Excel, follow these steps:

Click on the first cell of the range where you want to find matching cells.
Go to the Home tab in the Excel ribbon.

Click on Conditional Formatting in the toolbar.
Hover over Highlight Cells Rules in the menu.
Select Duplicate Values from the submenu.

In the Duplicate Values dialog box, you can choose the formatting options for the matching cells. For example, you can choose a fill color.
Click OK to apply the formatting.

Excel will highlight all the cells with duplicate values in the selected range according to the formatting you specified.
This method allows you to quickly identify and visually locate matching cells in your Excel worksheet.

How do I count rows without duplicates?

To count unique rows in Excel and exclude duplicates, you can use the following steps:

Click on the first cell of the column containing the data you want to count without duplicates.
Go to the Data tab in the Excel ribbon.

Click on Remove Duplicates in the toolbar.
In the Remove Duplicates dialog box, select the column or columns that you want to check for duplicates.
Click OK to remove duplicates.

In an empty cell, use the COUNTA function to count the remaining rows. For example, if your data was in column A, enter the formula =COUNTA(A:A) in a different cell.
Press ENTER to execute the formula, and it will display the count of unique rows.

This method efficiently counts rows without duplicates by first removing duplicate values and then using the COUNTA function to count the unique entries in the specified column.

How do I find and delete duplicate rows?

To find and delete duplicate rows in Excel, follow these steps:

Click on any cell within the range that contains the data with duplicate rows.
Go to the Data tab in the Excel ribbon.

Click on Remove Duplicates in the toolbar.
In the Remove Duplicates dialog box, select the column or columns that you want to check for duplicates.
Click OK to identify and remove duplicate rows based on the selected columns.
Excel will show a preview of the duplicate values. Review the selection to ensure it includes the correct columns.

Click OK to delete the duplicate rows.

Excel will provide a confirmation message indicating the number of duplicate rows removed. This method efficiently finds and deletes duplicate rows in your Excel dataset, helping to streamline and organize your data.

How do I compare two cells in Excel?

To compare two cells in Excel, you can use simple formulas or functions. Here’s a step-by-step guide:

Click on the cell where you want to display the result of the comparison.
Type the following formula: =IF(A1=B1, “Match”, “No Match”)
Here A1 and B1 are the cells you want to compare.
Press ENTER.
If the content of cells A1 and B1 is the same, the formula will display “Match”; otherwise, it will show “No Match”. Alternatively, you can use other comparison operators in the IF function, such as <, >, <=, or >=, depending on the nature of your comparison.

This method provides a quick way to compare two cells in Excel and display a result based on their equality or inequality. Adjust the formula as needed for your specific comparison requirements.

How do I filter duplicates in Excel?

To filter duplicates in Excel, follow these steps:

Click on any cell within the range that contains the data with duplicates.

Go to the Data tab in the Excel ribbon.
Click on Filter in the toolbar. This will add filter arrows to the headers of your selected range.
Click on the filter arrow in the column header where you want to identify duplicates.
In the filter options, unselect Select All and then choose Duplicates.
Click OK or Filter to apply the filter.
Excel will display only the rows with duplicate values in the selected column. You can now review and work with the filtered data.

To revert to the original view, click on the filter arrow again and choose Clear Filter.

This method allows you to quickly identify and work with duplicate values in a specific column by using Excel’s built-in filter functionality.

Rate this post