Find duplicate values in two worksheets

How to find and remove duplicates from 2 Excel spreadsheets

Compare two Excel spreadsheets and remove duplicates

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.

Free Download
7-day fully functional version

Please make sure both tables are open in Excel. They can be in different files, the add-in will compare them as long as they are open in the same instance of Excel. Once you are ready, click the Compare Tables icon to start the process.

See the steps you take on the left

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 first table: all changes will be applied to the table you select on this step.

The add-in will automatically select all data in your worksheet at start. You can change the range by manually editing it in the Select your first table field, using a special icon there, or simply selecting the right cells in Excel.

Select the range with your first table

Excel doesn't let you cancel changes made by add-ins, feel free to select the Create a backup copy of the worksheet checkbox to get a copy of your original table.

Click Next to continue.

Step 2: Select the second Excel worksheet

On this step you will see all open workbooks and worksheets in the Select your second table field. Select the second Excel worksheet to check for the same values and you'll see it highlighted.

Select your second table

Step 3: Choose data you want to find: duplicate or unique values

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).
Choose if you want to find duplicate or unique values

Step 4: Select columns to be compared for duplicate records

This step will display 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.

Select columns to be compared
  • Take advantage of the Auto Select button to quickly select all columns with matching headers. Click Unselect All to remove selection from all columns.
  • You can also indicate if the first row is perceived as a label 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.
  • 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.

Step 5: Select the action for the found dupes (uniques)

Choose what you want to do with the found duplicate rows or unique values.

Choose what to do with the found entries
  1. If you choose to Select values, the rows with the found Excel dupes or uniques will be highlighted in your first spreadsheet.
  2. You can change the background color of the found rows, pick the color from the drop-down list next to the Highlight with color option.
  3. Add a status column to your main table to see if a row contains duplicates or uniques.
  4. Copy or move the duplicated results (to another location (a new Excel workbook, new/existing worksheet).
  5. Or delete duplicate values. All found dupes will be removed in seconds.

Click the Finish button and enjoy the results.

See also

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish your message on our support forum
 

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard
 
 
Excel add-ins and Outlook tools - Ablebits.com