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
Free 7-day trial version, 16.74 MB
  1. Open an Excel workbook with the worksheets where you want to search for duplicates.
  2. Then open the Compare Two Tables tool by clicking on its icon See the steps you take on the left
  3. Select your first table by highlighting it.

Select the second Excel worksheet

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 your second table

Choose data you want to find: duplicates 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

Select columns to be compared for duplicate records

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.

  1. 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.
    Select columns to be compared
  2. On this step you can also indicate if there are header rows in your Excel sheets. See the headers of your tables by selecting these options
  3. 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. Exclude empty cells from the search

Select the action for the found dupes (uniques)

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

  1. If you choose to Select values, the rows with the found Excel dupes or uniques will be highlighted in your main spreadsheet.
  2. 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.
  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.
Choose what to do with the found entries

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.

See also

Featured customers

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

Please remember that we work from 8am to 4pm GMT, this is 4:00am to 12:00pm EST,
1:00am to 9:00am PST, and our public holidays may differ from yours.

Ultimate Suite for Excel Professionals
 
 
40+ 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