How to Remove Duplicate Rows in Google Sheets: 5 Methods Compared

Duplicate rows are one of the most common data quality issues in Google Sheets. Whether you're managing a CRM, tracking inventory, or processing form responses, duplicates creep in and cause real problems — double-counted revenue, duplicate emails sent to customers, and inaccurate reports.

This guide covers 5 battle-tested methods to detect, highlight, and remove duplicate rows, from Google's built-in tools to fully automated solutions.

Why Duplicates Are Dangerous

Duplicates don't just waste space. They actively cause problems:

• Inflated metrics: Revenue, counts, and averages will be wrong if rows are counted twice.
• Double communications: If your sheet feeds into an email tool, customers get duplicate messages.
• Wasted resources: Duplicate orders can lead to double shipments or payments.
• Broken lookups: VLOOKUP and INDEX/MATCH may return wrong values when duplicates exist.
• Compliance risks: In regulated industries, duplicate records can cause audit failures.

Method 1: Built-in Remove Duplicates Tool

Google Sheets has a native "Remove duplicates" feature that works well for simple cases.

Steps:

1. Select your data range (or click any cell in the range).
2. Go to Data → Data cleanup → Remove duplicates.
3. Check which columns to analyze for duplicates.
4. Check "Data has header row" if applicable.
5. Click "Remove duplicates".
6. Google Sheets will tell you how many duplicates were removed.

Best for: Quick, one-time cleanup on small to medium datasets.
Limitation: Destructive — no preview. You can't choose which copy to keep. Always keeps the first occurrence.

Method 2: UNIQUE Function (Non-Destructive)

The UNIQUE function creates a new list of unique values without modifying your original data.

Formula:

=UNIQUE(A2:D100)

Place this formula in a new sheet or an empty area. It will output all unique rows from the range A2:D100.

Advanced: Find Duplicates First

To identify which rows are duplicates before removing them, add a helper column with this formula:

=IF(COUNTIF($A$2:$A2, A2) > 1, "DUPLICATE", "UNIQUE")

This formula checks if the current value in Column A has appeared before. Drag it down for all rows to flag duplicates.

Best for: When you want to preview duplicates without changing your original data.
Limitation: Doesn't delete duplicates — just creates a separate unique list. You still need to manually clean up.

Method 3: COUNTIF + Filter (Visual Detection)

This method helps you see duplicates before deciding what to do with them.

Steps:

1. Add a helper column (e.g., Column E) with the header "Duplicate?".
2. In cell E2, enter: =COUNTIF(A:A, A2)
3. Drag the formula down for all rows.
4. Any cell showing a number greater than 1 means that value has duplicates.
5. Use Data → Create a filter on Column E.
6. Filter to show only values greater than 1.
7. Review and manually delete the duplicate rows you don't need.

Best for: When you need to review duplicates before deleting and want to keep specific copies.
Limitation: Requires manual formula setup and cleanup. Gets complex with multiple columns.

Method 4: Google Apps Script

For programmatic duplicate removal:

function removeDuplicateRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var seen = {};
  var rowsToDelete = [];

  for (var i = data.length - 1; i >= 1; i--) { // Skip header
    var key = data[i].join('||');
    if (seen[key]) {
      rowsToDelete.push(i + 1);
    } else {
      seen[key] = true;
    }
  }

  // Delete from bottom up
  rowsToDelete.forEach(function(row) {
    sheet.deleteRow(row);
  });

  SpreadsheetApp.getUi().alert(
    'Removed ' + rowsToDelete.length + ' duplicate rows.'
  );
}

Best for: Technical users handling large datasets.
Limitation: No preview, no undo. Requires coding skills and script authorization.

Method 5: CleanSheet Add-on (Best for Ongoing Cleanup)

For teams that deal with duplicates regularly, CleanSheet turns duplicate detection into an automated, ongoing process.

Steps:

1. Install CleanSheet from the Google Workspace Marketplace.
2. Open CleanSheet from the Extensions menu.
3. Create a new rule:
   • Select your sheet and the column to check for duplicates
   • Set condition to "Is Duplicate"
   • Choose action: "Highlight" (to review first) or "Delete"
4. Enable "Keep First Duplicate" to preserve the original row and only remove copies.
5. Click "Preview" to see which rows will be affected.
6. Click "Run" to execute.

Automate Ongoing Duplicate Prevention

Set your duplicate detection rule to run on a schedule (e.g., every 6 hours). This way, new duplicates from form submissions or data imports are caught automatically before they cause problems.

Method Comparison

Frequently Asked Questions

How do I remove duplicates in Google Sheets without losing data?

Use CleanSheet's "Keep First Duplicate" option. It detects all duplicate rows but only deletes the copies, keeping the first (original) occurrence of each value intact. You can also use the UNIQUE function to create a separate deduplicated list without touching your original data.

Can I find duplicates across multiple columns?

Yes. Google Sheets' built-in "Remove duplicates" tool can check multiple columns. CleanSheet also lets you combine multiple conditions with AND/OR logic to detect duplicates based on any combination of columns.

What's the difference between UNIQUE and Remove Duplicates?

The UNIQUE function creates a new list of unique values in a separate location — it doesn't modify your original data. The built-in "Remove duplicates" feature directly deletes duplicate rows from your data. Use UNIQUE for non-destructive previews and "Remove duplicates" when you're ready to clean.

How often should I check for duplicates?

It depends on how often your data changes. For sheets connected to Google Forms or import feeds, daily or even hourly checks make sense. With CleanSheet's scheduled auto-run, you can set this up once and forget about it.