How to find duplicates in Google Sheets

Duplicates in a spreadsheet can mess up your data, skew your results, or simply make things harder to read. Fortunately, Google Sheets makes it easy to find and manage duplicate values using built-in tools — no complex scripts required.

Here’s how to quickly find duplicates in Google Sheets using simple methods anyone can follow.

✅ Method 1: Use Conditional Formatting to Highlight Duplicates

This is the fastest way to visually spot duplicates in a column or range.

Step-by-Step:

1. Select the column or range where you want to check for duplicates
2. Click Format > Conditional formatting
3. In the sidebar under Format cells if, choose Custom formula is
4. Enter this formula (for column A): =COUNTIF(A:A, A1) > 1
5. Choose a highlight color
6. Click Done

All duplicate values will be instantly highlighted.

✅ Method 2: Use the UNIQUE and COUNTIF Functions

If you want a list of duplicates or to flag them in another column, you can use formulas.

Example: Flag duplicates in Column A

1. In Column B (next to your data), enter this formula: =IF(COUNTIF(A:A, A1) > 1, "Duplicate", "")
2. Drag the formula down to apply it to the whole list

This will label each row as “Duplicate” if the value appears more than once.

✅ Method 3: Use Google Sheets’ “Remove Duplicates” Tool

Want to actually delete duplicates (not just find them)?

1. Select your data range
2. Click Data > Data cleanup > Remove duplicates
3. Check the boxes:
   • Data has header row (if applicable)
   • Select columns to check
4. Click Remove duplicates

Google Sheets will tell you how many duplicates were found and removed.

⚠️ Tip: Always make a copy of your data before removing duplicates — changes are permanent.

✅ Method 4: Use UNIQUE() to Extract Non-Duplicates

If you want a clean list of unique values, use:

=UNIQUE(A:A)

This creates a new list excluding all duplicates — perfect for summaries or clean data sets.

🔄 Bonus Tip: Combine with Filters or Sorting

After labeling duplicates with a formula, you can:

• Use the Filter tool to show only duplicates
• Sort your data to group duplicates together for easy review or deletion

Final Thoughts

Cleaning up duplicates in Google Sheets doesn’t require complicated scripts or add-ons. Whether you’re working with sales leads, sign-ups, product lists, or survey data, these built-in tools help you keep everything accurate and organized.

Try these methods the next time your sheet starts getting messy — your future self will thank you.