How to Highlight and Find Duplicates in Google Sheets

Are you dealing with messy data? Duplicates can ruin everything. I know. Whether it’s a budget report, a customer list, or a simple tracker, duplicates make your data unreliable and harder to analyze.

They waste your time, lead to errors, and sometimes even mess up important decisions.

But don’t stress—it’s easier to fix than you think. Google Sheets has simple tools that help you highlight and clean up duplicates in no time.

Whether you’re managing a small spreadsheet or a huge dataset, you can handle duplicates with just a few steps.

In this guide, I’ll show you exactly how to find and manage duplicates in Google Sheets. It’s quick, effective, and something you can do right now.

Ready to clean up your data? Let’s get started.

What are duplicates in Google Sheets?

Duplicates are just repeated data in your columns. They pop up for many reasons. Maybe you copied and pasted data from different places, and some rows got mixed up. Or maybe someone accidentally typed in the same info twice. It happens all the time.

So, why does this matter? Duplicates can mess up everything in your spreadsheet. They make your numbers look bigger than they should be, mess up calculations, and make it harder to spot trends.

Let’s say you’re tracking sales. If you have a duplicate entry, it could count the same transaction twice, messing up your revenue number. That’s a big problem, especially when you need accurate data to make decisions.

Cleaning up duplicates is more than just making your spreadsheet look neat. It’s crucial for keeping your data accurate. Whether you’re looking at customer lists, financial reports, or inventory numbers, clean data leads to better decisions. Without it, you’re working with faulty information that could cost you.

But don’t worry—Google Sheets makes it easy to find and fix duplicates. Stick with me, and I’ll show you how to clean up your data in no time.

How to highlight duplicates in Google Sheets

Duplicates can make your data messy—but don’t worry, Google Sheets has tools to help clean it up.

There are three simple ways to highlight duplicates: Conditional Formatting, UNIQUE formulas, and Google Sheets add-ons.

#1 Using conditional formatting

Conditional formatting is the easiest way to spot duplicates. It automatically highlights duplicate values so you can spot them right away. Here’s how to set it up:

• Select the range of cells you want to check for duplicates. For example, if you want to highlight duplicates in column A, select A1:A1000.

• Go to the top menu and click Format > Conditional formatting.

• In the Conditional format rules panel, make sure your range is selected under “Apply to range”.

• Under Format cells if, choose Custom formula is.

• Enter this formula: =COUNTIF(A:A, A1) > 1. This formula checks how many times each value appears in column A. If it appears more than once, it’s a duplicate.

• Pick your formatting style, like a bold color (red works well).

• Click Done.

That’s it! Now, duplicates will be highlighted. This is perfect for quick checks, whether you’re working with a small list or a large dataset.

#2 Using formulas like UNIQUE

The UNIQUE function is great when you want to filter out duplicates and see only the unique values. Here’s how:

• Select an empty column where you want the unique values to show up.

• Type the formula: =UNIQUE(C1:C8). Replace C1:C8 with the range you want to check.

• Press Enter.

This formula will list only the unique values, skipping any duplicates. Want to see only duplicates instead?

Use this formula: =FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1)

This shows only the duplicate values in your dataset.

The UNIQUE function is fast and effective when you need a clean list with no repeats.

#3 Using Google Sheets add-ons

For larger datasets or complex data, Google Sheets add-ons are a game-changer. Tools like Remove Duplicates by Ablebits can do a lot more than just highlight duplicates.

They can find duplicates across multiple columns and can automatically remove or move duplicates to another sheet. They can also focus on specific rows or exclude headers.

Use an add-on like Remove Duplicates:

• Go to the Extensions menu.

• Then click Add-ons > Get add-ons.

• Search for Remove Duplicates and install it.

• Once installed, open the add-on from the Extensions menu.
• Follow the on-screen instructions to select your range, define rules, and choose what to do with duplicates (highlight, delete, or move them).

Add-ons are especially useful when you have data spread across multiple sheets or columns, or when you need to automate the process for big datasets.

So, which method should you choose?

It depends on your needs! Use conditional formatting for quick, visual spotting of duplicates. Use UNIQUE formulas when you want a clean list without repeats. And use add-ons for advanced tasks like cross-sheet duplicate checks or bulk cleanup.

Each method has its strengths. Choose the one that works best for you. With these tools, keeping your data clean and accurate has never been easier.

