How to Remove Duplicates in Excel (Keep First, Delete Rest)

Published October 18, 2026 · By Ahmad Parvez

Short answer: click anywhere in your data, go to the Data tab → Remove Duplicates → choose which columns to compare → OK. Excel keeps the first occurrence of every value and deletes the rest, then tells you how many duplicates it removed. Work on a copy first — once you save and close, there is no undo.

Duplicate rows sneak into every spreadsheet: pasted lists, merged exports, sign-up forms submitted twice. This guide covers how to remove duplicates in Excel the fast way, how to find duplicates before you delete anything, and the edge cases that trip people up — case-sensitivity, invisible spaces, merged cells, and the "entire row vs. one column" choice that changes everything. It also clears up a common mix-up: several "duplicate" searches are actually about copying a sheet or file, which is a different thing entirely (covered near the end).

How to remove duplicates in Excel: the 3-click method

This is the built-in button most people are looking for, and it works the same in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel for the web:

Select your data. Click any cell inside the table, or drag-select the exact range you want to dedupe. Selecting a range yourself is safer than clicking one cell — Excel guesses the boundary of your data, and a stray blank row can make it guess wrong (more on that below).
Data tab → Remove Duplicates. It sits in the Data Tools group, next to Text to Columns. In Excel for the web, the path is the same: Data → Remove Duplicates.
Choose columns, then OK. A dialog lists every column with a checkbox. Checked columns are the ones Excel compares: two rows count as duplicates (duplicate records) only if all checked columns match. Leave everything checked to remove only fully identical rows. Click OK, and Excel reports the result, e.g. "3 duplicate values found and removed; 97 unique values remain."

One detail worth knowing: the "My data has headers" checkbox at the top of that dialog. If your first row is a header, leave it checked so Excel doesn't treat it as data. If your data has no header row and you leave it checked, the first data row gets excluded from comparison — and if a duplicate lived there, it survives.

How to remove duplicate rows in Excel (entire row vs. selected columns)

The column checkboxes in the Remove Duplicates dialog are the single most misunderstood part of the feature. They answer the question: what counts as a duplicate?

All columns checked (default): only rows that are identical in every column get removed. Safest option.
One column checked, e.g. "Email": any two rows with the same email count as duplicates — even if the names, dates or amounts in the other columns differ. Excel keeps the first row and deletes the others, taking their differing data with them.

"Uncheck a column only when you genuinely don't care about the data in it. Checking just the Email column to dedupe a customer list will delete rows that had different order totals attached."— The rule of thumb for the column dialog.

Practical advice: if you only care about one column but the rest of the row matters, don't uncheck everything — find the duplicates first (next section), review them, and delete manually or with a filter. Getting rid of duplicates in Excel is easy; getting rid of the right duplicates takes one extra minute of checking.

How to remove duplicates in a column in Excel

When you only want to dedupe a single column — say a list of emails or product SKUs:

Select just that column (click its letter header, e.g. column A).
Data → Remove Duplicates. The dialog will show only that column checked.
Click OK.

Warning: removing duplicates from a column deletes entire rows, not just the cell. If column B has notes attached to the deleted rows, those notes are gone too. If the surrounding data matters, use the highlight-and-filter method below instead, so you can see what's attached before anything disappears.

How to find and identify duplicates in Excel before you delete anything

Smart workflow: look for duplicates first, then decide whether to delete them. There are two reliable ways — a no-formula visual method and a formula method.

Method 1: Conditional Formatting (no formulas)

Select the range where you want to search for duplicates.
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. (Same path in Excel for the web.)
Pick a color format and click OK. Every value that appears more than once is colored; unique values stay untouched.

This is also how you show duplicates, view duplicates, mark duplicates or flag duplicates in Excel — it is the same feature under different names. To find duplicate names specifically, select just the name column before applying it. To find duplicates across multiple columns, select the whole table: any value repeated anywhere in the selection gets highlighted.

