How do I remove duplicates in Excel without losing data?

Excel's Remove Duplicates button deletes rows permanently and in place, so work on a copy of your sheet first. To keep the original, copy the column or table to a new sheet, run Remove Duplicates there, or use a COUNTIF helper column to flag duplicates so you can review them before deleting anything.

How do I find duplicates without deleting them?

Use Conditional Formatting. Select the range, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel highlights every duplicate so you can inspect them. Alternatively, add a helper column with =COUNTIF(A:A, A2) — any value greater than 1 appears more than once.

Does Remove Duplicates keep the first or last copy?

Excel's Remove Duplicates always keeps the first occurrence in row order and deletes the later copies. If you need to keep the last copy instead, sort the data so the row you want to keep appears first, or use a tool that lets you choose first or last explicitly.

Why is Remove Duplicates not working in Excel?

The most common cause is invisible differences: trailing spaces, different letter case treated as identical, or numbers stored as text. Excel matching is case-insensitive but space-sensitive. Clean the column with TRIM first, or use a tool that lets you ignore case and surrounding spaces when matching.

How do I remove duplicates across multiple columns?

In the Remove Duplicates dialog, tick every column that together defines a unique record. A row is treated as a duplicate only when the values in all of the ticked columns match another row. Untick columns that should not affect the comparison, such as a notes or timestamp field.

How do I remove duplicates in Excel based on one column?

In the Remove Duplicates dialog, untick every column except the one that defines a unique record — for example, Email. Excel then treats two rows as duplicates whenever that single column matches, keeps the first occurrence, and deletes the rest even when the other columns differ. This is the right setting when one field is the true identity of a row and everything else is supporting detail.

How do I count how many duplicates are in Excel before removing them?

Add a helper column with =COUNTIF(A:A, A2) and drag it down: any result above 1 marks a repeated value. To get a single total, use =SUMPRODUCT((COUNTIF(A2:A100, A2:A100)>1)*1), which counts every cell involved in a duplicate. Checking the count first tells you how much the file will shrink, so the Remove Duplicates pass holds no surprises.

What is the keyboard shortcut to remove duplicates in Excel?

There is no single dedicated key, but the ribbon sequence Alt, A, M opens the Remove Duplicates dialog on Windows — press and release Alt, then A for the Data tab, then M. On a Mac there is no equivalent accelerator, so use Data → Remove Duplicates from the ribbon. Excel still asks which columns to match on, so the shortcut saves the click but not the decision.

How do I remove duplicates in Excel using Power Query?

Load the range with Data → From Table/Range, select the columns that define a duplicate, then use Home → Remove Rows → Remove Duplicates and click Close & Load. The step is stored with the query, so a refresh re-runs it on next month's file. Two differences from the ribbon button matter: Power Query is case-sensitive, so apply a lowercase transform first if ANNA and anna should collapse, and it does not guarantee that the first instance is the row kept, so sort deliberately if that choice matters.

How many rows can Excel remove duplicates handle?

A worksheet holds at most 1,048,576 rows by 16,384 columns, so that is the ceiling for anything Remove Duplicates can see. A CSV export larger than that is silently truncated when you open it, which means the duplicates in the cut-off rows are never compared. Deduplicating the CSV before it reaches Excel avoids the problem — a browser-based pass reads the whole file regardless of row count, and the file stays on your machine.

What is the difference between filtering for unique values and removing duplicates in Excel?

Filtering is reversible and removing is not. Data → Advanced with 'Unique records only' ticked hides the repeated rows for as long as the filter is applied, so the data is all still there; Data → Remove Duplicates deletes the repeated rows from the sheet. Microsoft recommends filtering or conditionally formatting for unique values first, to confirm the result is what you expect before anything is deleted.

How do I copy unique values to a new range without changing the original?

Select the range, open Data → Advanced, choose 'Copy to another location', enter a destination cell in the 'Copy to' box, tick 'Unique records only', and click OK. Excel writes a duplicate-free copy at the destination and leaves the source range exactly as it was, which is the safe option when you need to show both the raw list and the cleaned one.

Does Remove Duplicates delete the whole row or just the matching cell?

The whole row. Microsoft is explicit that data is removed from all columns even when you do not tick all of them at the column-selection step — the ticked columns decide only what counts as a match, and once a row matches, the entire row is deleted. So unticking a notes column does not protect the notes; it just stops that column from breaking the comparison. Copy the range to another worksheet first if you need the dropped rows back.

