How to Find and Remove Duplicates in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to find and remove duplicates in Excel and Google Sheets.
In this Article
When you have a sheet of data in Excel that contains repetitive data, there are several methods you can use to find and remove duplicate values from your data. This tutorial covers four methods: the UNIQUE Function, conditional formatting with autofilter, advanced filter, and the remove duplicates data tool.
Isolate Non-Duplicates: The UNIQUE Function
To find and extract unique values from this data, you can use the UNIQUE Function. This is only available in Excel 365. Consider the list of data below:
In an empty cell to the right of the data, type in the formula:
=UNIQUE(B3:B12)
Only unique data is shown in Column D, with all duplicates removed. The original list remains in Column B.
Conditional Formatting
To remove duplicates from data, you could also use conditional formatting. Consider the following data:
Identify and Format Duplicates: Highlight Cells
- Select the data with duplicates. In the Ribbon, go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values… to format duplicate values.
- Change the format to show the duplicate values or leave the default (Light Red Fill with Dark Red Text).
- Click OK. The duplicate values are shown in the data.
Delete Formatted Duplicates
To remove duplicates shown by the duplicate values 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.
Note: Don’t delete multiple values at once; you might end up deleting a distinct item.
Exclude Duplicates: Advanced Filter
You can use an advanced filter to extract unique values from a dataset.
- Select any cell in the data where you want to get rid of duplicates, and then in the Ribbon, go to Data > Sort & Filter > Advanced.
- Choose whether to Filter the list, in place or Copy to another location.
- Then, make sure the List range is correct and select the cell where you want to paste the filtered data.
- Finally, make sure Unique records only is ticked.
- Click OK to extract the unique data.
Remove Duplicates Data Tool
Excel has a built-in tool to remove duplicates.
- Click in the range of data and then, in the Ribbon, go to Data > Data Tools > Remove Duplicates.
- If your 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.
- Click OK to remove the duplicates.
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.
- Select the data range in your Google spreadsheet.
- In the Menu, go to Data > Data clean-up > Remove Duplicates.
- Tick the Data has header row checkbox if you have a header row.
- Tick both checkboxes under Columns to analyze.
- Click Remove duplicates. A pop-up message tells you how many unique and duplicate rows were in the original data.
The UNIQUE Function in Google Sheets
You can also use the UNIQUE Function in Google Sheets to remove duplicates.
For further information about finding and removing duplicates in Excel and Google Sheets, have a look at the tutorials below.