Want to act on the highlighted cells? Select the range, then Home → Sort & Filter → Custom Sort → Sort Options → sort by Cell Color (choose the duplicate color first). Now all duplicates sit together, and you can review or delete them as a group. That covers "how to sort duplicates in Excel" and "how to sort out duplicates" too.

Method 2: The COUNTIF formula

Add a helper column next to your data with this formula (assuming data starts in A2):

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

The $ signs lock the range so it doesn't shift as you fill down. The formula returns TRUE for every value that appears more than once. Then Data → Filter on the helper column for TRUE — there's your duplicate list, without deleting a thing. To find duplicate rows across two columns (say first name in A and last name in B), use COUNTIFS instead:

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

For a ready-made version, the Excel Formula Builder generates the COUNTIF and COUNTIFS flagging formulas for your exact ranges — plus the UNIQUE formula below. Its COUNTIF and UNIQUE entries explain each argument step by step.

How to highlight duplicates in Excel (and what to do next)

Highlighting deserves its own section because it answers a different question than deleting: where are my duplicates? The core path is Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, and a few refinements make it far more useful:

Highlight duplicates in one column only: select that column first. This is how you highlight duplicate values in a column, e.g. duplicate invoice numbers.
Highlight the unique values instead: in the Duplicate Values dialog, switch the dropdown from "Duplicate" to "Unique" — useful when you want to see what survives a dedupe.
Have Excel highlight duplicates as you type: the formatting is live. New entries that duplicate an existing value get colored the moment you press Enter, which makes conditional formatting a lightweight duplicate prevention system. For hard prevention, use Data → Data Validation → Custom with =COUNTIF($A:$A, A1)=1 to reject duplicate entries outright.
Clear the colors when done: Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells. The colors are formatting only — clearing them never deletes data.

How to filter out duplicates in Excel (hide them without deleting)

Sometimes you don't want to delete duplicates — you want them out of sight. Two options:

Advanced Filter: "Unique records only"

Select your data, then go to Data → Sort & Filter → Advanced.
Check "Unique records only" and click OK. Excel hides the duplicate rows in place — the data is still there, just filtered from view. Clear the filter (Data → Clear) to bring them back.
Alternatively, choose "Copy to another location" in the same dialog and check "Unique records only": Excel extracts a clean, deduped list to a new spot, leaving the original untouched.

Note: Advanced Filter is a desktop-Excel feature; Excel for the web doesn't have it. And like Remove Duplicates, its comparison is case-insensitive.

The UNIQUE formula (Microsoft 365 / Excel 2021+)

If your Excel is current, the cleanest way to view a deduplicated list is a single formula:

=UNIQUE(A2:A100)

It spills a list of each value once — no deletions, no dialogs, and it updates automatically when the source changes. Wrap it in SORT for an alphabetical clean list: =SORT(UNIQUE(A2:A100)). This is the modern answer to "how to filter duplicates in Excel" and "how to eliminate duplicates" for reporting.

How to find duplicates in Excel with multiple columns

Real-world duplicates are rarely one column. Two customers named "Ahmad Parvez" are different people if their phone numbers differ. Three approaches, from simplest to most robust:

Remove Duplicates dialog: leave the relevant columns checked — rows must match on all of them to count as duplicates. Fastest when you want to delete.
COUNTIFS helper column: =COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1 flags rows where the combination repeats. Add more column pairs for three or four columns.
Concatenated key + conditional formatting: in a helper column, join the fields with a separator — =A2&"|"&B2&"|"&C2 — then run Duplicate Values highlighting on that column. Easy to scan visually.

Edge cases most guides skip (read this before you click OK)

This is where duplicate removal goes wrong in practice. Every one of these is real behavior in Excel:

