Managing messy data is one of the most common challenges when working with spreadsheets. Whether you are merging two customer lists, importing website form submissions, or tracking inventory, duplicate entries can skew your analysis, lead to double-spending, or simply cause confusion.

In Google Sheets, there is no single "correct" way to handle duplicates. The best method depends on your goal: Do you want to delete them forever, highlight them for review, or create a separate list of unique items?

This guide breaks down the most effective strategies for identifying and cleaning duplicate records using built-in tools, conditional formatting, and advanced formulas.

Quick Summary of Methods

Goal Method Best For Result
Permanent Removal Data Cleanup Tool Cleaning up raw imports quickly Deletes duplicate rows instantly
Visual Identification Conditional Formatting Reviewing data before making changes Highlights duplicates in a specific color
Non-Destructive List UNIQUE Function Creating dynamic reports Keeps original data; generates a clean list elsewhere
Advanced Filtering Custom Formulas Handling case-sensitivity or multi-column matches Provides granular control over what counts as a duplicate

Method 1: Removing Duplicates Permanently Using the Built-In Tool

If your spreadsheet has grown too large and you are certain that redundant rows serve no purpose, the built-in "Remove duplicates" tool is the fastest solution. This is a destructive method, meaning it permanently alters your original dataset.

Steps to Use the Data Cleanup Tool

  1. Select Your Data: Highlight the range of cells or columns you want to check. If you want to check the entire sheet, click the top-left corner square or press Ctrl + A.
  2. Access the Tool: Go to the top menu and select Data > Data cleanup > Remove duplicates.
  3. Define Your Criteria:
    • Data has header row: Check this box if your first row contains titles (like "Email," "Name," or "Date"). This prevents your headers from being deleted.
    • Columns to analyze: You can select "Select all" to find rows that are identical across every single column. Alternatively, you can check only specific columns (e.g., only "Email") to find duplicates based on a unique identifier.
  4. Execute: Click Remove duplicates.

Google Sheets will then display a summary pop-up telling you exactly how many duplicate rows were removed and how many unique rows remain.

Expert Experience: When to Select Specific Columns

In our testing with large marketing datasets, selecting all columns is often too restrictive. For example, if a customer signed up twice but used a different phone number the second time, a "Select all" check would consider them unique. If your goal is to have one entry per person, you should only select the "Email" or "User ID" column to ensure true deduplication.

Method 2: Highlighting Duplicates with Conditional Formatting

Sometimes, you don't want to delete data immediately. You might need to investigate why a duplicate exists—perhaps a customer placed two separate orders, or a transaction was logged twice by mistake. In these cases, highlighting the duplicates in a bright color is the better approach.

How to Apply a Duplicate-Finding Formula

Google Sheets doesn't have a "Highlight Duplicates" button, so we use a custom formula within the Conditional Formatting menu.

  1. Select the range you want to monitor (e.g., A2:A100).
  2. Navigate to Format > Conditional formatting.
  3. Under the "Format rules" dropdown, scroll down and select Custom formula is.
  4. Enter the following formula: =COUNTIF($A$2:$A$100, A2) > 1
  5. Set your formatting style (e.g., a light red or yellow fill) and click Done.

How the Formula Works

The COUNTIF function counts how many times a specific value appears in a range. By setting the rule to > 1, you are telling Google Sheets: "If this value appears more than once in this list, change its color."

Handling Multiple Columns

If you need to highlight a row only if the combination of "First Name" and "Last Name" is repeated, you can use the COUNTIFS function (plural). For example, if Names are in Column A and Surnames are in Column B: =COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2) > 1

Method 3: Using the UNIQUE Function for Dynamic Data

The UNIQUE function is a "non-destructive" powerhouse. Instead of changing your source data, it scans a range and outputs a perfectly clean list of unique values in a new location. This is ideal for building dashboards or summary tables that stay updated even as you add more messy data to the original list.

Syntax and Implementation

Select an empty cell where you want your clean list to start, and type: =UNIQUE(A2:C500)

