A short video tutorial: how to compare 2 sheets for duplicates

Detailed steps to compare two sheets

Find Remove Duplicates add-on in the Add-ons menu and click on the Compare two sheets tool to open it.

Step 1: Select your main table

First select the sheet and range with your main table. The action you select for the results will be applied to this sheet.

Note: If you want to delete duplicates, it's best to select the whole table so that the add-on removes the entire rows and you can avoid data corruption.

You can select any of the open sheets in the 'Select your sheet' drop-down list.

Please select the sheet with your main table

'Select the range with your table' shows the current highlighted range in the selected sheet. You can alter it by changing the selection or by entering the range address in this field.

Use the Auto select" button to automatically pick the range with all data you have.

You can choose to back up the original data by selecting the check box 'Create a backup copy of the sheet'

Click Next to proceed to the second step, or cancel the process by clicking Cancel

Step 2: Select the table for comparison

Select the sheet with the table you want to compare to your main one.

You can select any of the open sheets in the 'Select your sheet' drop-down list.

Please select the second table for comparison

You can define the range with your table in the 'Select the range with your table' field.

Tip: Use the Auto select button to automatically pick the range with all data you have.

Click Next to proceed to the second step, go back to change the main table, or cancel the process by clicking Cancel.

Step 3: Choose what to find

The add-on allows you to find singular or repeating values in the tables.

Choose Duplicates to find repeating data

Pick this option to look for the values you have in both tables. The action you select for the found duplicates will be applied to the main table.

Unique values

Choose uniques to find those records that you have only in your main table.

You can return to the previous steps by clicking Back; click Next to go on to the next step, or Cancel to call off the process.

Step 4: Select the columns to compare

You will see a list of columns in your tables. Please select the check boxes next to the key columns you want to compare in your sheets and pick the corresponding column in your second table.

Tip: If you select more than one column, a row will be considered duplicate if the values in all the selected columns are the same.

If your tables have header rows, make sure you tick off the check box 'Table 1 has headers' and 'Table 2 has headers' respectively to see the column titles. If you don't have headers, unselect this box to display column names. You can use the '1st row content' field to see what kind of data you have in your main table columns.

Choose columns you want to compare

You can automatically select all columns that have the same headers in both tables by using the Auto detect button.

If you want to look for complete row duplicates, select all columns by using the top check box in the header row and make sure you choose the corresponding Table 2 columns.

You can tick off the 'Skip empty cells' option to ignore duplicated blanks in the selected columns. If this check box is not selected, blank matches will be considered duplicates.

Use the 'Case sensitive' option to consider those values that differ only in case as unique values.

Note: Leading and trailing spaces in the cell values are ignored by default.

Click Back to return to the previous step, click Next to proceed to the last step, or cancel the process by clicking Cancel.

Step 5: Choose action for the found values

Currently you can choose from six ways to deal with the found values. Select the radio button next to the action you'd like to apply to your main table.

Remove the found duplicates from your main table or delete entire rows
  • Color the rows with the found values by picking the 'Fill with color' option. Click on the arrow next to this option to pick a color you'd like to use.
  • 'Add a status column' to your main table and sort out duplicates
  • Select to 'Copy or move results to another location' and choose to have them in a new or in the current spreadsheet
  • You can remove all rows with the found values using the 'Delete rows' option.
  • Choose to 'Clear values' to remove the found values in the selected key columns and leave all other data intact.

Click Back" to change the previous settings, cancel the process, or click Finish to find duplicate or unique values.

See the results

Once the add-on completes the search, you will see the summary of the results with the number of found values and the action that was applied.

See the summary with the number of found values

If you'd like to run the add-on again, click on the Start over button. Otherwise click Close.

If you have any questions or difficulties with the add-on, please use the form below to let us know. We'll do our best to assist you.

See also:

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
Sheila Blanchard
Excel add-ins and Outlook tools - Ablebits.com