Can I find duplicates in Excel across two sheets?

Remove Duplicates only looks at one range at a time, so cross-sheet checks need a formula. In the first sheet, add =COUNTIF(Sheet2!$A$2:$A$1000, A2)>0 — TRUE means the value in that row also exists in Sheet 2. For a whole-file comparison it is quicker to stack both sheets into one CSV and deduplicate the combined column in a single pass.

How to remove duplicates in Excel

To remove duplicates in Excel, select your data, open the Data tab, and click Remove Duplicates. Tick the columns that define a duplicate, then click OK — Excel keeps the first copy of each row and deletes the rest. For a faster, error-proof option, Sigmera removes duplicate rows from a CSV in one click, entirely in your browser with no upload.

Last updated: August 2026

Remove duplicates in Excel (the manual way)

Excel has a built-in Remove Duplicates command that deletes repeated rows in place. It is the fastest manual route and works in every modern version of Excel (2010 and later, plus Microsoft 365).

1. Select your data. Click any cell inside your table, or highlight the exact range you want to clean, including the header row.
2. Open the Data tab. On the ribbon, click Data, then click Remove Duplicates in the Data Tools group.
3. Choose the columns to match on. In the dialog, tick only the columns that define a duplicate. Tick My data has headers if your first row is a header so it is not treated as data.
4. Confirm and review. Click OK. Excel keeps the first occurrence of each row, deletes the rest, and reports how many duplicates were removed and how many unique values remain.

Find duplicates first with COUNTIF or Conditional Formatting

If you want to review duplicates before deleting them, add a helper column with =COUNTIF(A:A, A2) and drag it down. Any value greater than 1 appears more than once. Alternatively, select the range and use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values to colour every repeated entry without changing anything.

Excel formula to identify duplicates

Three formulas cover almost every case. Put one in a helper column next to your data, drag it down, then filter that column to TRUE to see every repeated row before you delete anything.

=COUNTIF($A$2:$A$1000, A2)>1 — TRUE when the value in A2 appears more than once. The dollar signs lock the range so it does not drift as you fill down.
=COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2)>1 — the multi-column version. TRUE only when the same first-name and last-name pair repeats, so two different people called Smith stay put.
=SUMPRODUCT(--EXACT($A$2:$A$1000, A2))>1 — case-sensitive matching. COUNTIF treats ANNA@site.com and anna@site.com as the same value; EXACT does not.

Two reasons Remove Duplicates skips rows

Microsoft documents two limits that catch people out. You cannot run Remove Duplicates on outlined or subtotaled data at all — the outline and the subtotals have to be removed first. And the comparison uses what is displayed in the cell, not the value stored underneath, so the same date shown as 3/8/2006 in one row and Mar 8, 2006 in another counts as two unique values (Microsoft 365 documentation). Standardise the column format first, or match on a field that cannot be reformatted, such as an email address.

Remove duplicates without deleting rows: UNIQUE and Power Query

The Remove Duplicates button edits the sheet in place. Two other routes leave the original data untouched, which matters when someone else will ask to see the rows you dropped.

The =UNIQUE(A2:A1000) function spills a duplicate-free copy of the range into a new area and updates itself when the source changes. It is available in Microsoft 365, Excel 2021 and Excel 2024 — earlier versions do not have it (Microsoft 365 documentation). Add a third argument of TRUE — =UNIQUE(A2:A1000,,TRUE) — to return only the values that appear exactly once, which is the fast way to see what is genuinely unrepeated.

Power Query is the option for a file you clean repeatedly. Load the table with Data → From Table/Range, then use Home → Remove Rows → Remove Duplicates. The step is saved, so next month’s export runs through the same cleanup on refresh. Two behaviours differ from the ribbon button and both bite in practice: Power Query compares values case-sensitively, so Anna and anna both survive unless you apply a lowercase transform first, and it does not guarantee that the first instance is the one kept (Power Query documentation). If which copy survives matters — the newest record, the one with a phone number filled in — sort before the step or pick a method that lets you state the rule.

Preview the unique rows first with Advanced Filter

Microsoft’s own guidance is to look at the unique values before deleting anything, because filtering for unique values only hides the repeats for as long as the filter is on, while Remove Duplicates deletes them for good (Microsoft 365 documentation). Advanced Filter is how you take that look, and it works in every desktop version of Excel, including the ones without the UNIQUE function.

