How to find duplicates in two Excel worksheets

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.

Start Compare Tables

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

Step 1. Select table 1

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

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

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.

Step 2. Pick table 2

In 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:

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.

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

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 1^st 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:

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

Click Finish and voila! The dupes or uniques are found in your table 1 and processed the way you need.

How to save scenarios

In Step 5, before clicking the Finish button, click Save scenario:

Decide on a name for your scenario and enter it into the Compare Tables dialog. Then click OK:

How to run scenarios

To run any of your saved scenarios, start the Compare Tables tool, select the necessary scenario from the list, specify which worksheet is going to be used as the first table, and click Start: