How to remove duplicates manually in Excel
Excel has several built-in ways to deal with repeated rows. Each works, but each has a catch worth knowing about before you rely on it.
The Remove Duplicates command
- Click any cell inside your data.
- On the Data tab, in the Data Tools group, click Remove Duplicates.
- Leave My data has headers ticked if the first row holds column names.
- Tick the columns that must match for two rows to count as duplicates. Leaving every column ticked removes only exact copies.
- Click OK. Excel reports how many duplicate values it removed and how many unique values remain.
Excel keeps the first occurrence and deletes the rest in place. It ignores capital letters, so “ACME Ltd” and “acme ltd” match, but it does not ignore spaces: “Priya Shah” and “Priya Shah “ (with a trailing space) are treated as two different people. That trailing space is invisible in the cell, which is why duplicates often survive this command. Run a trim first, or use a tool that ignores spacing while it compares.
Highlight duplicates before deleting
If you would rather look before you delete, select a column and choose Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values. This colours repeated values in that column. It works cell by cell, not row by row, so to highlight whole duplicate rows you need a helper column that joins the key fields, for example =A2&"|"&C2, and then a COUNTIF on that helper column.
The UNIQUE function
In Excel for Microsoft 365 and Excel 2021, =UNIQUE(A2:D500) returns the distinct rows of a range into a new area of the sheet, leaving the original untouched. Like Remove Duplicates, it ignores case but not extra spaces, and the result is a formula, so you need to copy it and use Paste Special › Values before you can edit or sort it freely.
Advanced Filter and Power Query
Older versions can use Data › Advanced with Copy to another location and Unique records only ticked. Power Query offers Home › Remove Rows › Remove Duplicates, but note that Power Query compares text case-sensitively, so it treats “Rahul Mehta” and “RAHUL MEHTA” as different, which is the opposite of the Excel command.
When this tool helps
Merged contact lists. You export customers from a CRM and leads from a web form, paste them into one sheet and need a single list. The same person often appears in both, sometimes in capitals in one system and with a stray space in the other. Comparing on the Email column alone, with case and spaces ignored, catches these.
Overlapping bank or sales exports. Downloading transactions for 1–15 March and then 10–31 March gives you six days twice. Comparing whole rows removes the overlap without touching genuine repeat purchases that differ in amount or time.
Survey and form responses. People press Submit twice. Keeping the last copy keeps the most recent answer if they corrected something on the second attempt.
Product and stock lists. A supplier sends a price list where the same SKU appears on two pages. Comparing on the SKU column with the default settings leaves one line per product.
What the tool does differently
- It ignores case and extra spaces by default. Most “missed” duplicates in Excel are caused by a capital letter or an invisible space. Both settings can be turned off under More options.
- It never treats empty rows as duplicates. Two rows with no email address are not the same customer. If you compare on Email, rows with a blank email are always kept.
- It shows you before it deletes. The Before view marks every row that will be removed, and the summary states the exact count. Nothing changes until you download.
- It can mark instead of delete. The flag option adds a column such as “Duplicate of row 4”, so you can review and decide in Excel.
- It keeps what it removed. After running, you can download the removed rows as their own file, which is a simple audit trail when you clean shared data.
- It preserves your values. Customer codes like 000123 keep their leading zeros, dates stay dates and text in any language comes through unchanged.
Choosing the right columns
Think about what identifies one record in your data. For people, an email address or customer ID is usually best; names alone are risky because two different people can share one. For transactions, whole-row comparison is safest, because two genuine payments can share a date and an amount but differ in reference number. When in doubt, use the flag option first, check the marked rows in the preview, then run the tool again in remove mode.
Once duplicates are gone, the file check on this page may suggest a next step, such as trimming spaces from names or making capitalisation consistent, so the list is fully tidy before you import it anywhere else.