You can find duplicates in Google Sheets in a few different ways, depending on whether you want to just see them, highlight them, or actually remove them.

How to Find Duplicates in Google Sheets

(Quick Scoop Guide)

1. Fastest method: built‑in “Remove duplicates”

If you mainly want to clean a list (like emails or IDs), this is the quickest way.

  1. Select the range you want to check (for example A1:A500 or a whole table).
  2. Go to Data → Data cleanup → Remove duplicates.
  3. In the dialog, choose:
    • Whether your data has a header row.
    • Which columns to use to detect duplicates.
  4. Click Remove duplicates.

What happens:

  • Google Sheets scans rows using the columns you selected.
  • It keeps the first occurrence and deletes the others.
  • It shows how many duplicates were removed and how many unique rows remain.

Use this when you’re confident you don’t need to review every duplicate manually.

2. Highlight duplicates with Conditional Formatting

This is perfect when you want to see duplicates but not delete them yet.

A. Duplicates in a single column

Say your data is in column A (A2:A100):

  1. Select the range (e.g. A2:A100).
  2. Click Format → Conditional formatting.
  3. Under Format cells if , choose Custom formula is.
  4. Use this formula (assuming your first data cell is A2):

=COUNTIF($A$2:$A$100,A2)>1

  1. Pick a fill color (e.g. light red) and click Done.

All cells in that range that appear more than once will be highlighted.

B. Duplicates across multiple columns (same row)

If a row is a “duplicate” only when several columns match (for example, same Name and Email):

Assume data is in columns A–B, rows 2–100.

  1. Select the whole region: A2:B100.
  2. Format → Conditional formatting → Custom formula is.
  3. Use:

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

  1. Choose a style and click Done.

Now any row where the combination of A and B repeats will be highlighted.

C. Duplicates anywhere in a larger grid

For a big block (say A2:Z1000), some tutorials use a formula like:

=COUNTIF($A:$Z,INDIRECT(ADDRESS(ROW(),COLUMN(),)))>1

This checks whether the current cell’s value appears more than once anywhere in that full range. It’s more advanced but useful if duplicates can appear in different columns.

3. List duplicates with formulas (without deleting)

Sometimes you want a separate list of values that are duplicated, instead of highlighting.

A. Single column duplicate values

Say your original data is in A2:A100. To list each value that appears more than once, without repeats in the result, you can use a combination of FILTER, COUNTIF, and UNIQUE:

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

  • COUNTIF(A2:A100, A2:A100) counts how many times each value appears.
  • FILTER keeps only those with a count greater than 1.
  • UNIQUE makes sure each duplicated value appears only once in the output list.

Place this formula in another column (for example D2), and you’ll get a clean list of “problem” values that occur multiple times.

B. Filter rows that are duplicates

If you want the full rows that are duplicated (not just the value):

  • Add a helper column (say column Z) with a formula like:

=COUNTIF($A$2:$A$100,A2)

(Assuming column A is your key.)

  • Then filter the sheet where this helper column is greater than 1 to see only rows with duplicates.

This is handy when reviewing duplicate records before cleaning them up.

4. Using add‑ons and tools (for large or complex sheets)

When your sheet is big or logic is complex, dedicated tools can save time.

Popular approaches include:

  • Marketplace add‑ons such as “Remove Duplicates” that:
    • Find duplicates or uniques.
    • Highlight or delete duplicates.
    • Keep only first or last occurrence.
    • Move duplicates to another sheet.
  • Some tools let you:
    • Save your duplicate‑search settings as scenarios.
    • Schedule them to run automatically on a schedule.

These are useful if you regularly clean big spreadsheets, like weekly imports or CRM exports.

5. Practical tips so duplicates are caught correctly

A few small details can make a big difference:

  • Exclude headers : Don’t include header rows in your ranges for formulas or conditional formatting.
  • Clean spaces : Extra spaces before/after values make them look different. Use TRIM() or cleanup tools first.
  • Decide your “key” : For people, is the key Email, ID, or Name+Birthdate? Choose the column(s) that actually define a duplicate.
  • Backup before deletion : Duplicate removal is hard to undo when many rows are involved; copying to another sheet first is a safer workflow.

6. Mini “story” example

Imagine you run an online course and your signup sheet has 1,500 emails. You notice people signed up twice with the same email but slightly different names.

  • You first use Conditional formatting + COUNTIF on the email column to highlight duplicates so you can visually check whether they’re real duplicates or shared addresses.
  • Then, once you’re confident, you use Data → Data cleanup → Remove duplicates , selecting only the Email column as the key so that each email appears once.
  • Finally, you keep a backup sheet with all original entries so you can restore any genuinely different users who accidentally got removed as duplicates.

This workflow balances safety and cleanliness without too much complexity.

SEO bits: focus keyword and meta description

Suggested meta description (under 160 characters):
Learn how to find duplicates in Google Sheets using conditional formatting, formulas, and built‑in tools, plus tips and add‑ons for large, messy data.

Main focus keyword used: “how to find duplicates in google sheets”.