Some advanced techniques to identify duplicates

Sometimes, duplicates aren’t just sitting in one column—they’re hiding across multiple fields, rows, or even entire datasets.

But no worries, you can tackle these tricky duplicates using advanced techniques. Let’s break it down.

#1 Highlighting duplicates across multiple columns

What if you have duplicates across multiple columns, like “Fruits” and “Quantity“? Here’s a formula that checks for duplicates across both columns.

• Select the range of data you want to check, for example, A1:D8.

• Go to Format > Conditional formatting.

• Under Format cells if, choose Custom formula is.

• Enter this formula: =COUNTIF($A$1:$D$8, A1) > 1. This formula combines the values from columns A, B, C and D and checks if they appear more than once.

• Pick your formatting style, like a bright color.

• Click Done.

This method is perfect when you need to check for duplicates across multiple fields, like “Fruits” and “Quantity” that need to match exactly to be considered a duplicate.

#2 Highlighting only subsequent duplicates

What if you only want to highlight the repeated entries, not the first occurrence? You can do that too with a small change in the formula.

• Select your range, for example, A2:A1000.

• Go to Format > Conditional formatting.

• Under Custom formula is, use this formula: =COUNTIF($A$2:A2, A2) > 1. This formula checks how many times the value appears up to the current row, so only duplicates after the first occurrence are highlighted.

• Set your formatting style and click Done.

This is useful when you want to spot the repeated values but keep the first instance intact, which may be the original data you entered.

#3 Highlighting duplicate rows

When you’re dealing with entire rows that are identical across multiple columns, this formula is your best friend.

• Select the range of rows you want to check, for example, A2:D9.

• Go to Format > Conditional formatting.

• Under Format cells if, choose Custom formula is.

• Enter this formula: =COUNTIF(ArrayFormula($A$2:$A$9&$B$2:$B$9&$C$2:$C$9&$D$2:$D$9), $A2&$B2&$C2&$D2) > 1. This combines the values of all columns in each row and checks if the entire row is repeated.

• Choose your formatting style.

• Click Done.

This works great for cleaning up data sets like sales records or customer lists where you need to make sure each row is unique.

#4 Using QUERY or ARRAYFORMULA for large datasets

Handling huge datasets? These advanced functions like QUERY and ARRAYFORMULA can make finding duplicates more dynamic and efficient.

1. Using QUERY to filter duplicates:

Use this formula to create a summarized table with duplicates: =QUERY(A2:C, "SELECT A, B, C, COUNT(A) GROUP BY A, B, C HAVING COUNT(A) > 1", 1). Replace A2:C with your actual data range.

2. Using ARRAYFORMULA to highlight duplicates dynamically:

Use this formula to label rows as duplicates or unique: =ARRAYFORMULA(IF(COUNTIF(A2:A100, A2:A100) > 1, "Duplicate", "Unique")). This will label each row with “Duplicate” or “Unique”. Replace A2:A100 with your actual data range.

These techniques are ideal for tech-savvy users working with large or constantly changing datasets. They make duplicate management much easier when dealing with bulk data.

How to remove duplicates in Google Sheets

Removing duplicates doesn’t have to be a hassle. Whether you need a quick fix or something more tailored, Google Sheets has simple tools to help you out.

Let’s look at two easy ways: the built-in Remove Duplicates feature and advanced filtering techniques.

#1 Using the built-in “Remove Duplicates” feature

Google Sheets makes it super easy to remove duplicates. This built-in tool clears your data in seconds and leaves only the unique values.

• Select the data range you want to clean up. For example, highlight the rows or columns with duplicates.
• Go to the top menu and click Data > Data cleanup > Remove duplicates.

• A pop-up will appear. Make sure your range is selected. If your data has headers, check the box that says Data has header row.

• Choose the columns to check. By default, all columns are selected. You can adjust it if duplicates only matter in specific columns.
• Click Remove duplicates.
• A message will pop up showing how many duplicates were removed and how many unique entries remain.

This method is perfect when you need a quick and simple cleanup. It’s ideal when you’re sure the duplicates aren’t important or when you’ve already reviewed your data.

#2 Using advanced filtering or sorting

If you need more control over the process, advanced filtering and sorting help you isolate duplicates before removing them. This way, you can review them first.

• Add a helper column next to your data. In the first cell of this column, enter this formula: =COUNTIF(A:A, A1) > 1.
  • Replace A:A with the column, you want to check and A1 with the first cell in that column. For Example, =COUNTIF(B:B, B2) > 1

