Find and Remove Duplicates in Google Sheets Easily

Duplicate entries in your spreadsheets can lead to skewed data, errors in analysis, and a general inefficiency in managing your information. Whether you're an analyst, a marketer, or just someone who loves keeping things tidy, finding and removing duplicates in Google Sheets can be a pivotal task for maintaining data accuracy. In this comprehensive guide, we'll walk through the steps to locate and eliminate duplicate entries, ensuring your datasets are as clean as they can be.

Understanding Duplicates in Google Sheets

Before we dive into the process, let's clarify what we mean by duplicates. In Google Sheets, duplicates are entries where:

• Identical values occur across multiple rows.
• You have the same information in different columns or combinations of columns.

Duplicates can arise from human error, data import issues, or sometimes from intentional data copying.

Methods to Find Duplicates

Conditional Formatting

Conditional formatting is a simple yet powerful way to visually identify duplicates. Here’s how you can do it:

1. Select the range of cells you want to check for duplicates.
2. Go to Format > Conditional formatting.
3. In the sidebar, under "Format rules," click on "Format cells if..." and choose "Custom formula is."
4. Type in the formula: =COUNTIF(A:A,A1)>1 assuming your data starts from column A.
5. Set the formatting style to highlight these cells, typically with a background color or bold text.
6. Click "Done" to apply.

⚠️ Note: This method only highlights duplicates but does not remove them. It's great for visual identification.

Using Filter

Google Sheets offers a built-in feature to filter for unique values, which can indirectly help you spot duplicates:

1. Select your data range.
2. Click on Data > Create a filter.
3. In any column header dropdown, select "Filter by condition" then choose "Is unique" or "Is not unique".
4. To display only the duplicates, choose "Is not unique."

Utilizing Google Sheets Functions

If you need to programmatically find duplicates, consider using the following functions:

• UNIQUE: Extracts unique values from a range, leaving out duplicates.
• COUNTIF: Counts how many times a value appears in a range. If the count exceeds 1, you have a duplicate.
• FILTER: Filters data based on criteria, which can include looking for duplicates.

Here's a simple example of using these functions:

=UNIQUE(A2:A) 
=COUNTIF(A2:A,A2) 

How to Remove Duplicates

Using Google Sheets Data Cleanup

Google Sheets provides an intuitive way to remove duplicates with the following steps:

1. Select the range or column where you want to remove duplicates.
2. Go to Data > Data cleanup > Remove duplicates.
3. In the popup window, check the columns you want to analyze for duplicates.
4. Click Remove duplicates. Google Sheets will notify you how many duplicates have been deleted.

✅ Note: Ensure you have a backup of your data before removing duplicates to avoid accidental data loss.

Using Formulas for Complex Duplicate Removal

If your data has complex criteria for what constitutes a duplicate, you can use an array formula like this:

={FILTER(A2:B,A2:A&" "&B2:B<>UNIQUE(A2:A&" "&B2:B)) ;}

This formula concatenates values from columns A and B to create a unique identifier for each row, then filters out the rows where this identifier isn't unique.

Automate with Google Apps Script

For those who are comfortable with scripting, here’s a simple script that can find and remove duplicates:

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  var unique = {};
  var uniqueData = [];
  
  for(var i = 0; i < data.length; i++) {
    var key = data[i].join('|');
    if(!unique[key]) {
      unique[key] = true;
      uniqueData.push(data[i]);
    }
  }
  
  sheet.getRange(1, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
}

Wrapping Up

Now you have multiple ways to identify, highlight, and remove duplicates in Google Sheets. From using conditional formatting to spot duplicates visually, to applying formulas or scripts for a more nuanced approach, you're equipped to keep your spreadsheets clean and your data accurate. Whether you're working with small datasets or managing large inventories, these methods can save you time and ensure your analysis is based on reliable, duplicate-free data. Remember, regular maintenance of your spreadsheets not only enhances productivity but also preserves the integrity of your work.

Can I undo removing duplicates in Google Sheets?

+

Yes, Google Sheets allows you to undo actions through the Edit menu or by pressing Ctrl+Z (Cmd+Z on a Mac) immediately after removing duplicates. However, ensure you do this right away as prolonged actions might overwrite the undo history.

How do I know if a row is completely identical?

+

To check for completely identical rows, you can use a combination of conditional formatting or a script. For conditional formatting, apply the formula =COUNTIFS(A:A,A1,B:B,B1,C:C,C1,…) to highlight rows where all specified columns have matching values.

What if I only want to remove duplicates from certain columns?

+

When using the Remove duplicates feature, you can choose specific columns to check for duplicates. If your data has multiple columns but you’re only concerned about duplicates in specific ones, select those columns in the dialog box before removing the duplicates.