Translate

Find and Remove Duplicate Rows in Google Sheets

How to Find and Remove Duplicate Rows in Google Sheets

Duplicate data is a common problem in any spreadsheet—especially when working with large datasets, imported lists, survey results, or manually entered records. Duplicate rows can cause inaccurate calculations, reporting errors, and confusion for collaborators.

In this guide, we’ll show you how to find and remove duplicate rows in Google Sheets using multiple methods, both manual and automated. Whether you want to delete duplicates, highlight them, or extract unique entries, this step-by-step article has you covered.

Why Duplicates Happen in Google Sheets

Duplicate rows may appear in your data due to:

  • Manual copy-paste errors

  • Merged spreadsheets

  • Form responses with repeated submissions

  • Appended data from external sources

  • API integrations adding redundant data

Regardless of the cause, identifying and removing these rows is critical for clean and reliable data.

Method 1: Use Google Sheets’ Built-in “Remove Duplicates” Tool

This is the fastest and simplest method.

Step-by-Step:

  1. Open your Google Sheet.

  2. Select the range of rows and columns you want to check.

  3. Click Data > Data cleanup > Remove duplicates.

  4. In the popup:

    • Check “Data has header row” if your selection includes headers.

    • Choose the columns to check for duplicates.

  5. Click “Remove duplicates.”

  6. Google Sheets will show how many duplicate rows were found and removed.

Use Case:

  • Ideal for small to medium datasets.

  • Quick cleanup after importing or copying data.

Method 2: Highlight Duplicates Using Conditional Formatting

If you want to review duplicates before removing them, highlighting is a safer option.

Step-by-Step:

  1. Select the column or range you want to check for duplicates.

  2. Click Format > Conditional formatting.

  3. Under “Format cells if”, select “Custom formula is”.

  4. Enter this formula (assume data starts in A2):

    =COUNTIF(A:A, A2) > 1

  5. Choose a background color (e.g., red or yellow).

  6. Click “Done.”

Now, all duplicates in that column will be highlighted.

Modify for Entire Rows:

If you want to highlight rows where the full row is duplicated:

  1. Combine the row’s values into a helper column using:

    =A2&B2&C2&D2

  2. Apply the same COUNTIF formula to the helper column.

Method 3: Use the UNIQUE Function to Extract Only Unique Rows

The UNIQUE function automatically removes duplicates and shows only the first occurrence of each unique row.

Step-by-Step:

  1. In a new sheet or empty area, enter:

    =UNIQUE(A2:D)

    Replace A2:D with your actual data range.

  2. This will return a list of unique rows.

Benefits:

  • Keeps your original data intact.

  • Good for generating summary tables.

  • Works dynamically if new data is added.

Method 4: Use Google Apps Script to Automatically Remove Duplicates

For more control or to schedule duplicate removal, use a custom script.

Script Example: Remove Duplicate Rows Based on All Columns

  1. Click Extensions > Apps Script.

  2. Delete the default code and paste:

function removeDuplicatesFromSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  var uniqueData = [];
  var stringifiedRows = new Set();
  
  for (var i = 0; i < data.length; i++) {
    var rowString = JSON.stringify(data[i]);
    if (!stringifiedRows.has(rowString)) {
      uniqueData.push(data[i]);
      stringifiedRows.add(rowString);
    }
  }

  sheet.clearContents();
  sheet.getRange(1, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
}

  1. Save and click Run to remove duplicate rows.

  2. Authorize the script on first run.

Use Case:

  • Good for automated cleanup.

  • Handles large datasets with multiple duplicate rows.

Method 5: Filter Duplicates Using Pivot Tables

Pivot tables can help you count how many times a row or value appears.

Steps:

  1. Select your data and click Insert > Pivot table.

  2. Set the pivot to appear in a new sheet.

  3. Add the column(s) you want to check to Rows.

  4. Add the same column to Values, using the COUNTA function.

  5. Sort by count > 1 to find duplicates.

This method is more analytical and helps you understand how many duplicates exist, without deleting anything.

Method 6: Use Add-ons Like “Remove Duplicates” by Ablebits

If you work with spreadsheets frequently, you may want to install an add-on.

  1. Go to Extensions > Add-ons > Get add-ons.

  2. Search for Remove Duplicates.

  3. Install the add-on.

  4. Launch it and follow the step-by-step wizard.

These tools often include advanced features like:

  • Partial duplicate matching

  • Fuzzy matching (typos)

  • Multi-column selection

  • Data backup before removal

Best Practices to Avoid Duplicates in the Future

  • Use data validation to restrict repeated entries.

  • Lock key columns to prevent accidental copy-paste errors.

  • Use Google Forms with email collection to prevent multiple submissions.

  • Use filters or query functions to work with clean views.

  • Create automated workflows using Google Apps Script.

Conclusion

Duplicate data may seem harmless, but it can quietly disrupt calculations, reports, and business decisions. Google Sheets offers multiple powerful methods—from built-in tools and formulas to custom scripts and third-party add-ons—for finding, highlighting, and removing duplicate rows.

By following the right method based on your dataset size and complexity, you can keep your spreadsheets clean, efficient, and trustworthy.