How to Find Duplicate Cells in Excel

With Ultimate Suite for Excel

The Find Duplicate Cells tool scans the selected Excel range cell by cell and can find duplicates, duplicates + 1st occurrences, unique cells, or uniques + 1st occurrences. You can define what exactly should be identified: same values, formulas, background, or font color–and tick one or more of these options.

The found cells can be eliminated entirely, or you can select to clear formatting or contents. Also, you can get all the found cells colored or selected. For example, in the range below, if we search for duplicates + 1st occurrences with same values and want them all selected, the result will be the following:

Find Duplicate Cells.

Before you start

Before working with the tool, please note the following points.

  • Only visible cells of your table will be processed. Please make sure the range you select does not contain any hidden rows or columns and doesn't have filter applied.
  • While working with the add-in, you can't use Excel's Undo option. We suggest you always create backup copies of your worksheets by keeping the Back up this worksheet option checked on the first step.

Run the wizard

Go to the Ablebits Data tab and click on the small down arrow under the Duplicate Remover icon:

Start Find Duplicate Cells.

In the drop-down list, click on Find Duplicate Cells:

Start Find Duplicate Cells.

Step 1. Select a range

First, you choose the range to search for duplicate cells:

Select your range.

On the right side of the Select your table field, you can see two icons: Expand range and Select range.

Also, there is the Back up this worksheet box that we recommend always keeping checked. A backup copy of the sheet will be automatically created in your workbook and will have the same name as the initial sheet with a hashtag before it. If your sheet is named "Sheet", the backup copy will be named "#Sheet(1)".

When the needed range is selected, click Next.

Step 2. Choose the type of data to look for

You can look for one of the four types of data:

Find and remove duplicates or uniques.

  • Select Duplicates to find all dupes except for the first occurrence:
    How to remove duplicates but leave their first occurrences in Excel.

  • Use Duplicates + 1st occurrences if your aim is to find all duplicates including the first appearance:
    Find duplicates and first occurrences.

  • Choose Uniques to identify unique cells only:
    Find unique cells.

  • Pick the Uniques + 1st occurrences option to search for both uniques and first occurrences of duplicates:
    How to find distinct cells.
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. Tick the required options

You can look for cells with same values, formulas, font or background color:
Tick the required options

Note. For the Same background and Same font color options, the default background and font colors are not considered by the tool.
Note. If your range includes conditionally formatted cells and you choose the Same background or Same font color options, the add-in performance may significantly slow down.

Also, tick the additional options that suit your data:

  • My table has 1 header row lets you exclude the header row from the search. If you've got more header rows, click on the '1 header row' phrase and enter the number of header rows.
  • If text case matters, tick the Case-sensitive match box. With the checked box, the same text written in different cases ("Text" and "text", for instance) will be considered as different text.
  • Tick the Skip empty cells option to exclude cells that have no values or formulas from the search results. If they are colored–they are still considered empty.
  • If the Ignore extra spaces option is checked, leading and trailing spaces will be ignored, as well as extra spaces between symbols. For instance, ' ab c ' and 'ab c' cell values will be identified as the same.

When all the needed options are checked, click Next.

Step 4. Choose what to do with the found entries

On the final step, you are to select the action that will be applied to the found duplicate and/or unique cells in the selected range:

Identify duplicates and uniques in Excel, remove or select them.

  • Clear all will eliminate all contents, formatting and comments of the found cells.
  • Clear contents will delete only values and formulas leaving formatting and comments intact.
  • Clear formats will clear formatting in the found cells.
  • To color the found cells, tick Highlight cells with color and select the color of interest.

    Note. This option may have no effect on conditionally formatted cells.
  • Opt for Select cells to get all the found cells selected. However, please note that if you work with large ranges, the number of selected cells may be limited by Excel.

Click Finish.

More ways to remove duplicates

Ultimate Suite provides several tools for working with duplicates: