How to remove duplicate rows in Excel, Google Sheets and Notepad++

Three apps, four methods, and the one setting that silently deletes the wrong rows.

In Excel, select any cell in the range and go to Data → Remove Duplicates, then tick the columns that define a duplicate. In Google Sheets it is Data → Data cleanup → Remove duplicates. In Notepad++ install the TextFX plugin and use TextFX → TextFX Tools → Sort lines case sensitive and remove duplicates. All three keep the first occurrence and delete the rest.

Excel, step by step

1. Click any cell inside your data. Excel expands the selection automatically, which is convenient and also the source of most accidents — check the highlighted range before you continue.
2. Data tab → Remove Duplicates in the Data Tools group.
3. Tick My data has headers if row 1 is a header, otherwise Excel treats it as data and may delete it.
4. Tick only the columns that define a duplicate. Leaving every column ticked means a row is only removed when all values match — a different phone number on the same email survives.
5. Press OK. Excel reports how many duplicates were removed and how many unique values remain.

This deletes rows in place and cannot be undone once the file is saved and closed. Copy the sheet first, or at minimum note the original row count.

Non-destructive alternative in Excel 365 and 2021: =UNIQUE(A2:A500) spills a clean list into a new column and updates when the source changes. To find duplicates without deleting anything, use conditional formatting: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, or add a helper column with =COUNTIF($A$2:$A$500,A2)>1 and filter on TRUE.

Google Sheets

Select the range, then Data → Data cleanup → Remove duplicates. The dialog has the same two decisions: does the data have a header row, and which columns are compared. Sheets tells you afterwards how many duplicate rows were removed and how many remain.

The formula route is better if the source list keeps changing: =UNIQUE(A2:A) in an empty column gives a live deduplicated list. Combine it with =SORT(UNIQUE(A2:A)) to get a sorted one in a single formula. To count how often each value appears, =COUNTIF(A:A,A2) is the same as in Excel.

Data cleanup also has Trim whitespace right above it. Run that first. Leading and trailing spaces are the reason two visually identical entries are not treated as duplicates.

Notepad++ and plain text lists

For a list that is not in a spreadsheet — emails, URLs, keywords, log lines — Notepad++ has two routes.

Built in, no plugin: Edit → Line Operations → Remove Consecutive Duplicate Lines. The word consecutive is the catch: it only removes repeats that are next to each other, so sort the file first with Edit → Line Operations → Sort Lines Lexicographically Ascending.

With the TextFX plugin: select all, then TextFX → TextFX Tools → Sort lines case sensitive and remove duplicates. One step, but it sorts the list and you lose the original order. Install TextFX from Plugins → Plugins Admin; it is not bundled in 64-bit builds by default.

Regex route, keeps order, no plugin: Find & Replace, Regular expression mode, find ^(.*?)$\s+?^(?=.*^\1$) and replace with nothing. It is slow on files over a few thousand lines and it is easy to mistype. On the command line, awk '!a[$0]++' in.txt > out.txt does the same job, keeps order, and handles a million lines.

If you just want it done in the browser without installing a plugin or writing a regex, paste the list into a duplicate line remover — it keeps the first occurrence and preserves the original order, which is what the sort-based methods take away.

Which method for which job

What usually goes wrong

• Trailing spaces. john@mail.com and john@mail.com  are different strings. Trim first — Sheets has Trim whitespace, Excel has =TRIM(), or run the list through a whitespace cleaner.
• Case. Excel Remove Duplicates ignores case, so Apple and apple are one row. Notepad++ TextFX in case-sensitive mode keeps both. Decide which you want and, if you want them merged, convert the column to lowercase before deduplicating.
• Only one column selected when the sheet has more. Excel warns you and offers to expand the selection. If you say no, it deletes cells in that column only and every other column shifts out of alignment. This is the single most damaging mistake in this whole article.
• Header row treated as data. Untick or tick My data has headers deliberately; the default is guessed from the content.
• Hidden rows and filters. Remove Duplicates operates on the whole range, including rows hidden by a filter. Clear filters before you run it.
• Formulas compared by value, not formula. Two different formulas producing 100 are duplicates. Paste as values if that is not what you want.
• Look-alike characters. A Cyrillic а and a Latin a render identically and never match. If a duplicate refuses to be found, that is usually why.

Cleaning a list properly, in order

The sequence matters more than the tool. Do it in this order and the deduplication step becomes trivial:

1. Trim leading and trailing whitespace.
2. Normalise case if case should not distinguish entries.
3. Remove empty lines.
4. Deduplicate.
5. Sort if you need a browsable list, and only then — sorting first throws away the original order for nothing.

Record the count before and after. A 12,000-row export that drops to 11,940 is plausible; one that drops to 3,100 means your comparison column was the wrong one.

Related tools

See all text tools →

Frequently asked questions