How To Find Duplicates In Google Sheets: The Complete Technical Guide

Finding duplicates in Google Sheets is essential for maintaining database integrity, preventing billing errors, and cleaning messy customer lists. This guide covers four distinct methods—ranging from conditional formatting and the UNIQUE function to advanced QUERY formulas and Google Apps Script automation—designed for datasets of any scale.

Pre-Procedure Planning

Executing data hygiene operations requires a structured approach to prevent accidental data loss or permanent overwrites. Proper preparation ensures that primary keys remain intact and that downstream reporting is not corrupted by aggressive scrubbing.

• Essential tools and permissions: A modern web browser, an active Google Workspace account with Edit access to the target spreadsheet, and a clean staging tab for destructive testing.
• Mandatory prerequisite knowledge: Understanding range references, absolute versus relative cell referencing, case sensitivity rules in spreadsheet formulas, and the difference between highlighting data and physically purging rows.
• Estimated duration and scope: 5 to 15 minutes for standard datasets under 50,000 rows; larger enterprise datasets exceeding 100,000 rows may require formula optimization or Google Apps Script.

Step-by-Step Duplicate Detection Workflow

Step 1: Use Conditional Formatting for Visual Identification

Visual identification is the fastest way to spot duplicate entries without altering your underlying data structure. This method applies a background color to any cell containing a value that appears more than once in a designated range.

1. Open your target Google Sheet and highlight the exact column or range of cells you wish to audit for duplicate values.
2. Navigate to the top menu bar, click on Format, and select Conditional formatting from the dropdown list.
3. In the conditional format rule panel on the right side of your screen, look under the Format rules section and click the dropdown menu that currently says "Cell is not empty".
4. Scroll to the bottom of the list and select Custom formula is.
5. In the value or formula input box that appears, enter the formula: equals countif($A$1:$A$100, A1) greater than 1, replacing A1:A100 and A1 with your actual starting and ending cell references, making sure to include the dollar signs for absolute referencing.
6. Choose your desired highlight style under Formatting style (such as a light red fill with dark red text) and click Done to apply the rule across your selected range.

Pro-Tip: Always lock your range using absolute dollar signs (e.g., $A$2:$A$1000) while keeping your evaluation cell relative (e.g., A2) so Google Sheets correctly evaluates every single row against the entire dataset.

Step 2: Use the UNIQUE Function to Extract Distinct Records

When you need to generate a clean, standalone list of unique values extracted from a messy, duplicate-heavy column, the built-in UNIQUE function provides a dynamic, formula-driven solution.

1. Click on an empty cell in a separate, clear column or a new tab where you want your clean dataset to live.
2. Type the formula starting with the equals sign, followed by UNIQUE, open parentheses, and then select your source data range. For example: equals unique(Sheet1!A2:A1000).
3. Press the Enter key on your keyboard to execute the formula. Google Sheets will automatically spill the filtered list of unique values downward into adjacent empty rows.
4. Review the generated list to ensure it captured all distinct items without truncation.

Warning: The UNIQUE function creates a dynamic array output. If you attempt to type manual data into the cells directly below or beside the formula output, you will trigger a #REF! error because the spilled range is blocked.

Step 3: Use the Built-In Data Cleanup Tool to Remove Duplicates

Google Sheets features a dedicated, automated utility designed to find and permanently strip duplicate rows from your active range in a single click.

1. Click and drag your cursor to select the entire table or range of data containing potential duplicates, including your header row.
2. Navigate to the top menu, click on Data, hover over Data cleanup, and select Remove duplicates.
3. In the modal window that pops up, verify that the checkbox for "Data has header row" is checked so your column titles are excluded from deletion.
4. Choose whether to evaluate all columns or only specific columns by checking or unchecking individual boxes in the list.
5. Click the blue Remove duplicates button at the bottom right of the modal.
6. Read the confirmation pop-up message that tells you exactly how many duplicate rows were removed and how many unique rows remain, then click OK.

Step 4: Use a Multi-Column Formula for Advanced Matching

Sometimes a single column is not enough; a customer name might appear twice, but if they have different email addresses, they are unique records. Finding duplicates based on a combination of two or more columns requires a concatenated helper column or a multi-criteria COUNTIFS formula.

1. Create a temporary helper column adjacent to your main dataset (for example, Column Z).
2. In the first data row of your helper column, enter a formula that joins your criteria columns together using an ampersand delimiter. For example: equals A2 ampersand space ampersand B2, combining First Name and Last Name.
3. Drag the fill handle down to apply this concatenation formula to every row in your dataset.
4. Apply the conditional formatting rule or a standard COUNTIF formula to this helper column to spot rows where the combined identity is duplicated across the sheet.

Comparative Method Analysis

Common Troubleshooting & Field Fixes

Executing data deduplication procedures at scale frequently exposes hidden formatting inconsistencies and structural spreadsheet errors.

• Root Cause: Trailing whitespace characters or invisible non-breaking spaces cause formulas to treat visually identical text strings as unique values.
  
  
  • Actionable Fix: Wrap your evaluation ranges inside a TRIM function (e.g., TRIM(A2)) or use the dedicated "Trim whitespace" tool under Data cleanup to normalize your text strings before running duplicate checks.
• Root Cause: Mixed case sensitivity issues (e.g., "Apple" versus "apple") bypass standard exact-match duplicate formulas.
  
  
  • Actionable Fix: Convert all text to lowercase or uppercase uniformly across your dataset using the LOWER() or UPPER() wrapper functions inside your conditional formatting rules.
• Root Cause: Number formatting mismatches where numerical digits are stored as text strings (e.g., ID number "1042" versus numeric 1042).
  
  
  • Actionable Fix: Select your numeric column, navigate to Format > Number, and explicitly set the data format to Number or Plain Text uniformly across the entire column range.

Frequently Asked Questions

How do I find exact duplicates across an entire multi-column table?

To find complete duplicate rows where every single column matches, the most efficient method is using the built-in Data > Data cleanup > Remove duplicates tool. Alternatively, you can create a single concatenated helper column that joins every column in the row together, then apply a simple COUNTIF formula to flag rows where the concatenated string count exceeds one.

Can Google Sheets find duplicates across two different worksheets?

Yes. You can compare two distinct sheets by using a VLOOKUP or MATCH formula referencing the external sheet tab, or by using a conditional formatting rule with a custom formula that utilizes the COUNTIF function pointing to the secondary sheet range. For example: equals countif(Sheet2!$A$2:$A$1000, A2) greater than 0 will highlight any value in Sheet 1 that also exists anywhere in Sheet 2.

Does the Remove Duplicates tool delete data permanently?

Yes. The built-in "Remove duplicates" utility permanently deletes entire rows from your active spreadsheet range based on your specified criteria. It is an irreversible action unless you press Control plus Z immediately afterward or restore a previous file version from your Version History logs.

Why is my conditional formatting duplicate rule highlighting unique cells?

This usually happens because the cell reference inside your custom formula is locked with absolute dollar signs (e.g., $A$1 instead of A1). Ensure that your row number in the evaluation cell reference is completely relative so that Google Sheets dynamically updates the evaluation down the column.

Master your spreadsheet workflows today and ensure absolute data integrity across all your professional reporting sheets.