Excel keeps the FIRST occurrence and deletes later ones. Which row survives depends on your current sort order. If you want to keep the newest order per customer, sort by date newest-first before removing duplicates.
Matching is case-INSENSITIVE. "Apple", "APPLE" and "apple" are the same value to Excel — Remove Duplicates, conditional formatting and COUNTIF all agree on this. If case matters (product codes, passwords), use a helper column with the case-sensitive formula =SUMPRODUCT(--EXACT($A$2:$A$100, A2))>1 and filter for TRUE.
Leading and trailing spaces break matching. "Apple " (with a trailing space) is not a duplicate of "Apple" — they look identical but won't be removed. Clean first: put =TRIM(A2) in a helper column, fill down, then Paste Special → Values back over the original before deduping.
Merged cells stop it cold. Remove Duplicates will not run on a range containing merged cells — Excel throws an error asking you to unmerge them. Select the range, go to Home → Merge & Center dropdown → Unmerge Cells first. (Tip: after unmerging, use Go To Special → Blanks to fill the gaps if you need the values repeated.)
Blank rows can hide duplicates. If you click a single cell instead of selecting the full range, Excel expands to the surrounding block of filled cells — a completely blank row or column ends that block, and everything past it is silently ignored. Select the whole range yourself before clicking Remove Duplicates.
There is no undo after save + close. Ctrl+Z works immediately after a dedupe, but once the workbook is saved and closed, the deleted rows are gone for good. Always duplicate the sheet first: right-click the sheet tab → Move or Copy → Create a copy.
Numbers stored as text don't match numbers. The text "123" and the number 123 are different values to Excel. If a column mixes both (common in pasted data), select the column → Data → Text to Columns → Finish to normalize it to numbers first.

How to combine, merge, and count duplicates in Excel

Not every duplicate problem is a deletion problem. Sometimes you want to consolidate duplicates — add up the repeat orders per customer, or count how many times each value appears:

Count duplicates: the fastest way is a PivotTable. Select your data → Insert → PivotTable, drag the field into both Rows and Values. Every unique value appears once with its count; anything above 1 was duplicated. With a formula: =COUNTIF(A:A, A2)-1 tells you how many extra copies of the value in A2 exist beyond the first.
Combine duplicates (sum the repeats): in the same PivotTable, put the value field in Rows and the amount field in Values (set to Sum) — each duplicate group collapses into one row with its total. With formulas, =SUMIF($A$2:$A$100, A2, $B$2:$B$100) sums column B for every row matching the value in A2.
Merge two files, then dedupe: paste both datasets into one sheet with columns lined up, then run Remove Duplicates. For a repeatable workflow on desktop Excel, use Power Query: Data → Get Data → Combine Queries → Append, then Home → Remove Rows → Remove Duplicates.

The Excel Formula Builder generates the COUNTIF, COUNTIFS, SUMIF and UNIQUE formulas above for your exact ranges, with a how-to for each.

A different kind of "duplicate": copying a sheet, file, or cells

A big chunk of "duplicate" searches mean copying, not deduping. Quick answers so you land in the right place:

