Find duplicates in an Excel list – nice duplicate finder for Excel 2010 - 2000

Duplicate Remover for Microsoft Excel

Remove all Excel duplicates – deduplication addin for Excel 2010 - 2000

For Microsoft Excel 2010 32-bit and 64-bit, Excel 2007, Excel 2003.

On this page you will find step-by-step instructions on how to work with Duplicate Remover for Excel, in particular, how to find duplicates / uniques in 1 list.

• Quickly find duplicates in a single table (list)
• Perform advanced search in a single Excel table

How to quickly find duplicates in a single table (list)

If you often work with similar tables, Quick Remover tool lets you quickly find duplicates in a single table. Just follow these simple steps:

1. Open Excel workbook with the table and select the range you want to search in.
   Then open Quick Remover by clicking on its icon or pressing the Ctrl+Shift+D hotkey.
   If you work in Excel 2003 - 2000, you can also go to Excel menu -> Ablebits.com -> Duplicate Remover for Excel -> Quick Search

   Tip: Before opening Quick Remover, select any cell from your list (table/range). The wizard will automatically select the entire table.

2. Define the settings for the search:
   • Select columns to be searched for duplicates.
     All columns are selected by default. If you have a lot of columns in your table, take advantage of the Uncheck All button and all the columns in the list will be deselected. Then you can tick those you need to search in.
     

     Note! If you select more than one column, the add-in looks for "row" duplicates. So if you select 2 columns, only those records that have the same values in both columns will be considered duplicates. E.g. If "Fisher 36 Oak Drive" is duplicated on another row, the add-in will find it. If your purpose is to compare column A to column B, then use Advanced Wizard and choose the option to "Compare two tables" on step 1, select column A as your first table and column B as your second table.

   • It may be helpful to indicate that there are header rows in your table. As a rule, the add-in indicates headers automatically. If it didn't, you can check the My table has headers option. This will let you see what kind of information you have in the columns. You can also uncheck this option if you don't want header rows to be indicated. If your table doesn't have headers, you can see what kind of information is in the columns by looking at the 1st row content column next to the columns.
   • You can choose to ignore extra spaces.
     You should tick the Ignore extra spaces in matching columns checkbox if your data may have some extra leading or trailing spaces. This option will prevent the add-in from missing some values.
   • If you may have empty cells in your search range, you can tick the Ignore empty cells option and exclude such cells from the search.
     
3. Choose what you want to do with the found entries:
   • Delete the rows with the found duplicates.
   • Color values. This will change the background color of the found rows.
   • Select found duplicates. In this case the rows with the found entries will be selected in your main table.
   • Add a status column to your list to see if a row contains duplicates.
   
4. Click "Run" and enjoy the results!

Perform advanced search in a single Excel table

• Select the search mode
• Choose the type of data you want to find
• Select columns to be searched for duplicates or uniques
• Select the action for the found entries

1. Open Excel workbook with the table you need to check for duplicate or unique values.
2. Then open Duplicate Remover Advanced Wizard by clicking on its icon
   If you work in Excel 2003 - 2000, you can also go to Excel menu -> Ablebits.com -> Duplicate Remover for Excel -> Advanced Wizard

When working with the add-in, on the left you can see the step you are on.

Select the search mode

1. Select the Search in a single table mode and choose the range for the search. You can select the table by clicking the select range icon or enter its address in the Select your Table field.
   
   

   Tip: Before you start Duplicate Remover Advanced Wizard, select any cell in your list. The wizard will automatically select the whole table.

2. Click the Next button on the Duplicate Remover dialog box to continue.

Choose the type of data you want to find

You can search for duplicate entries only, duplicates with their first occurrences, for all unique values or uniques with the first occurrences of the duplicates. Choose the option with the type of data you are looking for.

Select columns to be searched for duplicates or uniques

1. On the Select matching columns step you will see a table with a list of columns in your table. You can choose the columns that will be checked for duplicates or uniques.
   

   Tip: All columns are selected by default. If you have a lot of columns in your table, take advantage of the Uncheck All button and all the columns in the list will be deselected. Then you can tick those you need to search in.

   
   
   

   Note! If you select more than one column, the add-in looks for "row" duplicates. So if you select 2 columns, only those records that have the same values in both columns will be considered duplicates. E.g.
   
   If "Fisher 36 Oak Drive" is duplicated on another row, the add-in will find it. If your purpose is to compare column A to column B, then select "Compare two tables" search mode on step 1, select column A as your first table and column B as your second table.

2. On the Select matching columns step you can also choose to ignore extra spaces in matching columns.
   You should tick the Ignore extra spaces in matching columns checkbox if your data may have some extra leading or trailing spaces. This option will prevent the add-in from missing some values.
   
3. It is also possible to indicate if there are header rows in your table. As a rule, the add-in indicates headers automatically. If it didn't, you can check the My table has headers option. This will let you see what kind of information you have in the columns. You can also uncheck this option if you don't want header rows to be indicated.
   
4. If you may have empty cells in your table, you can tick the Ignore empty cells option and exclude such cells from the search results.
   
5. Click on the Next button on the Duplicate Remover dialog box to proceed.
6. If you need to get back to the previous step, press the Back button on the dialog box.

Select the action for the found entries

On the Choose what to do with found values step you can select the action that will be applied to the found entries.

If you are searching for duplicates, you can do the following:

• Select found values. In this case the rows with the found entries will be selected in your main table.
• Color values to change the background color of the found rows. You can pick the color from the drop-down list next to the Color values option.
• Add a status column to your list to see if a row contains duplicates.
• Copy or move the results to another location (a new workbook, new/existing worksheet).
• Or delete the rows with the found duplicates.
  

If you are looking for unique entries, you can choose one of the following options:

• Select found values
• Color rows with unique values
• Add a status column
• Copy uniques to another location (a new workbook, new or existing worksheet)
  

Click on the Finish button

Allow a few seconds for processing.

Enjoy the results.

See also

• Comparing two Excel tables for duplicates or uniques

Download now and see how it can help you