How to find duplicates in two Excel worksheets

The Compare Tables add-in compares two Excel lists for duplicate and unique rows.

Duplicate rows are the rows that are present in both the first and the second table. Unique rows are the rows that are present in table 1 but are missing in table 2. The add-in searches for duplicates and uniques by the key columns you select. The dupes and uniques are identified only in table 1.

Video: How to find duplicates and uniques in Excel

Before you start

Excel instances

Please make sure both tables are opened in Excel. They can be in different files but the add-in will compare them as long as they are open in the same instance of Excel.

Backup copies

We strongly recommend keeping the checkbox Create a backup copy of the worksheet selected, as Excel won't let you undo the changes made by the add-in.

How to use Compare Tables

Start Compare Tables

On the Ablebits Data tab, in the Merge group, click Compare > Compare Tables:
Start Compare Tables.

Step 1. Select table 1

Once you start the add-in, you will see the step you are on at the top of the window.

First, select the range with your table 1: all changes will be applied to the table you select on this step.
Select the first Excel table.

If you select just one cell in the table, the add-in will automatically highlight the used range at the start. You can change the range by editing it manually in the Select your first table field, using the Select range icon there, or simply by choosing the right cells in Excel.
Select the range to find uniques or duplicates.

Step 2. Pick table 2

On this step, you can see all open workbooks and worksheets in the Select your second table field. Choose the second Excel worksheet to check for the same values there:
Choose your second table.

Note. If your second table is in another workbook, make sure to open it before starting the add-in.

Click any cell in a table, and get the entire used range selected automatically. If you need to use only a specific range for comparison, click the Select range icon and highlight the necessary cells.

Tip. If you realize that something was chosen incorrectly, you can always get back to the previous step by clicking the Back button.

When the second table is chosen, click Next.

Step 3. Choose the type of data you are searching for

Specify whether you are going to search for duplicate or unique values: :
Select to find duplicate or unique rows.

  • Select Duplicate values to find the entries that are present in both table 1 and table 2.
  • Choose Unique values to mark the rows that are present in table 1 but are missing in table 2.

Click Next.

Step 4. Select the columns for comparison

This step displays a list of columns in both Excel spreadsheets.

Select the column(s) from the first table and the matching column(s) from the second worksheet to compare them and check for duplicate or unique data.
Pick the columns to compare.

  1. You can indicate if you have header rows with the help of Table 1 has headers and Table 2 has headers options respectively. You can also check the 1st row content to make sure you match the right records.
  2. If you have empty cells in your tables, tick the Skip empty cells option to ignore them while searching for duplicates and uniques in two Excel worksheets.
  3. Take advantage of the Auto Select button to quickly select all columns with matching headers. Click Unselect All to remove the selection from all columns.

Once the columns are chosen, click the Next button.

Step 5. Specify the desired action

Finally, you can choose what to do with the found duplicate or unique rows:
Choose what to do with dupes or uniques.

  • You can remove duplicate or unique values from the first table by ticking Delete values.
  • To change the background color of the found rows, check Highlight with color and pick the needed hue from the drop-down list.
  • Tick Select values, and the rows with the found Excel dupes or uniques will be highlighted in your first spreadsheet.
  • Select the Identify in the Status column option to insert a special column into your main table and mark duplicates or uniques there:
    Identify duplicates in a status column.
  • You can also copy or move the found results to another location: a new Excel workbook, new or existing worksheet. For this, tick the required option and specify where you want to place the result.
Tip. If you want the add-in to memorize the tables and options you selected, click the Save scenario button:

Save your scenario.
Then enter the name of your scenario and click OK.

Click Finish and voila! The dupes or uniques are found in your table 1 and processed the way you need.
Compare two Excel sheets and find duplicates and unique rows.

Note. For a saved scenario to work for the current worksheet, the structure of your current table must be the same as that of the table in the scenario.