• • This formula will return TRUE for duplicates and FALSE for unique entries.

• Copy the formula down the entire column to apply it to all rows.

• Filter the data:
  • Select your data range, including the helper column.

• • Go to Data > Create a filter.

• • Click the filter icon in the helper column and choose TRUE to show only duplicates.

• Review the filtered duplicates and decide whether to delete or move them.

Alternatively, you can sort duplicates to make them easier to review:

• Select your data range.

• Go to Data > Sort range> Advanced range sorting options.

• Sort by the helper column so duplicates (TRUE) will appear together.

This method is useful when you want to analyze the duplicates first or when you want to remove duplicates only from certain sections of your data.

How to solve some common issues with duplicates

Sometimes, finding and fixing duplicates in Google Sheets doesn’t go as smoothly as planned. Incorrect formulas, case sensitivity, or hidden formatting issues can trip you up. But don’t worry—here are the most common problems and how to solve them.

#1 Case sensitivity causing mismatches

Google Sheets treats uppercase and lowercase as different values. For example, “John” and “john” are seen as unique, even though they’re clearly the same name.

Use the LOWER function to standardize the text before checking for duplicates.

• Add a new column next to your data.
• Enter the formula: =LOWER(A1). Replace A1 with the first cell of your data range.

• Copy the formula down to apply it to all rows.
• Use this new column to check for duplicates instead of the original.

#2 Extra spaces creating false duplicates

Trailing or leading spaces can make identical values appear as duplicates—or worse, prevent real duplicates from being flagged.

Use the TRIM function to clean up your data.

• Add a new column next to your data.
• Enter the formula: =TRIM(A1). Replace A1 with the first cell of your data range.

• Copy the formula down to apply it to all rows.

• Replace the original column with the cleaned data:
  • Copy the cleaned column.

• • Right-click on the original column and select Paste Special > Values only.

#3 Incorrect formulas or ranges

A wrong formula or range can lead to incomplete or inaccurate results. For example, forgetting to lock a range with $ in a formula might apply it inconsistently.

• Double-check your formula. Ensure that ranges are correct and absolute references (e.g., $A$1:$A$100) are used where needed.
• Test the formula on a smaller dataset to confirm it works as expected.
• If using COUNTIF, verify that the range includes all relevant cells and that the formula matches the intended logic.

#4 Merged cells disrupting analysis

Merged cells can block duplicate checks. Google Sheets can’t properly apply formulas or functions to merged ranges.

Unmerge the cells before checking for duplicates:

• Select the merged cells.

• Go to Format > Merge cells > Unmerge.
• Fill in any gaps caused by unmerging, then reapply your duplicate-checking method.

#5 Hidden characters or non-printable symbols

Sometimes, invisible characters (like non-breaking spaces) sneak into your data, causing duplicates to be missed.

Use the CLEAN function to remove non-printable characters:

• Add a new column.
• Enter the formula: =CLEAN(A1). Replace A1 with the cell reference.

• Copy the formula down for all rows.
• Use the cleaned data for duplicate checks.

#6 Overlapping ranges when using formulas

Using formulas like COUNTIF or QUERY on ranges that overlap can result in unexpected behavior, such as double-counting duplicates.

Separate your data into distinct ranges if you’re analyzing multiple columns or sheets.

Use ARRAYFORMULA to apply consistent logic to larger datasets: less Copy code =ARRAYFORMULA(COUNTIF(A2:A100, A2:A100) > 1). This ensures the entire range is processed uniformly.

The takeaway

Managing duplicates is key to keeping your data clean and accurate. Duplicates can mess up your workflow, throw off your results, and lead to costly mistakes. But with the right tools, cleaning up your data is easy and quick.

We’ve looked at different ways to find and handle duplicates, from simple tools like conditional formatting to advanced methods using formulas and add-ons. Whether you’re dealing with a few rows or large, complex datasets, there’s a solution that works for you.

But don’t stop now—try out the advanced tools like QUERY or ARRAYFORMULA to tackle bigger problems. And remember, data management is ongoing. Keep checking your sheets regularly to catch any new duplicates and keep things accurate.

With these tips, you’ll stay in control of your data and be sure it’s always reliable for making decisions. Now it’s your turn! Go ahead and clean up those spreadsheets today!