How to Remove Duplicates in Google Sheets

You don't need to dedupe line by line

What to Know

  • Remove Duplicates: Select cell range/columns to search > Data > Data Cleanup > Remove Duplicates.
  • UNIQUE function: Select first cell in column for unique returns > Insert > Function > Filter > Unique.
  • Next: Select search range > press Enter > Sheets will create new column/table of unique entries.

This article explains how to find and remove duplicate entries in Google Sheets using built-in functions or third-party add-ons.

Find Duplicates in Google Sheets Using the Remove Duplicates Tool

Google Sheets includes a built-in tool that will search the sheet based on the criteria you provide.

When you use the Remove Duplicates tool, it'll automatically remove the duplicates it finds. You won't have the opportunity to view them first. If you prefer to review them before Google Sheets removes them, you may want to try highlighting duplicates in Google Sheets instead.

Two men going over a paper with laptops open
 Helloquence / Unsplash
  1. Open the spreadsheet in which you want to find and remove duplicate data.

  2. Select the data range in which you’d like to remove duplicates.

    Cells with identical values, but different formatting, formulas, or letter cases are considered duplicates.

  3. Select Data > Data Cleanup > Remove Duplicates. The Remove Duplicates dialog box will open showing the number of columns and rows selected.

    The Remove duplicates command in Google Sheets
  4. Select the Data has header row checkbox if your spreadsheet includes a header row to prevent this row from being affected.

    The "Data has header row" option in the Remove Duplicates window
  5. Choose the columns you want the tool to analyze by selecting the corresponding checkboxes. To analyze all columns, select the Select All checkbox.

    If you select a single cell within a range, duplicates will be removed for the whole range.

    The "Select All" option and a column setting in the Remove Duplicates dialogue box of Google Sheets
  6. Select Remove Duplicates. The tool will analyze the data, and a box will appear showing how many duplicate rows Google Sheets found and removed.

    The "Remove duplicates" button

Find Duplicates in Google Sheets Using the UNIQUE Function

The UNIQUE function in Google Sheets returns unique rows from the provided source range, discarding duplicates. The UNIQUE function returns rows in the order they first appear in the source range. Using this method, you are able to view and compare both sets of data before deleting the original duplicates.

Ensure numeric values are formatted in the same way, such as percentages or currency values.

  1. Select the first cell in the column in which you want to unique rows returned.

  2. Select Insert > Function.

    Function on the Insert menu
  3. Select Filter > Unique.

    Unique on Filter menu
  4. Enter or select the range you want to use as the argument for the function and press Enter.

    UNIQUE function argument and results
  5. Google Sheets will create a new column or table with only unique rows included.

    If returned rows appear to include duplicates, look for cells that have hidden text or other differences, such as trailing spaces.

Find and Remove Duplicates in Google Sheets Using an Add-on

Add-ons are tools created by third-party developers that improve or simplify your experience using Google Sheets. Add-ons are available that will find and remove duplicates from your spreadsheet in Google Sheets.

While no two add-ons are identical, many include similar features. Learn how to locate add-ons that find and remove duplicates in Google Sheets and how to decide which one best suits your needs.

  1. Go to Google Sheets.

  2. Select Add-ons > Get Add-ons. The Add-ons window will open with several suggested tools.

    Add-ons menu
  3. Type "remove duplicates" into the Search add-ons field and press Enter. A list of potential add-ons matching your search will appear.

    Add-ons window with "remove duplicates" in the search box
  4. Select an add-on you want to learn more about to open the description page. Scroll down to read reviews or scroll right and left to view screenshots and videos displaying the add-on in action.

    Screenshot of Remove Duplicates add-on description
  5. Select Add.

    Free button on Add-ons page

    The Add button might have a price or the word "Free" on it.

  6. Select or sign in to your Google account if prompted.

  7. Select Allow to provide the requested permissions to the add-on.

    Allow button
  8. Wait while the add-on is installed.

  9. Select Add-ons to access and use the tool.

    Remove Duplicates add-on in Add-ons menu

    Select Help in the add-on menu to get help using the tool you have downloaded.

Was this page helpful?