How to Find and Remove Duplicates in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on September 7, 2023

This tutorial demonstrates how to find and remove duplicates in Excel and Google Sheets.

remove duplicates intro


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:

remove duplicates list

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


remove duplicates unique

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:

remove duplicates cond format list

Identify and Format Duplicates: Highlight Cells

  1. Select the data with duplicates. In the Ribbon, go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values… to format duplicate values.

remove duplicates cond format dups

  1. Change the format to show the duplicate values or leave the default (Light Red Fill with Dark Red Text).

remove duplicates cond format remove dups

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

remove duplicates list dups gone
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.

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

remove duplicates ribbon filter advanced

  1. Choose whether to Filter the list, in place or Copy to another location.
  2. Then, make sure the List range is correct and select the cell where you want to paste the filtered data.
  3. Finally, make sure Unique records only is ticked.

remove duplicates filter advanced

  1. Click OK to extract the unique data.

remove duplicates filtered

Remove Duplicates Data Tool

Excel has a built-in tool to remove duplicates.

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

remove duplicates ribbon data

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

remove duplicates select

  1. Click OK to remove the duplicates.

remove duplicates duplicates removed

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. Select the data range in your Google spreadsheet.
  2. In the Menu, go to Data > Data clean-up > Remove Duplicates.

remove duplicates gs menu data

  1. Tick the Data has header row checkbox if you have a header row.
  2. Tick both checkboxes under Columns to analyze.

find duplicates gs select

  1. Click Remove duplicates. A pop-up message tells you how many unique and duplicate rows were in the original data.

find duplicates gs msg

The UNIQUE Function in Google Sheets

You can also use the UNIQUE Function in Google Sheets to remove duplicates.

remove duplicates gs formula

For further information about finding and removing duplicates in Excel and Google Sheets, have a look at the tutorials 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
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
Advanced Filter
Filter Duplicate Values
Filter Unique Values
Find & Selectyes
Find Unique Values
Count Unique Values in a Range
Sum By Category or Group
UNIQUE Function Examples
VLOOKUP Display Multiple Matches
VLOOKUP Duplicate Values
XLOOKUP Duplicate Values
Organize Sheetsyes
Pull Data From Multiple Sheets and Consolidate
Sort Datayes
Sort Without Duplicates
Percent Frequency Distribution
VBA Advanced Filter
VBA Conditional Formatting Highlight Duplicates in a Range
VBA Populate Array with Unique Values from Column
VBA Removing Duplicate Values

AI Formula Generator

Try for Free

See all How-To Articles