See all How-To Articles

How to Find and Remove Duplicates in Excel & Google Sheets

This article will demonstrate how to find and remove duplicates in Excel and Google Sheets.

findduplicates-intro

 

The UNIQUE Function in Excel 365

To find and extract unique values from this data, we can use the UNIQUE Function. This is only available in Excel 365.

Consider the list of data below:

findduplicates data

In an empty cell to the right of the data, type in the following formula:

=UNIQUE(B3:B12)

findduplicates unique

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:

find duplicates conditional data

  1. Select the data and format duplicate values. In the Ribbon, select Home > Styles > Conditional Formatting> Highlight Cells Rules > Duplicate Values…

find duplidates ribbon

2. Amend the format to show the duplicate values or leave it as the default – Light Red Fill with Dark Red Text.

find duplicates duplicate values

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.

findduplicates remove duplicates

Duplicates and Advanced Filter

Find Duplicates with Advanced Filter

We can use an advanced filter to extract unique values from a dataset.

  1. Click in the data you wish to filter, and then in the Ribbon, select Data > Sort & Filter > Advanced.

 

findduplicates ribbon filter

 

  1. 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.

 

findduplicates filter advanced

3. Click OK to extract the unique data.

findduplicates filtered

Remove Duplicates

Delete Duplicates with the Remove Duplicates Tool

Excel has a built in tool to Remove Duplicates.

  1. Click in the range of data and then, in the Ribbon, select Data > Data Tools > Remove Duplicates.

find duplicates ribbon 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.

 

find duplicates removeduplicates

 

3. Click OK to remove the duplicates.

find duplicates duplicates removed

 

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.

  1. Highlight your data in your Google Sheet, and then, in the Menu, select Data > Data clean-up > Remove Duplicates.

findduplicates gs 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.

findduplicates gs select

3. Click Remove duplicates.

findduplicates gs msg

 

Other Methods in Google Sheets

You can also use the UNIQUE function in Google sheets to identify duplicates.

findduplicates gs unique

 

 

For further information about finding and removing duplicates in Excel and Google Sheets, have a look  at the table below:

Find, Remove, or Highlight Duplicates 
Conditional Formattingyes
Highlight Duplicate Rows
Highlight Duplicate Values
Copy & Pasteyes
Paste Unique Values
Drop-Down Listyes
Alphabetize a Drop-Down List
Data Validation Unique Values
Duplicatesyes
Clear Duplicate Cells
Combine Duplicate Rows
Count Duplicate Values Only Once
Distinct (Unique) Values
Find Duplicate Values
Merge Lists Without Duplicates
Prevent Duplicate Entries
Remove Both Duplicates
Remove Duplicate Rows
Remove Unique Values
Show Only Duplicates
Filtersyes
Advanced Filter
Filter Duplicate Values
Filter Unique Values
Find & Selectyes
Find Unique Values
Formulasyes
Count Unique Values in a Range
Sum By Category or Group
UNIQUE Function Examples
VLOOKUP CONCATENATE
VLOOKUP Display Multiple Matches
VLOOKUP Duplicate Values
XLOOKUP Duplicate Values
Organize Sheetsyes
Pull Data From Multiple Sheets and Consolidate
Sort Datayes
Sort Without Duplicates
Statisticsyes
Percent Frequency Distribution
VBAyes
VBA Advanced Filter
VBA Conditional Formatting Highlight Duplicates in a Range
VBA Populate Array with Unique Values from Column
VBA Removing Duplicate Values