Find duplicate values in two worksheets
How to find and remove duplicates from 2 Excel spreadsheets
On this page you will find step-by-step instructions on how to search for duplicates in two Excel lists. Use the Compare Two Tables tool to find unique records or remove duplicate values between 2 worksheets.
- Open an Excel workbook with the worksheets where you want to search for duplicates.
- Then open the Compare Two Tables tool by clicking on its icon
- Select your first table by highlighting it.
On this step you will see all the open workbooks and worksheets in the Select your second table field. Select the Excel workbook and worksheet you want to de-dupe and you will see it highlighted.
Select the type of data that you are looking for:
- duplicate values (entries that are present in both Excel lists), or
- unique values (entries that are present only in the first Excel table).
On the Select the columns that will be used for comparison step you will see a table with 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. Identical columns will be automatically marked in bold.
- On this step you can also indicate if there are header rows in your Excel sheets.
- If you may have empty cells in your tables, you can tick the Skip empty cells option and exclude such cells while searching for duplicates in two Excel worksheets.
Choose what you want to do with the found duplicate rows or unique values.
- If you choose to Select values, the rows with the found Excel dupes or uniques will be highlighted in your main spreadsheet.
- You can color values to change the background color of the found rows, by picking the color from the drop-down list next to the Fill with color option.
- Add a status column to your main table to see if a row contains duplicates or uniques.
- Copy or move the duplicated results to another location (a new Excel workbook, new/existing worksheet).
- Or delete duplicate values. All found dupes will be removed in seconds.
Click on the Finish button and enjoy the results.
Wait for a couple of seconds and let Duplicate Remover find all duplicates in your Excel sheets.