How to Remove Duplicates in Google Sheets (3 Methods, Edge Cases Included)

You just imported a client list from a CSV, and now maria.lopez@gmail.com appears three times — once exactly right, once with a typo, and once with an invisible trailing space. Before you can trust the list for anything, you need to know how to remove duplicates in Google Sheets without nuking rows you meant to keep.

There are three ways to do it, and they are not interchangeable. One deletes permanently, one leaves your source data untouched, and one shows you the duplicates before you act on them. Picking the wrong one is how people lose data they wanted.

The Fast Way: Data cleanup → Remove duplicates

This is the built-in tool, and for most one-off cleanups it’s the right answer. It takes about ten seconds.

1. Select the range containing your data. Click and drag from the top-left cell of your list to the bottom-right — include the header row in your selection.
2. Click Data → Data cleanup → Remove duplicates.
3. In the dialog that appears, check Data has header row so Sheets doesn't treat your column titles as data.
4. Keep all columns checked to remove rows that are identical across every column — or uncheck the columns you want Sheets to ignore.
5. Click Remove duplicates. Sheets shows a summary: "X duplicate rows found and removed. Y unique rows remain."

What Sheets actually counts as a duplicate

This part surprises people. Per Google’s own documentation, cells with identical values but different letter cases, formatting, or even different formulas that produce the same result are all considered duplicates. So Apple and apple get treated as the same value, and a cell containing =2+2 matches a cell containing 4.

What does not count as a duplicate: invisible trailing spaces. maria.lopez@gmail.com and maria.lopez@gmail.com (with a space at the end) are different values to Sheets. If your cleanup seems to be missing obvious duplicates, run the column through =TRIM(A2) first and try again.

Which columns you check changes the outcome

By default every column is checked, so only rows that match on all columns get removed. The interesting case is when you uncheck everything except one column — say, Email.

Now Sheets treats two rows as duplicates if their emails match, even if the names, phone numbers, and everything else differ. It keeps the first occurrence and deletes the rest, including whatever data those later rows carried. If row 8 has a newer phone number than row 3 but the same email, row 8’s phone number is gone. Check your column choice deliberately — this is the most common way people lose data with this tool.

The Non-Destructive Way: the UNIQUE formula

If you want a clean list but need to keep the original intact — for auditing, for a client deliverable, for a backup — use UNIQUE. It outputs unique values somewhere else and never touches your source.

Click an empty cell away from your data and enter:

=UNIQUE(A2:D100)

That returns the unique row combinations across columns A through D. For a single column of values, just narrow the range: =UNIQUE(A2:A100). Want the result sorted alphabetically in one step? Wrap it:

=SORT(UNIQUE(A2:A100))

Two behaviors worth knowing. First, UNIQUE keeps the first occurrence of each value, same as the menu tool. Second, the output is a live formula — if someone adds rows to the source range later, the unique list updates automatically. That’s either a feature or a trap, depending on whether you expected a snapshot.

The Review-First Way: Conditional Formatting

Sometimes the right move is to see the duplicates before deleting anything — a client list where each duplicate might carry a different note, for instance. Conditional formatting highlights them without changing a single cell.

1. Select the column you want to check, excluding the header row — for example, A2:A100.
2. Click Format → Conditional formatting.
3. In the side panel, under Format cells if…, choose Custom formula is.
4. Enter the formula below, adjusting the range to match yours: =COUNTIF($A$2:$A$100, A2)>1
5. Pick a fill color, then click Done.

Every cell whose value appears more than once in the range gets highlighted. The dollar signs in $A$2:$A$100 lock the range so the formula evaluates consistently across all cells, while the A2 without dollar signs shifts row by row. From here you can review the highlighted rows and delete the ones you don’t need manually — or filter by color and delete them in one pass.

Which Method Should You Use?

If you’re not sure, start with conditional formatting. It costs nothing, answers “how bad is this?” in one step, and you can always graduate to the destructive tool once you’ve looked at what’s highlighted.

When It Goes Wrong: Troubleshooting

The tool “missed” duplicates that look identical. Almost always trailing spaces. Select a blank column next to your data and enter =LEN(A2) for the suspect rows — if two cells that look the same return different lengths, one has a hidden space. Fix with TRIM, then re-run the cleanup.

Your header row disappeared. You skipped the “Data has header row” checkbox, so Sheets treated “Email Address” as just another value and compared it against the data. Undo and re-run with the checkbox ticked.

It kept the wrong copy of each duplicate. The tool always keeps the first occurrence and removes later ones. If you need the last occurrence instead — common when later rows have more recent data — sort your data in reverse order first, run the tool, then sort back.

You selected one cell and it removed duplicates from the whole sheet. That’s intended behavior. Per Google’s documentation, selecting a single cell within a range removes duplicates across the whole range. If you only wanted a subset, select that subset explicitly before opening the dialog.

Formulas further down the sheet broke the moment you clicked Remove. Deleting rows deletes the cells too, so anything pointing at them now shows #REF!. Press Ctrl + Z (or Cmd + Z on Mac) immediately to bring the rows back, or work through the fixes in our guide to the #REF! error — it also covers locking references so the next cleanup doesn’t repeat the damage.

After Your Data Is Clean

Deduplication is usually step one of a bigger job — the cleaned list still needs formatting, follow-ups, or a mail merge. If you find yourself repeating the same cleanup every week on fresh exports, make it a standing appointment instead of a rediscovered chore — time blocking in Google Calendar shows how to give recurring admin like this a slot that survives a busy week.

Frequently Asked Questions

Does Remove duplicates keep the first or last occurrence?

The first. Sheets keeps the topmost copy of each duplicate and deletes every occurrence below it. UNIQUE behaves the same way. If you need the last occurrence kept, sort the data in reverse before running the tool.

Is the duplicate check case-sensitive?

No. Google’s documentation states that cells with identical values but different letter cases are considered duplicates, so Apple and apple count as the same value. Formatting differences don’t matter either — bold versus plain text is ignored.

Can I remove duplicates based on just one column?

Yes. In the Remove duplicates dialog, uncheck every column except the one you want to compare. Rows are then treated as duplicates when that single column matches, regardless of what the other columns contain. Just remember that the deleted rows take their other data with them.

What’s the difference between Remove duplicates and UNIQUE?

Remove duplicates modifies your source data permanently. UNIQUE leaves the original untouched and writes the deduplicated values to a new location as a live formula. Use the menu tool to clean a dataset in place; use UNIQUE when you need the original preserved or a list that updates itself.

How do I find duplicates without deleting anything?

Conditional formatting with a custom COUNTIF formula: select the column, open Format → Conditional formatting, choose “Custom formula is,” and enter =COUNTIF($A$2:$A$100, A2)>1. Duplicates get highlighted in your chosen color and nothing is removed.

Sources:

• Split text, remove duplicates, or trim whitespace — Google Docs Editors Help