How to Quickly Find and Manage Duplicates in Google Sheets

Identifying duplicate data is an essential skill for anyone working with spreadsheets, whether you are managing a CRM export, a marketing email list, or an inventory tracker. Redundant entries can skew your analytics, lead to double-billing in financial reports, and generally compromise the integrity of your decision-making.

In Google Sheets, there are three primary ways to handle duplicates depending on your goal: highlighting them for manual review, extracting a clean list of unique values, or permanently removing them using built-in tools.

The Fastest Ways to Identify Duplicates in Google Sheets

For those who need an immediate solution, here are the most effective methods:

1. To visualize duplicates: Use Format > Conditional formatting with the custom formula =COUNTIF(A:A, A1)>1. This highlights every cell that appears more than once.
2. To create a clean list: Use the formula =UNIQUE(A1:B100) in a new cell. This generates a dynamic table containing only the first occurrence of each row.
3. To delete duplicates permanently: Select your data and go to Data > Data cleanup > Remove duplicates.

Highlighting Duplicates with Conditional Formatting

Visualizing duplicates is often the safest first step. It allows you to inspect why data is repeated before taking the irreversible action of deleting it. Conditional formatting uses a logic-based rule to change the background color of cells that meet specific criteria.

Setting Up a Basic Duplicate Highlight Rule

To highlight duplicates in a single column (e.g., Column A), follow these steps:

1. Select the range of cells you want to check (e.g., A2:A100).
2. Navigate to the top menu and select Format, then click Conditional formatting.
3. In the sidebar that appears, under the Format cells if dropdown, scroll to the bottom and select Custom formula is.
4. Enter the following formula: =COUNTIF($A$2:$A$100, A2)>1
5. Choose a formatting style (such as a light red fill) and click Done.

Understanding the Formula Logic

The COUNTIF function works by scanning a range and counting how many times a specific value appears.

• $A$2:$A$100: The dollar signs ($) create an absolute reference. This ensures that as Google Sheets checks each row, it always compares it against the same fixed range.
• A2: This is a relative reference. As the rule moves down the column, it checks A3, A4, and so on.
• >1: This is the trigger. If a value appears once, the result is 1 (False). If it appears twice or more, the result is greater than 1 (True), triggering the highlight.

In our internal testing with large datasets, we found that using a fixed range like $A$2:$A$5000 is significantly faster than using a whole-column reference like A:A if your sheet has thousands of empty rows at the bottom.

How to Highlight Only the Second Occurrence of a Duplicate

A common frustration with the standard COUNTIF method is that it highlights every instance of a duplicate. If "Customer A" appears three times, all three rows turn red. If your goal is to identify which rows to delete while keeping the "original" entry clean, you need a "growing range" formula.

To highlight only the repeat entries (the 2nd, 3rd, or 4th occurrences):

1. Select your range (e.g., A2:A100).
2. Open Conditional formatting and select Custom formula is.
3. Enter this formula: =COUNTIF($A$2:A2, A2)>1

Why this works: Notice the difference in the range $A$2:A2. The start is locked ($A$2), but the end (A2) is not. As the formula moves to row 5, it checks $A$2:A5. When it hits the first instance of a value, the count is only 1. When it hits the second instance later in the sheet, the count becomes 2, and the highlight is applied. This effectively flags only the "extra" data.

Extracting Unique Values Using the UNIQUE Function

If you don't want to mess with your original "dirty" data, the UNIQUE function is the most efficient tool in the Google Sheets arsenal. It creates a new, deduplicated list in a different part of your spreadsheet.

Using the UNIQUE Formula

In an empty cell, type: =UNIQUE(A2:C100)

This single formula will output a clean table. It is dynamic, meaning if you add a new unique entry to the original range, the UNIQUE list will update automatically.

