This article will demonstrate how to find and remove duplicates in Excel and Google Sheets.
The UNIQUE Function in Excel 365
Consider the list of data below:
In an empty cell to the right of the data, type in the following formula:
Only unique data will then be shown, with all duplicates removed. This function is also available in Google Sheets.
Duplicates and Conditional Formatting
Finding Duplicates with Conditional Formatting
To remove duplicates from data, you could also use conditional formatting.
Consider the following data:
- Select the data and format duplicate values. In the Ribbon, select Home > Styles > Conditional Formatting> Highlight Cells Rules > Duplicate Values…
2. Amend the format to show the duplicate values or leave it as the default – Light Red Fill with Dark Red Text.
3. Click OK. The duplicate values are shown in the data.
Delete Duplicates Found by Conditional Formatting
To remove duplicates shown by the Duplicate Conditional Formatting rule, you can delete each of the red values until no more duplicates are shown by either clearing the values, or deleting the rows.
Duplicates and Advanced Filter
Find Duplicates with Advanced Filter
We can use an advanced filter to extract unique values from a dataset.
- Click in the data you wish to filter, and then in the Ribbon, select Data > Sort & Filter > Advanced.
- Select either Filter the list, in place OR Copy to another location.
Then, make sure the List range is correct and select a cell to copy the filtered data to.
Finally, make sure Unique records only is ticked.
3. Click OK to extract the unique data.
Delete Duplicates with the Remove Duplicates Tool
Excel has a built in tool to Remove Duplicates.
- Click in the range of data and then, in the Ribbon, select Data > Data Tools > Remove Duplicates.
2. If you data has headers, ensure that the option is checked, and then select the columns to check for duplicates. In this example, there’s only one column to check.
3. Click OK to remove the duplicates.
How to Find and Remove Duplicates in Google Sheets
Built-in Clean-up Tool
The easiest way to remove duplicates in Google Sheets is to use Google’s built-in clean-up tools.
- Highlight your data in your Google Sheet, and then, in the Menu, select Data > Data clean-up > Remove Duplicates.
2. Ensure that the Data has header row is selected if you have a header row, and then select the Columns to analyze.
3. Click Remove duplicates.
Other Methods in Google Sheets
You can also use the UNIQUE function in Google sheets to identify duplicates.
For further information about finding and removing duplicates in Excel and Google Sheets, have a look at the table below: