How to Remove Duplicates in Google Sheets (5 Methods)

Duplicate rows in a spreadsheet are more than annoying — they skew totals, break reports, and waste your time. If you have ever wondered how to remove duplicates in Google Sheets, the good news is there are several ways to do it, from a built-in menu option to powerful formulas and scripts. In this guide you will learn five proven methods so you can pick the one that fits your workflow, whether you are cleaning a quick contact list or processing thousands of imported CSV rows.

Key Takeaways:Google Sheets has a built-in Remove Duplicates tool under Data > Data cleanup that works in two clicksThe UNIQUE function creates a live, auto-updating copy of your data without duplicatesCOUNTIF helper columns let you flag and review duplicates before deleting anythingFor CSV files with 100K+ rows, SmoothSheet's CSV Duplicate Remover handles deduplication without browser slowdowns

Method 1: The Built-in Remove Duplicates Tool

Google Sheets added a native duplicate-removal feature in 2019 and it remains the fastest option for most users. It permanently deletes duplicate rows from your data in place, so make a copy of your sheet first if you want to keep the originals.

Step-by-step

1. Select the range that contains your data (or click any cell inside the range and Sheets will auto-detect it).
2. Go to Data > Data cleanup > Remove duplicates.
3. In the dialog, check "Data has header row" if your first row contains column titles.
4. Choose which columns to compare. By default all columns are selected, meaning a row must match on every column to count as a duplicate. Uncheck columns you want to ignore.
5. Click Remove duplicates. Google Sheets tells you how many rows were removed and how many unique rows remain.

This method is ideal when you need a quick, one-time cleanup. The downside is that it modifies your original data — there is no undo history beyond Ctrl+Z, and it does not update automatically when new data arrives.

When to use it

• One-time list cleaning (email lists, product catalogs, survey responses).
• Small to medium datasets (up to about 50,000 rows performs well).
• You want to permanently delete the duplicates, not just hide them.

Method 2: The UNIQUE Function

If you prefer a non-destructive, formula-based approach, the UNIQUE function is your best friend. It outputs a filtered copy of your data to a new location, leaving the source untouched. Even better, the result updates automatically whenever the source data changes.

Syntax

=UNIQUE(range, [by_column], [exactly_once])

• range — the data range to deduplicate (e.g., A2:D500).
• by_column — FALSE (default) removes duplicate rows; TRUE removes duplicate columns.
• exactly_once — FALSE (default) keeps the first occurrence; TRUE returns only rows that appear exactly once (no duplicates at all).

Example

Suppose your raw order data is in columns A through D. In a new sheet (or an empty column), enter:

=UNIQUE(Sheet1!A2:D500)

This spills a clean, duplicate-free version of the data automatically. If someone adds a new row to the source, the UNIQUE output updates on its own — no manual rerun needed.

Combining UNIQUE with SORT

Want your deduplicated data sorted by a specific column? Wrap the formula:

=SORT(UNIQUE(A2:D500), 3, TRUE)

This removes duplicates and sorts the result by column 3 in ascending order. You can also combine UNIQUE with the FILTER function to remove duplicates from a pre-filtered subset of your data.

When to use it

• You want a live, auto-updating deduplicated view.
• You need to keep the original data intact for audit or reference.
• You are building dashboards or reports that pull from a raw data sheet.

Method 3: Conditional Formatting to Highlight Duplicates

Sometimes you do not want to remove duplicates right away — you want to see them first. Conditional formatting lets you color-code duplicate cells so you can review them visually before deciding what to do.

Step-by-step

1. Select the column you want to check for duplicates (e.g., A2:A1000).
2. Go to Format > Conditional formatting.
3. Under "Format rules," choose Custom formula is.
4. Enter the formula: =COUNTIF($A$2:$A$1000, A2) > 1
5. Set a fill color (red or orange works well for spotting issues).
6. Click Done.

Every cell that appears more than once in the range will now be highlighted. You can then manually review and delete the rows you do not need, or sort by color to group duplicates together.

Multi-column duplicate highlighting

To highlight rows where a combination of columns is duplicated (e.g., same name AND same email), use a concatenation trick:

=COUNTIF($A$2:$A$1000&$B$2:$B$1000, A2&B2) > 1

Note: this is an array-style conditional format — it concatenates columns A and B for every row and checks if the combination appears more than once.

When to use it

• You want to visually audit duplicates before deleting anything.
• You need to decide on a case-by-case basis which copy to keep.
• You are working with a team and want everyone to see problem rows at a glance.

Method 4: COUNTIF to Flag Duplicates

A helper-column approach gives you more control than conditional formatting. You add a new column that counts how many times each value appears, then filter or delete based on that count. If you have used COUNTIF in Google Sheets before, this will feel familiar.

Step-by-step

