How to find duplicates and uniques in Excel

Duplicate Remover for Excel is a 4-step way to quickly remove, highlight, select, or move repeated or unique cells in Excel lists and duplicate rows in tables. Here you will find the instructions on how to use the tool.

Video: Duplicate Remover for Excel

Watch more videos

How to remove duplicate rows in Excel
How to delete duplicates in Excel
Highlight and identify unique entries in Excel

Before you start

We care about your workbooks and always recommend creating backup copies of your files by selecting the corresponding option in the add-in.

How to use Duplicate Remover

Start Duplicate Remover

  1. Open your Excel workbook with the table you need to check for duplicate or unique values.
  2. On the Ablebits Data tab, in the Dedupe group, click the Duplicate Remover icon.

Open Duplicate Remover

Tip. You can pre-select the type of data to look for and jump right to step 3 by clicking on the down arrow below the add-in's icon:
Pre-select the type of data.

Step 1. Select a table

First, choose a range to dedupe.
Select Excel list to dedupe.
Click any cell in your table and get the whole dataset highlighted automatically. You can change the range manually by editing it in the Select your table field, clicking the Select range icon, or simply by selecting the needed cells in Excel.
Pick the range.

Note. We strongly recommend having the Create a backup copy of the worksheet option checked as Excel doesn't let you cancel changes made by add-ins.

When the needed range is picked, click Next.

Step 2. Choose the type of data to look for

Unless you use the drop-down list on the toolbar, this is the step where you choose the type of data to find:
Choose the type of data.

  • Select Duplicates to find all dupes except for the first occurrence:
    Find duplicates.
    Learn the fast way to Find and delete duplicate lines.
  • Use Duplicates + 1st occurrences if your aim is to find all duplicates including the first appearance:
    Find duplicates and first occurrences.
    See how to Remove duplicates by key columns in this video.
  • Choose Uniques to identify unique cells only:
    Find unique cells.
    Watch the Highlight and identify unique entries tutorial.
  • Pick the Uniques + 1st occurrences option to search for both uniques and first occurrences of duplicates:
    Find uniques and first occurrences.
  • Select 1st occurrences of duplicates to find only the first duplicate instances:
    Find first occurrences.
Tip. If you select a wrong option, you can always return and choose another by clicking the Back button.

Click Next to continue.

Step 3. Pick columns to check for duplicates and uniques

This step displays a list of columns with their headers as they are in your worksheet:
Pick columns with duplicates to dedupe Excel list.
You can take advantage of the additional options:

  • 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 you have more than one header rows, click 1 header row and enter the number of header rows your table has.
  • If you have empty cells in your Excel table, tick the Skip empty cells option to exclude them from the search results.
  • The Ignore extra spaces option may be useful if your data may contain extra spaces that you want to ignore. For example, the " September " and "September" values will be identified as duplicates with the checked Ignore extra spaces option and will be considered two different values if the box is not ticked.
  • If text case matters, tick the Case-sensitive match box. With the box checked, the same text written in different cases ("Text" and "text", for instance) will be considered as different text.
  • If your range has no labels, look at the 1st row content area to make sure you choose the right columns.
  • 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 Select All and Unselect All buttons to quickly clear the selection and specify only the records you want to check.
Note. If you select more than one column, the add-in will look 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 with column B, please use the Compare Tables tool instead with column A as your first table and column B as your second table.
Tip. If you have a long list of columns, you can make the wizard window bigger by dragging the bottom-right corner down and to the right.

When the columns are selected, click Next.

Step 4. Choose what to do with the found entries

In the final step, choose the action to perform with found entries:
Identify duplicates and uniques in Excel, remove or select them.

  • You can have the rows with duplicate values deleted.
  • Highlight results with color to quickly see all the duplicated rows. You can pick the color from the drop-down list next to the option.
    This is how you can Highlight duplicate rows.
  • Select the found values to be able to work with them further.
    Here is how you can quickly Select duplicate and unique values in Excel.
  • Mark the found dupes or uniques in the Status column in your Excel list.
    What is the status column? See it in the Identify duplicate rows in a special column video.
  • Copy or move the results to another location (a new workbook, new or existing worksheet).

Click Finish.
Highlight duplicates in Excel.

Scenarios

How to save scenarios

If you'd like to create a scenario and reuse it any time you need, click the Save scenario button in Step 4: Save your scenario. You'll be asked to name the scenario you're going to save. After entering the name of the scenario, click OK: Name your scenario.

How to run scenarios

To see the list of your saved scenarios, click the Duplicate Remover icon above the tool's dropdown menu on the ribbon:
This is the icon you need.
Select the scenario that you want to run and the table to be used. When you're done, click the Start button: Choose from your saved scenarios.

Note. For a saved scenario to work for the current worksheet, the structure of your current table must be the same as that of the table in the scenario.