1. Select the range, headers included, then open Data → Advanced.
2. Pick where the result goes. Filter the list, in-place hides the repeats inside your sheet and changes nothing. Copy to another location asks for a destination in the Copy to box and writes a duplicate-free copy there, leaving the source range untouched.
3. Tick Unique records only and click OK.

The filter compares whole rows across every column in the selected range, so two records that differ in a single notes field both survive. When one field carries the identity of a record — an email address, a customer ID — select that column alone before opening the dialog. This is the step to run when the file is heading into a CRM or a mail merge and someone will ask which rows you dropped: a filtered view can be cleared and re-checked, whereas an in-place deletion cannot be argued with after the fact. If the file is still a raw export, the same before-and-after review is built into our CSV duplicate remover, which flags the repeated rows and lets you choose which copy to keep before anything is removed — in the browser, with the file never leaving your device.

Two places duplicate-matching behaves differently

Both are documented by Microsoft and neither is surfaced in the dialog, so they only show up as a result that looks wrong.

Conditional formatting cannot flag duplicates inside a PivotTable. Fields in the Values area of a PivotTable report cannot be conditionally formatted by unique or duplicate values at all. If you need to see repeats behind a pivot, run the check on the source range before the PivotTable is built, not on the report.
Half-width and full-width characters are treated as identical. In double-byte character set languages such as Japanese, Chinese and Korean, Excel gives halfwidth and fullwidth forms the same treatment when it compares values — so two rows you can see are typed differently collapse into one. That is usually what you want in a contact list, and a nasty surprise when the width itself carries meaning.

Microsoft’s standing advice around both cases is the blunt one: copy the original range to another worksheet or workbook before removing duplicate values, because the removal is permanent (Microsoft 365 documentation).

Remove duplicates in Google Sheets

Google Sheets has its own one-click command:

1. Select the range you want to deduplicate, including headers.
2. Open Data → Data cleanup → Remove duplicates.
3. Confirm headers and columns, then click Remove duplicates. Sheets reports how many rows were removed.

You can also use the formula =UNIQUE(A2:A) to spill a duplicate-free copy of a column into a new range, leaving the original untouched.

Faster way → do it in one click with the free Remove Duplicates tool, no upload. Choose first or last copy, ignore case and spacing, and preview every flagged row before you download.

Do this in 1 click — free

There is a hard ceiling worth knowing before you start: a worksheet holds 1,048,576 rows by 16,384 columns (Excel specifications and limits). Open a bigger CSV and the extra rows never load, so Remove Duplicates never compares them — the file looks clean and is not. Deduplicating the CSV first sidesteps that: the browser reads every row, and nothing is uploaded.

Once the sheet is deduplicated, most importers want plain text rather than a workbook. You can export the sheet to CSV in the same browser tab — no upload, and the UTF-8 output keeps accented names intact.

One caveat if the column you deduplicated holds email addresses: Excel’s Remove Duplicates compares the cell text, so Sam@Acme.com and sam@acme.com survive as two rows. Lowercase and trim the column first, then remove duplicate emails in one pass — the cleaner normalises the address before it compares, which is the order that actually collapses the repeats.

If the list arrived as a .csv and you would rather dedupe it in a real workbook, do the format step first: convert the CSV to Excel and the columns land already split and typed, so Remove Duplicates compares the values you meant rather than whatever Excel guessed on the way in.

Manual Excel vs Sigmera, compared

Method	Time	Handles edge cases	Skill needed
Sigmera (this tool)	Seconds, one click	Ignore case & spacing, choose first/last copy, live preview	None
Excel Remove Duplicates	A minute (find the menu)	Space-sensitive; keeps first only; deletes in place	Low (menus)
COUNTIF formula	Several minutes	Flags only; you still filter and delete manually	Medium (formulas)

Frequently asked questions

