How to Find Duplicate Cells in Excel

Before you start

Before running the tool, please take account of the following:

• 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.
• As Excel doesn’t let add-ins undo changes, we recommend keeping the Create a backup copy of the worksheet checkbox selected.

Start Find Duplicate Cells

On the Ablebits data tab, in the Dedupe group, click Duplicate Remover > Find Duplicate Cells:

Step 1. Select a range

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

Click any cell in a table and get the entire used range selected automatically. If you need to search for duplicate cells in a specific range, place the cursor in the Select your table box or click the Select range icon and highlight the target cells, or type the range address in the box manually. To expand your selection to the entire table, click the Expand selection icon.

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:

• Select Duplicates to find all dupes except for the first occurrence:
  

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

• Choose Uniques to identify unique cells only:
  

• Pick the Uniques + 1st occurrences option to search for both uniques and first occurrences of duplicates:
  

Click Next to continue.

Step 3. Tick the required options

You can look for cells with same values, formulas, font or background color:

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 1 header row and enter the number of header rows.
• If text case matters, tick the Case-sensitive match box. With this box checked, 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. Colored cells with no values or formulas are 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 selected, click Next.

Step 4. Choose what to do with the found entries

On the final step, you decide what to do with the found duplicate and/or unique cells in the selected range:

• Clear all will eliminate all contents, formatting and comments in 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.