/ How to

How to Identify Duplicate Entries in Google Sheets Spreadsheets

Working with large datasets in Google Sheets often leads to duplicate entries creeping in, which can skew analysis and reporting. Hence, it’s crucial to identify and remove these duplicate records.

This comprehensive guide will teach you different methods to find duplicate entries in your Google Sheets, using formulas as well as addons.

Why Duplicate Entries Matter

Duplicate entries refer to two or more rows having the same data values in selected columns. For instance, two rows with the same email address or phone number.

These duplicated records can negatively impact:

  • Data analysis: Duplicate entries can skew metrics like counts, averages, percentages etc. Giving incorrect insights.
  • Storage costs: Extra rows mean extra storage needs and costs on Google servers.
  • Performance: Large datasets with many duplicates slow down processing and functions.

Hence it’s essential to clean your data by identifying and eliminating duplicates.

Finding Duplicates in a Single Column

The simplest case is when you need to check a single column for duplicate entries.

For example, to find duplicates in the Email column:

Using Conditional Formatting

  1. Select the column to check, like A:A.
  2. Go to Format > Conditional Formatting.
  3. Click on “Custom formula is” and enter:
=COUNTIF($A:$A,A1)>1
  1. Select formatting like color fill or text color.
  2. The duplicate values get automatically highlighted.

Highlight Duplicates in Google Sheets

This conditional formatting formula checks each cell against the entire column and highlights cells appearing more than once.

Using the UNIQUE Formula

Another way is to use the UNIQUE() formula:

  1. Select an empty column beside the data.
  2. Enter =UNIQUE(A:A) in cell B1.
  3. It will list all unique values in column A, any blanks indicate duplicated rows.

The UNIQUE() formula removes duplicates and provides a list of distinct values.

Finding Duplicates Across Multiple Columns

To check duplicates across first name, last name, and email columns:

Using Conditional Formatting

  1. Select data range, like A2:C100.
  2. Go to Format > Conditional Formatting.
  3. Click on “Custom formula is” and enter: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1
  4. The duplicate rows get automatically highlighted.

This custom formula uses COUNTIFS() instead of COUNTIF(), to check the combination of first name, last name, and email values across each row.

Using the Remove Duplicates Tool

Google Sheets also has an inbuilt Remove Duplicates tool to identify and delete duplicates:

  1. Click on any cell in the data range.
  2. Go to Data > Data Cleanup > Remove Duplicates.
  3. Select columns to check.
  4. Click on display duplicates.

This will showcase all duplicate rows, which you can then manually delete as needed.

Remove Duplicates in Google Sheets

The Remove Duplicates tool is useful for analyzing the duplicate entries before deleting.

Identifying Duplicate Rows in Large Datasets

The methods above work for smaller datasets, but Google Sheets bogs down beyond 10,000 – 50,000 rows.

Some solutions for large data include:

Google Apps Script

Custom Google Apps Scripts can apply powerful processing even for millions of rows. Scripts can rapidly compare all values across columns to highlight duplicates.

For example:

function findDuplicates() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();

  var duplicateRows = [];

  for(var i=0; i<data.length; i++){
    for(var j=i+1; j<data.length; j++){
      if(data[i][0] === data[j][0] && 
         data[i][1] === data[j][1]) {
           duplicateRows.push(i+1);  
      } 
    }
  }

  if(duplicateRows.length > 0){
    sheet.getRange(duplicateRows[0], 1, duplicateRows.length).activate();
    SpreadsheetApp.getUi().alert('Duplicate rows:' + duplicateRows); 
  } else {
    SpreadsheetApp.getUi().alert('No duplicates found!');
  }

}

This script compares all rows, then highlights and alerts the duplicate ones.

External Tools

Third-party addons like Remove Duplicates and Duplicate Detector can efficiently handle large datasets of over 50,000 rows in Google Sheets, including:

  • One-click duplicate identification.
  • Options to highlight or delete duplicates.
  • Merging duplicate rows while retaining data.
  • Detailed duplicate analysis reports.

These tools are ideal to clean big datasets.

Google BigQuery

For data exceeding 500,000 rows, Google recommends BigQuery – a serverless enterprise data warehouse. It can efficiently store, process, and analyze massive datasets.

Advanced SQL queries in BigQuery can identify intricate duplicate patterns in billion-row tables in just seconds. Far faster than Google Sheets.

In Summation

Duplicate entries can severely impact the quality of your Google Sheet data. Small datasets can rely on Conditional Formatting and Remove Duplicates.

But large datasets require scripting or specialized addons to efficiently clean duplicates. For big data, Google BigQuery is the best solution.

With robust duplicate identification methods in place, you can keep your Google Sheet data clean and accurate for reliable reporting.

Related Posts