Find duplicates in Excel

Duplicate Remover for Excel: delete dupes, find uniques in 1 worksheet

Find duplicates in Excel list

On this page you will find step-by-step instructions on how to work with Duplicate Remover for Excel to quickly find duplicates and unique values in 1 list.

Free Download
7-day fully functional version

How to quickly find duplicates in 1 Excel table

If you often work with similar tables, use Dedupe Table as a prompt Excel duplicate finder for a single table. It's a one-step tool with simple settings:

Finding duplicates in a single Excel table with Dedupe Table
  1. Open your Excel table and click on the Quick Dedupe icon to open Dedupe Table. Duplicate Remover It will automatically select the entire range with data in your worksheet. If you want to check a particular range, select it before opening Dedupe Table.
  2. Define the settings for the search:
    • It may be helpful to indicate that there is a header row in your table. As a rule, the add-in identifies headers automatically. If it doesn't, you can check the My table has headers option. This will let you see what records you have in the columns. You can also uncheck this option if your Excel table doesn't have headers and check what kind of information is there by looking at 1st row content next to the columns.
    • If you may have empty cells in your search range, you can tick the Skip empty cells option and exclude such cells from the search.
    • Select columns where you want to look for Excel duplicates.
      All columns are selected by default. If you have a lot of columns in your table, take advantage of the checkbox at the top of the list and the Unselect All button to have all columns deselected. Then you can tick those you need to search in.
    Note. If you select more than one column, the add-in looks for duplicate rows. E.g. if you select 2 columns, only those records that have the same values in both columns will be considered dupes.
  3. Choose what you want to do when the Dedupe Table add-in finds duplicates in Excel:
    • Delete the rows with the found duplicates.
    • Color values. This will change the background color of the duplicated rows.
    • Select the 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.
    • Copy the found dupes to a new sheet
    • Move the results to a new worksheet.
    We also recommend keeping the Back up the worksheet option selected.
  4. Click OK and enjoy the results!

Once you open Excel workbook with the table you need to check for duplicate values, click the Duplicate Remover icon in the Dedupe group on Ablebits Data tab to start the add-in.

Duplicate Remover

You can pre-select the type of data to look for to jump right to to step 3: click on the down arrow next to the add-in icon in the toolbar and pick Duplicates Only to find just the repetitions, or Duplicates and 1st Occurrences to include their first instances.

Choose to find duplicates and include their first instances right on the toolbar

Step 1: Select your Excel table

As soon as you start the add-in, it selects the entire range with data you have in your Excel worksheet. If you wish to change the table it checks for duplicates, either select it right in the sheet, use the “Select range” icon in the field, or enter the range in the Select your table field.

Select the data range to check in Excel

We also recommend selecting the option to Create a backup copy of the worksheet as Excel doesn't let you cancel changes made by add-ins.

Click Next to continue.

Step 2: Choose the type of data you want to find

Unless you use the drop-down list on the toolbar, this is the step where you can choose to search for duplicated entries only or duplicates with their first occurrences. Choose the option with the type of data you are looking for.

Select the data type you want to find: duplicates or uniques

Step 3: Select columns to check for duplicates

This step will display a list of columns with their headers in your worksheet. Unless your range is formatted as a table in Excel, you can let the add-in know if the first row contains labels with the My table has headers option at the top. If it has no labels, look at the 1st row content to make sure you choose the right columns.

Select one or more columns that contain duplicates
Note. If you select more than one column, the add-in looks for duplicated rows. In other words, only those records that have the same values in both columns will be considered duplicates. If your purpose is to compare column A to column B, please use the Compare Tables tool instead with column A as your first table and column B as your second table.
  • All columns are selected by default. If you have a lot of columns in your table, take advantage of the top checkbox next to Columns or use the Unselect All button to quickly clear the selection and specify only the records you want to check.
  • If you may have empty cells in your Excel table, you can tick the Skip empty cells option and exclude such cells from the search results.
Tip. If you have a long list of columns, you can increase the size of the wizard window by dragging the bottom-right corner down and to the right.

Step 4: Specify what to do with the found records

Select the action that will be applied to the found Excel duplicates on the last step.

Choose what to do with the found duplicates
  • You can have the rows with duplicate values deleted.
  • Highlight results with color to quickly see the duplicated rows. You can pick color from the drop-down list next to the option.
  • Select values to be able to work with them further.
  • Identify in the Status column to mark such rows in your Excel list.
  • Copy or move the results to another location (a new workbook, new or existing worksheet).

Click Finish and enjoy the results

How to find unique values in your Excel table

Once you open the worksheet where you need to find unique records, click the Duplicate Remover icon in the Dedupe group to start the add-in.

Click on the down arrow next to the add-in icon in the toolbar to pre-select the type of data to look for to jump right to step 3: choose Uniques Only to find just the values that are not repeated, or Uniques and 1st Occurrences to include the first instances of duplicate values as well.

Choose to find uniques or include the first instances of the duplicates

Step 1: Select your data range

The add-in selects all data you have in your Excel worksheet by default. There are three ways you can modify the range to check for uniques: enter it in the Select your table field, select it right in the worksheet, or use the “Select range” icon to do it.

Select the data range to check in Excel

Excel doesn't let you cancel changes made by add-ins, so we recommend keeping the Create a backup copy of the worksheet checkbox selected.

Click Next to choose the type of data to find.

Step 2: Specify the type of records you want to find

If you don't use the drop-down list on the toolbar, here you can choose the option with the type of data you are looking for: unique values or uniques and the first occurrences of the dupes.

Specify if you want to find uniques or include the first instances of duplicated values

Step 3: Choose the columns to scan for uniques

This step lets you specify the columns or the combination of columns in your Excel table that will be checked for unique data.

Specify the key columns to check for unique records

The add-in selects all columns by default. You can quickly clear the selection with the help of the Unselect All button or using the checkbox at the top of the list. Then you can tick only those you need to search in.

  • You can indicate if there is a header row in your table by checking My table has headers. This will let you see what kind of information you have in the columns. You can also uncheck this option and look at the 1st row content instead, unless your range is formatted as a table in Excel.
  • Use the Skip empty cells option to exclude blanks from the search.
Tip. If you have many columns in your table, drag the bottom-right corner of the wizard window down and to the right to make the list fit in.

Step 4: Select, highlight, or copy the found values

The last step lets you choose how to deal with the found unique values in your Excel table.

Specify what to do with the found unique values

You can choose one of the following options:

  • Highlight with color to change the background color of uniques.
  • Select values and apply other actions to them.
  • Identify in the Status column to see unique rows marked in a new column.
  • Or copy to another location (get a new workbook, sheet, or insert results into an existing Excel worksheet).

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