How to Deduplicate Email Lists in Google Sheets: Formulas, Apps Script and Add-Ons

Why Deduplication Matters

Duplicate email addresses in your spreadsheets cause real problems:

Method 1: Remove Duplicates (Built-In Feature)

Google Sheets has a built-in tool for removing duplicates. This is the simplest method:

Method 2: Highlight Duplicates with Conditional Formatting

If you want to see duplicates before removing them:

This highlights all cells that appear more than once. You can then manually review and delete the duplicates you want to remove.

Method 3: UNIQUE Function

The UNIQUE function extracts a deduplicated list into a new column or sheet:

=UNIQUE(A2:A1000)

Method 4: COUNTIF to Flag Duplicates

Use COUNTIF in a helper column to identify which rows are duplicates:

In cell B2 (assuming emails are in column A):
=COUNTIF($A$2:$A2, A2)

Then filter column B to show only values greater than 1, and delete those rows.

Checking for duplicates across two lists

If you have one list in Sheet1!A:A and want to check for duplicates against Sheet2!A:A:

In Sheet1, cell B2:
=COUNTIF(Sheet2!$A:$A, A2)

Method 5: QUERY Function

For more control over deduplication, use QUERY:

=QUERY(A2:C1000,
  "SELECT A, B, C
   GROUP BY A, B, C", 1)

This groups by all columns and returns unique rows.

QUERY for email-only deduplication keeping first occurrence data

=QUERY(A2:C1000,
  "SELECT A, MIN(B), MIN(C)
   WHERE A IS NOT NULL
   GROUP BY A
   LABEL MIN(B) 'Name', MIN(C) 'Source'", 1)

Method 6: Apps Script for Advanced Deduplication

For large lists or complex deduplication logic, use Google Apps Script:

function deduplicateEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var header = data[0];
  var emailCol = 0; // Column A (0-indexed)

  // Track seen emails (case-insensitive)
  var seen = {};
  var uniqueRows = [header];
  var duplicateCount = 0;

  for (var i = 1; i < data.length; i++) {
    var email = String(data[i][emailCol])
      .toLowerCase()
      .trim();

    if (email === '' || email === 'undefined') {
      continue;
    }

    if (!seen[email]) {
      seen[email] = true;
      uniqueRows.push(data[i]);
    } else {
      duplicateCount++;
    }
  }

  // Write results to a new sheet
  var newSheet = SpreadsheetApp.getActiveSpreadsheet()
    .insertSheet('Deduplicated');
  newSheet
    .getRange(1, 1, uniqueRows.length, header.length)
    .setValues(uniqueRows);

  SpreadsheetApp.getUi().alert(
    'Removed ' + duplicateCount + ' duplicates. ' +
    'Results in "Deduplicated" sheet.'
  );
}

How to use

Advanced: normalise before deduplication

function normaliseAndDeduplicate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var header = data[0];
  var emailCol = 0;

  var seen = {};
  var uniqueRows = [header];
  var duplicateCount = 0;

  for (var i = 1; i < data.length; i++) {
    var rawEmail = String(data[i][emailCol]);
    var email = rawEmail
      .toLowerCase()
      .trim()
      .replace(/\s+/g, '');  // Remove spaces

    // Skip empty
    if (!email || email === 'undefined') {
      continue;
    }

    // Basic format check
    if (email.indexOf('@') === -1) {
      continue;
    }

    // Normalise the row's email value
    data[i][emailCol] = email;

    if (!seen[email]) {
      seen[email] = true;
      uniqueRows.push(data[i]);
    } else {
      duplicateCount++;
    }
  }

  var newSheet = SpreadsheetApp.getActiveSpreadsheet()
    .insertSheet('Normalised');
  newSheet
    .getRange(1, 1, uniqueRows.length, header.length)
    .setValues(uniqueRows);

  SpreadsheetApp.getUi().alert(
    'Normalised and removed ' + duplicateCount +
    ' duplicates.'
  );
}

Method 7: Google Sheets Add-Ons

Method Comparison

When to Use a Dedicated Tool

Google Sheets works well for deduplicating email lists up to around 50,000-100,000 rows. For larger lists, or when you need to extract emails from non-spreadsheet formats (PDF, DOCX, HTML, EML), upload your files to Email Extractor. It handles case-insensitive deduplication automatically across all uploaded files and works with 19 file types, not just spreadsheets.

Related Guides

• Google Sheets Email Management
• Removing Duplicates at Scale
• How Email Deduplication Works
• Spreadsheet Data Cleaning Techniques
• Normalising Email Formats