1. Add a helper column next to your data (e.g., column E) with the header "Duplicate?".
2. In the first data row, enter: =COUNTIF($A$2:$A2, A2)
3. Drag the formula down the entire column. Each cell will show 1 for the first occurrence and 2, 3, etc. for subsequent duplicates.
4. Filter or sort the column. Any row showing a value greater than 1 is a duplicate.
5. Select and delete the duplicate rows, or use a filter view to hide them.

Why the expanding range matters

Notice the formula uses $A$2:$A2 — the start of the range is locked but the end expands as you drag down. This means the first occurrence always gets 1 and only later appearances get a higher count. It is a classic technique that makes it easy to keep the first instance of every value.

Checking multiple columns

To flag duplicates based on more than one column, concatenate:

=COUNTIF($A$2:$A2&$B$2:$B2, A2&B2)

Press Ctrl+Shift+Enter if you are on an older version of Sheets (newer versions handle array context automatically).

When to use it

• You need a sortable/filterable flag to work with duplicates programmatically.
• You want to keep the first occurrence and remove only subsequent copies.
• Your dataset has multiple key columns that define uniqueness.

Method 5: Google Apps Script for Bulk Removal

When your dataset is large (tens of thousands of rows or more), formulas can slow your sheet to a crawl. A short Apps Script can remove duplicates far faster because it processes data in memory rather than cell by cell.

The script

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var header = data.shift(); // keep the header row
  var unique = {};
  var output = [header];

  data.forEach(function(row) {
    var key = row.join('|');
    if (!unique[key]) {
      unique[key] = true;
      output.push(row);
    }
  });

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

  SpreadsheetApp.getUi().alert(
    'Removed ' + (data.length - output.length + 1) + ' duplicate rows. ' +
    output.length + ' unique rows remain.'
  );
}

How to use it

1. Open your Google Sheet and go to Extensions > Apps Script.
2. Delete any existing code and paste the script above.
3. Click the Save icon (or Ctrl+S).
4. Click Run. The first time, Google will ask you to authorize the script — follow the prompts.
5. Return to your sheet. A dialog will tell you how many duplicates were removed.

Customizing the key columns

The script above joins all columns into a key. To deduplicate by specific columns only (for example, columns A and C), change the key line to:

var key = row[0] + '|' + row[2]; // columns A and C (0-indexed)

When to use it

• Your dataset has 10,000+ rows and formulas are too slow.
• You need to run deduplication on a schedule (Apps Script supports time-based triggers).
• You want full control over which columns define a duplicate and what to do with removed rows.

Tip: If your data starts as a CSV or Excel file with 100K+ rows, consider running deduplication before importing. SmoothSheet's free CSV Duplicate Remover processes files entirely in your browser — no upload to a server, no row limits — and lets you download both the unique rows and the removed duplicates separately. For importing the cleaned file into Sheets without browser crashes, SmoothSheet's Google Sheets add-on handles server-side processing for just $9/month.

Which Method Should You Use?

Here is a quick comparison to help you choose:

For most everyday tasks, the built-in Remove Duplicates tool (Method 1) is the fastest choice. If you need a living, breathing deduplicated view, reach for the UNIQUE function (Method 2). And when you are dealing with massive CSV files before they even hit Google Sheets, the CSV Duplicate Remover or the CSV Validator can save you significant cleanup time.

FAQ

Does the Remove Duplicates tool delete the first occurrence or the copy?

It keeps the first occurrence and deletes subsequent duplicates. The row order is preserved — the topmost instance of each duplicate group stays in your sheet.

Can UNIQUE work across multiple sheets?

Yes. Reference another sheet in the range, like =UNIQUE(Sheet2!A2:D500). The result will update automatically whenever the source sheet changes, making it perfect for consolidating data from multiple tabs.

How do I remove duplicates based on one column only?

With the built-in tool, uncheck every column except the one you want to compare. With UNIQUE, there is no single-column parameter, but you can wrap it: =UNIQUE(A2:A500) for one column, or combine it with INDEX/MATCH to pull related columns. The COUNTIF helper-column method is often the most flexible for single-column deduplication.

What is the fastest way to deduplicate a CSV before importing to Google Sheets?

Use a dedicated tool like the CSV Duplicate Remover to process the file in your browser. It handles files with hundreds of thousands of rows without slowing down, and you can choose which columns to compare. Once cleaned, import the file with SmoothSheet's add-on for a crash-free upload.

Wrapping Up

Duplicate data does not have to derail your work. Whether you use the two-click Remove Duplicates tool, the dynamic UNIQUE function, or a custom Apps Script, Google Sheets gives you solid options for every scenario. For the cleanest workflow, deduplicate your CSV or Excel files before importing them — tools like SmoothSheet's CSV Duplicate Remover make that effortless, and importing the result with SmoothSheet means no browser freezes even on large files.

Pick the method that matches your situation, and enjoy cleaner data.