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

Click the Compare Tables icon in the Dedupe group on the Ablebits Data tab:
Find the Compare Tables icon on the Ablebits Data tab.

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 to find duplicates.

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 the second table to find and remove duplicates.

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

Here you are to indicate what you are looking for in table 1:
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 how to deal with the found duplicate or unique rows. The add-in offers plenty of options:
Choose to remove, select, or highlight 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.
  • If you choose to Select values, the rows with the found Excel dupes or uniques will be simply 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 select the location of the result.

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.

Responses

I have a question about a voter 'name matching' problem. *

BTW, news about the 'name matching' problem went 'viral' in Nov 2018 when the man who won for governor of Georgia was also the Secretary of State who wrote the rules for deciding who got to vote. He narrowly defeated Stacey Abrams, who had a chance to become the first black female governor. Even in defeat, she became nationally famous for her fight against the disenfranchisement of a large block of voters whose votes weren't counted because the name was not precisely identical to the registered name and there was no chance to appeal.

* The 'name matching' problem leads me to ask: Do you have plans to enhance the table comparison Action options to = ('equals') with familiar options elsewhere in Microsoft Office such as 'contains'? Right now, when you search large databases of voters Gwen Clifton doesn't match Gwendolyn Clifton.

Reply

Hi Jim,
Thank you for the informative and interesting comment.
I think that our Fuzzy Duplicate Finder or Find Similar Cells are the tools that may help with the described problem.

Thank you!

Reply

Hi, I have one spreadsheet with a list of names, account numbers and addresses for a contact list and a second spreadsheet with names and account numbers that is a do not call list. I need to compare the two sheets, looking for account numbers, and delete the entire row from the main contact worksheet if the same account number exists on the do not call sheet. I have looked through the tools from Ablebits and don't see any way to be able to do this. Can this be done with your tools, or is there another way to do this that you know of? Any assistance would be appreciated.

Reply

Hi Ray,
Thank you for the comment.
Have you tried the Compare Sheets tool?
https://www.ablebits.com/docs/excel-compare-worksheets/

Looks like it does what you need.
Please feel free to contact us if you need any further assistance.

Reply

Irina,
I have tried the both the compare two sheets and compare multiple sheets tools, but I don't see an option to show matches, only differences.

Ray,
I have looked into your task once again, and if I understand it correctly, the Compare Tables - the tool of this page - can help you.
Your steps should be the following:
Step 1. Select your main table (the contact list with names, account numbers, and addresses).
Step 2. Select the entire range of your "do not call list".
Step 3. Choose the Duplicate values option.
Step 4. Select the columns with account numbers in table 1 and table 2.
Step 5. Choose the Delete values option.

Please let me know if this helped.

Thank you.

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-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