How To Find Duplicates In Google Sheets

9 min read

Finding duplicate data in Google Sheets is a fundamental skill for anyone managing spreadsheets, whether you are cleaning a mailing list, auditing inventory records, or preparing a dataset for analysis. Because of that, duplicate entries can skew your sums, inflate your counts, and lead to costly errors in reporting. Plus, fortunately, Google Sheets offers a versatile toolkit ranging from simple visual highlights to powerful formulas and built-in cleanup features. Mastering these methods allows you to maintain data integrity without needing complex external software.

Why Identifying Duplicates Matters

Before diving into the how, it helps to understand the why. Duplicates usually creep in during data entry, system migrations, or when merging multiple sources. A customer might sign up twice with slightly different email formats, or a product SKU might be scanned twice during inventory. If left unchecked, these repetitions distort pivot tables, break VLOOKUP or XLOOKUP logic, and make your "unique visitor" counts inaccurate. The goal isn't always to delete them immediately—sometimes you need to review them first—but you must be able to see them clearly.

Method 1: Conditional Formatting for Visual Detection

The fastest way to spot duplicates without altering your data is Conditional Formatting. This method highlights the offending cells in a color of your choice, giving you an instant visual map of your data health.

  1. Select the column or range you want to check (e.g., A2:A100).
  2. work through to Format > Conditional formatting in the top menu.
  3. In the sidebar that opens, ensure the "Apply to range" box matches your selection.
  4. Under "Format rules," click the dropdown that says "Is not empty" and scroll down to select Custom formula is.
  5. Enter the following formula (adjust A2 to match the first cell of your selected range): =COUNTIF(A:A, A2) > 1
  6. Choose a formatting style (a bright fill color like red or yellow works best).
  7. Click Done.

How it works: The COUNTIF function counts how many times the value in the current cell (A2) appears in the entire column (A:A). If the count is greater than 1, the formatting triggers. This is dynamic; if you fix a duplicate, the highlight disappears automatically.

Highlighting Entire Rows Based on a Duplicate Column

Often, you want to highlight the whole row if the ID in Column A is a duplicate.

  1. Select the entire dataset range (e.g., A2:Z100).
  2. Use the same Custom Formula, but lock the column reference with a dollar sign: =COUNTIF($A:$A, $A2) > 1
  3. Apply the format. Now, if Column A has a duplicate, the entire row lights up, making context immediately visible.

Method 2: The UNIQUE Function (Non-Destructive Extraction)

If you prefer to keep your raw data untouched and simply generate a clean list elsewhere, the UNIQUE function is your best friend. This is a dynamic array formula—meaning it spills results down automatically.

Syntax: =UNIQUE(range)

Example: If your data is in A2:B20, type this in an empty cell (e.g., D2): =UNIQUE(A2:B20)

This instantly outputs a list containing only the first occurrence of each row combination. Consider this: by default, UNIQUE compares entire rows. If you only want unique values from a single column, select just that column: =UNIQUE(A2:A20) Simple, but easy to overlook. That's the whole idea..

Pro Tip: Combine it with SORT for an alphabetized clean list: =SORT(UNIQUE(A2:A20))

This method is ideal for creating dropdown menus (Data Validation) or feeding clean data into pivot tables without modifying the source sheet.

Method 3: The "Remove Duplicates" Built-in Tool (Destructive Cleanup)

When you are confident you want to permanently delete extra rows, Google Sheets has a native feature that requires no formulas.

  1. Select the range containing your data (including headers).
  2. Go to Data > Data cleanup > Remove duplicates.
  3. A dialog box appears. Check "Data has header row" if your selection includes the top row.
  4. Select the columns you want to analyze for duplication.
    • Select all columns to remove rows that are exact duplicates across every column.
    • Select specific columns (e.g., only "Email" or "Transaction ID") to remove rows where those specific fields match, even if other columns differ.
  5. Click Remove duplicates.

Sheets will report how many duplicate rows were found and removed, and how many unique rows remain. Warning: This action modifies your source data. While you can undo it immediately with Ctrl+Z (or Cmd+Z), it is wise to duplicate the sheet (Right-click tab > Duplicate) before running this on critical datasets That's the part that actually makes a difference. That alone is useful..

Method 4: Advanced Formulas for Specific Scenarios

Sometimes you need more control than the standard tools offer—perhaps you need to flag duplicates with text, find the second occurrence only, or handle case sensitivity.

Flagging with COUNTIF in a Helper Column

Insert a new column next to your data. In row 2, enter: =IF(COUNTIF($A$2:A2, A2) > 1, "Duplicate", "Unique")

Drag this down. Because the range $A$2:A2 expands as you drag (absolute start, relative end), it marks the first occurrence as "Unique" and all subsequent ones as "Duplicate." This is perfect for filtering: filter the helper column for "Duplicate" to review or delete them manually.

Finding Case-Sensitive Duplicates

Standard COUNTIF and UNIQUE are case-insensitive ("Apple" = "apple"). For case-sensitive checks, you need a more complex array formula using EXACT and SUMPRODUCT (or MAP/LAMBDA in newer Sheets versions).

Formula for a helper column (assuming data in A2:A): =ARRAYFORMULA(IF(A2:A="",, IF(SUMPRODUCT(--EXACT(A2, $A$2:$A$100))>1, "Duplicate", "Unique")))

This treats "ID-101" and "id-101" as distinct values It's one of those things that adds up..

Identifying Duplicates Across Multiple Columns (Composite Keys)

