How to Remove Duplicates in Google Sheets

You don't need to dedupe line by line

Two men going over a paper with laptops open

Helloquence/Unsplash

Duplicate entries might be added when new data is imported into a spreadsheet or by simple human data entry error. Rather than manually checking each line trying to detect information added multiple times, let the application remove duplicates. In Google Sheets you can use a built-in tool, a function, or a third-party add-in to remove duplicated data. Keep reading to learn how to remove duplicates in Google Sheets.

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, any duplicates found will be removed automatically. You won't have the opportunity to view those found before they're removed.

  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 > Remove Duplicates. The Remove Duplicates dialog box will open showing the number of columns and rows selected.

    Screenshot of Remove Duplicates on the Data tab
  4. Select the Data has header row checkbox if your spreadsheet data includes a header row to prevent this row from being affected.

    Screenshot of Remove Duplicates dialog box
  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.

  6. Select Remove Duplicates. The tool will analyze the data and a box will appear showing how many duplicate rows were found and removed.

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.

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

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

    Screenshot showing 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.

    Screenshot of 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.

    Screenshot of Add-ons window with
  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.

    Screenshot of 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.

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

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

    Screenshot of Remove Duplicates add-on in Add-ons menu

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