Remove duplicates in Excel online
If you work in Office 365, see how Duplicate Remover works in Excel online. The add-in conveniently splits the process of finding duplicate or unique records into three simple steps:
Select the range with your data
The add-in will process the results only within the selected range, so it’s always best to select all data you have in the sheet to avoid data corruption. You can use the Auto select button to do it. You can also enter or edit the address right in the "select range" field. If you prefer to select your table right in the Excel sheet, the add-in will recognize and display the address.
Define key columns
Once you select the range, choose what columns the add-in should check for duplicate values. You can check records within just one of the columns, or find entire row duplicates by selecting all columns.
When you select more than one column, the add-in will look for the combination of values in these columns. E.g. if you select "Order number" and "City" columns, it will find rows with the same order numbers and cities. If it finds the same order numbers in different cities, it will not consider them duplicates.
Once you specify the key column(s) for the search, click Next.
Choose if you want to find duplicates or uniques
This step lets you choose the type of data you want to find in your online Excel sheet and specify if you want to include the original values:
- Choose duplicates to find all additional occurrences of the key values;
- Include their original values by selecting Duplicates + first occurrences;
- Select Uniques to find single values;
- Search for single values and the first entries of the duplicates with the Uniques + first occurrences option.
Choose additional settings
There are two settings that help you narrow down the results:
- Match case: check off this option to consider case when comparing values, i.e. to distinguish between "Abc" and "ABC".
- Skip empty cells: select this option to ignore empty values in the key columns. If you leave this box unselected, the add-in will consider blanks as values and find all repeated rows.
Click Back to change the range and key columns, or Next to specify what to do.
Choose this option to delete all rows with the found values.
Use this option to clear the found values in the key columns without touching records in other columns, so that you can leave the size of the table intact.
Fill with color
Highlight the rows with the results in any color. You can select the color by clicking on it next to the option.
Add a status column
Mark all found values in a new column as "duplicates", "uniques", or any other type selected on step 2. You can sort on this column to group the results.
Copy to another location
Copy the rows with the found values to a new or existing worksheet. To choose where to place the results, pick the "Custom location" option and either select the top-left cell in the necessary sheet, or enter its address right in the field.
Move to another location
Move the rows with the found records: choose a new worksheet or a custom location for the destination. To pick an existing sheet, open it and select the top-left cell for the results, or enter its address into the "Custom location" field.
Click Back to change any settings, or click Finish to get the results.
Once the add-in finishes working, you will see the summary of the results. Click New Search to run the add-in again.