Pro Tip from the Field: One limitation of UNIQUE is that the output is a formula result. You cannot manually edit individual cells in the new list without breaking the entire array (resulting in a #REF! error). If you need a static list to edit later, copy the results and use Paste Special > Values only.

Using Built-in Data Cleanup to Remove Duplicates

When you are certain that the duplicates are errors and you have a backup of your file, using the built-in "Remove Duplicates" tool is the fastest way to sanitize a dataset.

Steps to Permanently Delete Duplicates

1. Highlight the dataset or select the entire sheet.
2. Go to Data > Data cleanup > Remove duplicates.
3. A dialog box will appear. If your data has headers (like "Name", "Date", "Email"), make sure to check Data has header row.
4. Select Columns to analyze:
   • If you want to delete a row only if the entire row is a perfect match, leave all columns checked.
   • If you want to delete rows based on a unique identifier (like an Order ID), unselect everything and only check the "Order ID" column.
5. Click Remove duplicates.

Google Sheets will provide a summary of how many duplicate rows were found and removed, and how many unique rows remain.

Finding Duplicates Across Multiple Columns

Sometimes, a duplicate isn't defined by a single cell, but by a combination of cells. For example, in a class roster, "John" and "Smith" might appear multiple times, but "John Smith" in the same row is the actual duplicate you are looking for.

The COUNTIFS Approach for Multi-Column Duplicates

To find duplicates based on two columns (Column A and Column B), use this custom formula in conditional formatting:

=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2)>1

The COUNTIFS (with an 'S') allows you to set multiple criteria. The cell will only highlight if both conditions are met simultaneously. We have used this extensively for reconciling bank statements where both the "Date" and "Amount" must match to flag a potential double-entry.

Common Challenges and Expert Troubleshooting

Even with the right formulas, duplicates can sometimes hide in plain sight. In our experience managing complex spreadsheets, these are the most common reasons why "duplicates" aren't being caught:

1. Hidden Spaces (The Silent Saboteur)

"John Doe" and "John Doe " (with a trailing space) look identical to the human eye, but Google Sheets treats them as completely different values.

• The Fix: Before running a duplicate check, select your data and go to Data > Data cleanup > Trim whitespace. This removes all leading, trailing, and excessive internal spaces.

2. Case Sensitivity

By default, the COUNTIF and UNIQUE functions in Google Sheets are case-insensitive. "APPLE" and "apple" are considered duplicates.

• The Fix: If you need to find duplicates where case matters, you must use a more complex formula involving the EXACT function combined with SUMPRODUCT.

3. Data Type Mismatches

A "12345" stored as text and a "12345" stored as a number will not be flagged as duplicates by some functions.

• The Fix: Ensure your columns have consistent formatting by selecting the column and going to Format > Number > Automatic or Plain text.

4. Formula Performance in Large Sheets

Applying conditional formatting to a sheet with 50,000+ rows can make the scroll speed lag significantly.

• The Fix: For massive datasets, create a "Helper Column." In cell B2, enter =COUNTIF($A$2:$A$50000, A2)>1. Drag this down. Then, use the Filter tool to show only the TRUE values. This is much less taxing on your browser's memory than active color-formatting.

Summary of Methods

Frequently Asked Questions

How do I find duplicates in two different sheets?

To compare Sheet1 and Sheet2, you can use the VLOOKUP or MATCH function. For example, in Sheet1, you could use =ISNUMBER(MATCH(A2, Indirect("Sheet2!A:A"), 0)) to see if the value in A2 exists anywhere in Sheet2.

Can I find duplicates and move them to another sheet?

The easiest way is to use the FILTER function. Use =FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>1) in a new sheet. This will pull only the items that have a count higher than one into a separate list.

Why is "Remove Duplicates" not working?

Most likely, there are subtle differences in the data, such as hidden characters, non-breaking spaces from web imports, or different number formats. Always run Data > Data cleanup > Trim whitespace first.

Conclusion

Finding duplicates in Google Sheets doesn't have to be a manual, error-prone task. For quick visual audits, Conditional Formatting with COUNTIF is the gold standard. For rapid data hygiene, the built-in Data Cleanup tool is unbeatable. If you are building a reporting dashboard, the UNIQUE function ensures your results remain clean and dynamic.

Before you start, always remember to create a backup copy of your tab. Clean data is the foundation of accurate analysis, and mastering these tools ensures your spreadsheets remain a reliable source of truth.