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, fill, or font color.
The add-in can remove duplicates, clear formatting in the found cells, or just color or select dupes for you to work with them further.
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 as follows:
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.
Note. We recommend having the Create a backup copy of the worksheet option checked as Excel doesn’t let the add-in undo changes. If you have this checkbox selected, 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:
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:
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:
Note. For the Same background and Same font color options, the default background and font colors are ignored 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 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.
More ways to remove duplicates
Ultimate Suite provides other tools for working with duplicates:
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at firstname.lastname@example.org.