How to duplicate a sheet in Excel (a worksheet is just Excel's name for a sheet): right-click the sheet tab → Move or Copy… → check "Create a copy" → OK. The copy appears as "Sheet1 (2)". Shortcut: hold Ctrl and drag the sheet tab left or right. In Excel for the web: right-click the tab → Duplicate.
How to duplicate a page in Excel: this almost always means duplicating the sheet — right-click the sheet tab → Move or Copy → Create a copy. If you mean repeating content across printed pages, use Page Layout → Print Titles instead.
How to duplicate an Excel file / workbook / document: open it and use File → Save a Copy (Excel for the web: File → Save As → Save a Copy). This creates a second file and leaves the original untouched. Copying the file in File Explorer or Finder works too.
How to duplicate cells, a column, or rows: select them, Ctrl+C, click the destination, Ctrl+V. Or hold Ctrl and drag the selection's border to drop a copy elsewhere.
How to repeat a formula down a column: double-click the small square (fill handle) at the cell's bottom-right corner — the formula fills to the last adjacent data row. Select the cell, copy, and paste down a selection works too.
How to repeat the last action: press F4 (or Ctrl+Y). Deleted a row? F4 deletes the next one. Formatted a cell? F4 repeats the format.
How to repeat header rows is a printing question, not a duplicate question: Page Layout → Print Titles → "Rows to repeat at top" makes chosen rows print on every page.

Frequently asked questions

Can I remove duplicates in Excel but keep one copy?

Yes — that is exactly what the Remove Duplicates button does. It keeps the FIRST occurrence of each value and deletes every later copy, so one of each remains. If you need to keep a different copy (for example the most recent order per customer), sort your data first so the row you want to keep comes first, then run Remove Duplicates.

How do I find duplicates in Excel without deleting them?

To identify duplicates in Excel without deleting anything, use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, which colors every repeat. Or add a helper column with =COUNTIF($A$2:$A$100, A2)>1 (adjust the range to your data) and filter for TRUE — the TRUE rows are your duplicates, and nothing has been deleted.

Why doesn't Excel find my duplicates? They look identical.

Almost always invisible differences: a trailing space ('Apple ' vs 'Apple') is the classic culprit. Clean the column first with =TRIM(A2), then paste the results back as values (Paste Special → Values) before removing duplicates. Case differences never matter — 'Apple' and 'apple' already count as duplicates. Numbers stored as text ('123' vs 123) also fail to match; convert the column to one type first.

Can I undo Remove Duplicates in Excel?

Yes, immediately: press Ctrl+Z (Cmd+Z on Mac) right after running it and your deleted rows come back. But once you save AND close the workbook, the deletions are permanent. Always run Remove Duplicates on a copy of your data — duplicate the sheet first (right-click the sheet tab → Move or Copy → Create a copy) so the original stays untouched.

How do I remove duplicates from two merged Excel files?

Stack both datasets into one sheet (paste one under the other, making sure the columns line up), then run Data → Remove Duplicates on the combined range. For a repeatable process, use Power Query: Data → Get Data → Combine Queries → Append the two tables, then Home → Remove Rows → Remove Duplicates. Excel for the web can append ranges manually but has no Power Query; paste-and-dedupe works there too.

How do I count duplicates in Excel?

The fastest way is a PivotTable: select your data → Insert → PivotTable, drag the field into both Rows and Values — each unique value appears with its count, and anything above 1 is a duplicate. With a formula, =COUNTIF(A:A, A2)-1 tells you how many extra copies of the value in A2 exist beyond the first. The Excel Formula Builder generates both the counting and flagging formulas for you.

How do I check for duplicates in a column in Excel?

Select the column, then use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values to color every repeat instantly. For a column you can sort and filter, add a helper column with =COUNTIF($A$2:$A$100, A2)>1 and filter for TRUE. To check while typing, add Data → Data Validation → Custom with the formula =COUNTIF($A:$A, A1)=1, which rejects entries that would create a duplicate.

Is Excel's duplicate detection case-sensitive?

No — Excel's Remove Duplicates, conditional formatting, and COUNTIF are all case-insensitive, so 'Apple', 'APPLE' and 'apple' count as the same value. If case matters to you, use a helper column with =SUMPRODUCT(--EXACT($A$2:$A$100, A2))>1, because the EXACT function is case-sensitive, then filter for TRUE.

How do I duplicate an Excel file?

To duplicate an Excel file (the whole workbook), use File → Save a Copy — this saves a second file and leaves the original untouched. In Excel for the web, use File → Save As → Save a Copy. If you only need a copy of one sheet inside the same workbook, right-click the sheet tab → Move or Copy → check Create a copy → OK.

Keep your clean data clean

Deduping is half the job — the other half is stopping duplicates from coming back. These guides pick up where this one leaves off:

And for the formulas behind flagging, counting and extracting uniques, see the Excel Formula Builder — guided COUNTIF, COUNTIFS, SUMIF and UNIQUE builders with copy-ready formulas.