Why We Prefer UNIQUE for Reporting

  • Live Updates: If you add a new, unique entry to the bottom of the original range A2:C500, the UNIQUE list will automatically expand to include it.
  • Preserves Audit Trails: You keep your raw data (including the duplicates) for auditing purposes while using the cleaned list for calculations and visualization.
  • Multi-Column Support: If you select multiple columns in the range, UNIQUE only removes rows where every cell in that row is a duplicate of another.

Advanced Duplicate Management: Solving Common Data Issues

Real-world data is rarely perfect. Often, duplicates go undetected because of minor formatting differences.

The "Hidden Space" Trap

A common issue occurs when one entry is "John Doe" and another is "John Doe ". To a spreadsheet, that extra space at the end makes them completely different.

Pro Tip: Before running any duplicate removal tool, use the TRIM function to clean your data. You can apply it to an entire column using an ArrayFormula: =ARRAYFORMULA(TRIM(A2:A)) This removes leading, trailing, and extra internal spaces, ensuring your duplicate checks are accurate.

Case-Sensitive Duplicates

By default, Google Sheets is case-insensitive. It treats "APPLE" and "apple" as the same thing. If your project requires distinguishing between these (such as case-sensitive product codes), a standard COUNTIF will fail.

To find exact, case-sensitive duplicates, you must use the EXACT function wrapped in a SUMPRODUCT: =SUMPRODUCT(--EXACT($A$2:$A$100, A2)) > 1

Keeping Only the Latest Entry

Sometimes, you want to mark all duplicates except for the very last one (useful for tracking the most recent status update). You can do this by adjusting the range in your conditional formatting formula: =COUNTIF(A2:$A$100, A2) > 1 By leaving the first part of the range relative (A2), the formula "shrinks" its search area as it moves down the list, eventually reaching a point where no more duplicates exist below it.

Best Practices for Data Integrity

Before you start deleting or modifying your spreadsheets, follow these safety steps:

1. Create a Backup

Always make a copy of your sheet (File > Make a copy) before running "Remove duplicates." If you make a mistake and don't realize it until days later, you will need that backup.

2. Use Version History

If you accidentally deleted rows and the "Undo" (Ctrl + Z) buffer is gone, use File > Version history > See version history. You can restore your sheet to exactly how it looked before you ran the cleanup tool.

3. Normalize Data First

As mentioned earlier, use TRIM to remove spaces and LOWER to standardize capitalization if the case doesn't matter. Standardizing your data increases the success rate of any deduplication process.

4. Data Validation

To prevent duplicates from entering your sheet in the first place, use Data > Data validation. You can set a custom formula rule that prevents users from entering a value that already exists in the column.

Conclusion

Mastering duplicates in Google Sheets is about choosing the right tool for the specific task at hand. For a quick one-time cleanup, the Data Cleanup tool is unbeatable. For ongoing analysis where you need to see the errors, Conditional Formatting provides the visual feedback required for manual oversight. And for dynamic, professional reports, the UNIQUE function is the most robust choice.

By combining these methods with data normalization techniques like TRIM, you can transform a cluttered, unreliable spreadsheet into a high-quality data asset.

FAQ

How do I remove duplicates in Google Sheets without deleting rows?

The best way is to use the UNIQUE function in a new sheet or column. This creates a clean list while leaving your original data untouched. Alternatively, use Conditional Formatting to highlight them so you can hide them manually or via filters.

Can I find duplicates across two different sheets?

Yes, but it requires a more complex formula like VLOOKUP or MATCH. For example, =ISERROR(MATCH(A2, Indirect("Sheet2!A:A"), 0)) can tell you if a value in Sheet1 exists in Sheet2.

Does "Remove Duplicates" delete the entire row?

Yes. When you use the built-in tool, Google Sheets deletes the entire row where it finds a duplicate based on your selected criteria. If you only want to clear the content of the cell but keep the empty row, you will need to do it manually or via a script.

Why is Google Sheets not finding my duplicates?

The most common reasons are hidden characters (like non-breaking spaces), different data types (one "123" is a number, the other is text), or different capitalization in a case-sensitive search. Running the TRIM and CLEAN functions usually solves this.

Is there a way to automate duplicate removal?

You can use Google Apps Script to write a custom function that triggers every time the sheet is edited, or use the "Data validation" feature to block duplicates from being entered in the first place.