How do I remove duplicates in Excel without losing data?: Excel's Remove Duplicates button deletes rows permanently and in place, so work on a copy of your sheet first. To keep the original, copy the column or table to a new sheet, run Remove Duplicates there, or use a COUNTIF helper column to flag duplicates so you can review them before deleting anything.
How do I find duplicates without deleting them?: Use Conditional Formatting. Select the range, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel highlights every duplicate so you can inspect them. Alternatively, add a helper column with =COUNTIF(A:A, A2) — any value greater than 1 appears more than once.
Does Remove Duplicates keep the first or last copy?: Excel's Remove Duplicates always keeps the first occurrence in row order and deletes the later copies. If you need to keep the last copy instead, sort the data so the row you want to keep appears first, or use a tool that lets you choose first or last explicitly.
Why is Remove Duplicates not working in Excel?: The most common cause is invisible differences: trailing spaces, different letter case treated as identical, or numbers stored as text. Excel matching is case-insensitive but space-sensitive. Clean the column with TRIM first, or use a tool that lets you ignore case and surrounding spaces when matching.
How do I remove duplicates across multiple columns?: In the Remove Duplicates dialog, tick every column that together defines a unique record. A row is treated as a duplicate only when the values in all of the ticked columns match another row. Untick columns that should not affect the comparison, such as a notes or timestamp field.
How do I remove duplicates in Excel based on one column?: In the Remove Duplicates dialog, untick every column except the one that defines a unique record — for example, Email. Excel then treats two rows as duplicates whenever that single column matches, keeps the first occurrence, and deletes the rest even when the other columns differ. This is the right setting when one field is the true identity of a row and everything else is supporting detail.
How do I count how many duplicates are in Excel before removing them?: Add a helper column with =COUNTIF(A:A, A2) and drag it down: any result above 1 marks a repeated value. To get a single total, use =SUMPRODUCT((COUNTIF(A2:A100, A2:A100)>1)*1), which counts every cell involved in a duplicate. Checking the count first tells you how much the file will shrink, so the Remove Duplicates pass holds no surprises.
What is the keyboard shortcut to remove duplicates in Excel?: There is no single dedicated key, but the ribbon sequence Alt, A, M opens the Remove Duplicates dialog on Windows — press and release Alt, then A for the Data tab, then M. On a Mac there is no equivalent accelerator, so use Data → Remove Duplicates from the ribbon. Excel still asks which columns to match on, so the shortcut saves the click but not the decision.
How do I remove duplicates in Excel using Power Query?: Load the range with Data → From Table/Range, select the columns that define a duplicate, then use Home → Remove Rows → Remove Duplicates and click Close & Load. The step is stored with the query, so a refresh re-runs it on next month's file. Two differences from the ribbon button matter: Power Query is case-sensitive, so apply a lowercase transform first if ANNA and anna should collapse, and it does not guarantee that the first instance is the row kept, so sort deliberately if that choice matters.
How many rows can Excel remove duplicates handle?: A worksheet holds at most 1,048,576 rows by 16,384 columns, so that is the ceiling for anything Remove Duplicates can see. A CSV export larger than that is silently truncated when you open it, which means the duplicates in the cut-off rows are never compared. Deduplicating the CSV before it reaches Excel avoids the problem — a browser-based pass reads the whole file regardless of row count, and the file stays on your machine.
What is the difference between filtering for unique values and removing duplicates in Excel?: Filtering is reversible and removing is not. Data → Advanced with 'Unique records only' ticked hides the repeated rows for as long as the filter is applied, so the data is all still there; Data → Remove Duplicates deletes the repeated rows from the sheet. Microsoft recommends filtering or conditionally formatting for unique values first, to confirm the result is what you expect before anything is deleted.
How do I copy unique values to a new range without changing the original?: Select the range, open Data → Advanced, choose 'Copy to another location', enter a destination cell in the 'Copy to' box, tick 'Unique records only', and click OK. Excel writes a duplicate-free copy at the destination and leaves the source range exactly as it was, which is the safe option when you need to show both the raw list and the cleaned one.
Does Remove Duplicates delete the whole row or just the matching cell?: The whole row. Microsoft is explicit that data is removed from all columns even when you do not tick all of them at the column-selection step — the ticked columns decide only what counts as a match, and once a row matches, the entire row is deleted. So unticking a notes column does not protect the notes; it just stops that column from breaking the comparison. Copy the range to another worksheet first if you need the dropped rows back.
Can I find duplicates in Excel across two sheets?: Remove Duplicates only looks at one range at a time, so cross-sheet checks need a formula. In the first sheet, add =COUNTIF(Sheet2!$A$2:$A$1000, A2)>0 — TRUE means the value in that row also exists in Sheet 2. For a whole-file comparison it is quicker to stack both sheets into one CSV and deduplicate the combined column in a single pass.