Often a duplicate isn't defined by one column, but a combination (e.g., First Name + Last Name + Birthdate). Create a "Composite Key" helper column: =A2 & "|" & B2 & "|" & C2 (Consider using a delimiter like | that won't appear in your data to avoid false matches like "John" + "Smith" matching "Johns" + "Mith") That alone is useful..

Then run any of the above methods (Conditional Formatting, COUNTIF, UNIQUE) on this new helper column.

Method 5: Pivot Tables for Duplicate Analysis

Pivot Tables are excellent for quantifying duplicates before you decide what to do with them Easy to understand, harder to ignore..

  1. Select your data > Insert > Pivot table.
  2. In the Pivot Table editor, add the suspect column (e.g., "Email") to Rows.
  3. Add the same column (or any column) to Values. Ensure it summarizes by COUNTA (count of non-empty cells).
  4. Sort the "COUNTA" column Descending.

You now have a ranked list showing exactly which values appear most frequently and how many times. This is invaluable for auditing: you can see if "test@test.com"

Method 6: Automating Duplicate Removal with Apps Script

When the dataset is huge (thousands of rows) or you need to repeat the cleanup on a regular schedule, a one‑click script is far more efficient than any UI‑based tool Worth knowing..

  1. Open the script editor – Extensions ▸ Apps Script.
  2. Paste the following function (adjust sheetName and colRange as needed):
function removeDuplicates() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("sheetName"); // <- change to your sheet name
  const data = sheet.getRange("A2:Z").getValues(); // entire data block, adjust columns

  // Build a map of unique rows based on all columns
  const seen = new Map();
  const uniqueRows = [];

  data.And forEach((row, idx) => {
    // Convert row to a string key – join values with a delimiter that won’t appear in data
    const key = row. join("|");
    if (!That's why seen. has(key)) {
      seen.set(key, true);
      uniqueRows.

  // Clear the original data and write back only unique rows
  sheet.Worth adding: length, uniqueRows[0]. Here's the thing — getRange(2, 1, uniqueRows. clear();
  if (uniqueRows.Plus, length) {
    sheet. getRange("A2:Z").length).

3. **Create a button** (or add to the menu) so users can trigger the cleanup with a single click.  

> **⚠️ Safety tip:** Always make a backup copy of the sheet (File ▸ Print ▸ Save As ▸ Web Page, or simply copy the sheet to another file) before running any script that modifies data.

### Method 7: Using Add‑ons for “Smart” Duplicate Handling  

If you prefer a GUI‑driven approach with more control, consider add‑ons such as **Remove Duplicates**, **Duplicate Cleaner**, or **SheetTools! Duplicate Remover**. These tools often let you:

* Preview duplicates before deletion.  
* Choose which columns to treat as the duplicate key.  
* Preserve a history or log of removed rows (useful for audit trails).  

Most add‑ons integrate directly into the “Data” menu, so you can right‑click a column and run a duplicate‑scan in seconds.

### Best Practices & Tips  

| Practice | Why It Matters | How to Implement |
|----------|----------------|------------------|
| **Work on a copy** | Prevents accidental loss of original data. | Refresh the pivot after any change to see updated counts. |
| **Audit with Pivot Tables** | A quick count of duplicate frequency helps prioritize cleanup. |
| **Document your duplicate definition** | Different stakeholders may have varying ideas of what constitutes a duplicate (exact match vs. | Schedule a Google Apps Script **trigger** (e.| Right‑click the tab → **Duplicate** before any bulk operation. Day to day, g. fuzzy match). , “Every week on Sunday at 00:00”). Worth adding: | Create a rule: `=COUNTIF($A$2:A2, A2)>1` → format as red fill. g.Still, |
| **Validate after removal** | Ensure you didn’t inadvertently drop non‑duplicates. , “Duplicates defined as identical values in the *Email* column, case‑insensitive.|
| **put to work Conditional Formatting rules** | Visual cues let you spot duplicates instantly without extra columns. |
| **Automate recurring clean‑ups** | Weekly or monthly data imports often bring new duplicates. Now, ” |
| **Use helper columns sparingly** | Helper columns can bloat a sheet, but they are invaluable for debugging. | Add a comment in a dedicated “Notes” sheet: e.That said, | Delete the helper column(s) after you’ve verified the results (Data ▸ Filter ▸ **Clear filter**). | Run a `COUNTIF` check on the cleaned column; the count should equal the number of unique values. 

### When to Keep Duplicates  

Not every duplicate is a problem. Consider:

* **Log files** where the same event may legitimately appear multiple times.  
* **Historical records** that intentionally repeat for audit completeness.  
* **Data that will be merged later** (e.g., combining two datasets where overlap is expected).  

In such cases, you might want to

flag them rather than delete them. Add a “Status” or “Duplicate Flag” column and use conditional formatting to highlight rows that meet your criteria. Think about it: you can also move flagged rows to a separate “Review” or “Archive” sheet, keeping them accessible without cluttering your primary dataset. If you plan to merge this data with another source later, preserve the original import on a read‑only tab and perform deduplication only on a working copy.

### Conclusion

Whether you choose the built‑in “Data cleanup” feature, formulas like UNIQUE() and COUNTIF(), pivot tables, Apps Script, or a dedicated add‑on, the best duplicate‑removal method is the one that matches your dataset’s size, frequency, and audit requirements. Day to day, always start with a backup, document exactly what defines a duplicate for your project, and validate the results before deleting anything. With these habits in place, you can keep your Google Sheets clean, accurate, and trustworthy—without losing information you might need later.
Latest Batch

New and Noteworthy

Explore the Theme

Good Reads Nearby

Thank you for reading about How To Find Duplicates In Google Sheets. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home