Remove duplicates in Excel with one click

Duplicates in an Excel table waste time, skew analyses, and clutter your reports. Fortunately, Excel offers very quick ways to get rid of them — from the single « Remove Duplicates » button to more robust methods using Power Query or formulas. This article guides you step by step: when to click safely, how to prepare your data, which alternatives to use if your duplicates are « almost » identical, and what precautions to take to avoid deleting useful information.

In brief

Why duplicates are a problem

When a dataset contains duplicates, your totals can be inflated, your pivot tables distorted, and your analyses lose reliability. We first think of exact duplicates — two identical rows — but there are also « near-duplicates »: same values with extra spaces, different case, or distinct formatting (text vs number). Any effective strategy starts by understanding the nature of the duplicates present in your file.

The fastest method: the Remove Duplicates tool

Excel includes a simple and direct tool: Data → Remove Duplicates. In two clicks, Excel compares the chosen columns and deletes redundant rows. This method is perfect for clean datasets where duplicates are strictly identical. Here’s how to proceed safely.

Detailed steps (step by step)

• Select the range or click inside the table (Ctrl+A to select all if needed).
• In the Data tab, click on Remove Duplicates. A window opens.
• If your first row contains headers, check My data has headers.
• Check the columns to compare: all columns for an exact duplicate, or a single column to detect duplicates on a specific criterion (e.g., email address).
• Confirm: Excel will remove duplicates and display a message indicating how many rows were deleted.

Practical note: Undo (Ctrl+Z) immediately restores the rows if you make the mistake right afterwards. If you saved after the deletion, recovery becomes more complicated — think about automatic backups or preventive copies.

Concrete examples

Imagine a customer table with columns: ID, Name, Email, City. You want to delete rows where the email is identical (email duplicates). In the dialog box, uncheck everything and check only Email. Excel will keep the first occurrence and delete the following ones.

ID	Name	Email	City
1	Dupont	[email protected]	Paris
2	Martin	[email protected]	Lyon
3	Dupont	[email protected]	Lille

After removing duplicates on the Email column:

ID	Name	Email	City
1	Dupont	[email protected]	Paris
2	Martin	[email protected]	Lyon

When the one-click tool is not enough

You might think the button deletes everything cleanly, but it fails with near-duplicates: “Jean Dupont” vs “jean dupont”, addresses with trailing spaces, or values imported as text from a CSV file. In these cases, preparation is essential: clean spaces, standardize case, convert formats.

Clean before deduplicating

• Use =TRIM(A2) to remove unnecessary spaces.
• =LOWER(A2) or =UPPER(A2) to normalize case before comparison.
• Convert numbers stored as text to numeric format (Convert tool).
• Create a concatenated key column: =TRIM(LOWER(A2 & “|” & B2)) to combine multiple fields and compare on this key.

Power Query: the solution for repeated and reversible cleanups

Power Query transforms Excel into a true lightweight ETL. You import your data, apply steps (cleaning, transformation, deduplication) and the result refreshes with each update. The advantage is twofold: the logic is documented in the editor and you do not touch the source data.

Quick procedure with Power Query

1. Data → Get & Transform → From sheet or from a file.
2. In the Power Query editor, select the comparison columns.
3. Right-click → Remove Duplicates.
4. Apply additional transformations (Trim, Lowercase) before deduplication if necessary.
5. Close & Load to send the cleaned set back to Excel.

Power Query keeps the history of steps: you can go back, modify a transformation and reapply. This is the preferred method for recurring processes or automated imports.

Alternatives: formulas and highlighting

Before deleting, it is often better to visually identify duplicates. Conditional formatting helps you: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. You will immediately see the occurrences and can decide manually.

Useful formulas

Goal	Formula (examples)
Count duplicates in a column	=COUNTIF($B:$B,B2)
Identify the first occurrence	=IF(COUNTIF($B$2:B2,B2)=1,”Unique”,”Duplicate”)
Create a multi-column key	=TRIM(LOWER(A2&”|”&B2&”|”&C2))
Extract unique values (Excel 365)	=UNIQUE(B2:B100)

Best practices before deleting

• Make a copy of the sheet or file. Saving takes less time than recovering from errors.
• Sort by key column to group duplicates and spot anomalies.
• Create a status column (e.g. “To verify”) and only delete rows marked automatically after review.
• Export a version of the deleted rows (cut-paste) into a new sheet for audit.
• Document the method used (tool, chosen columns), especially if other colleagues need to reproduce the operation.

Complex cases and traps to avoid

Hidden duplicates are the main source of errors: cells with carriage returns, non-printable characters, different encodings after import, or identifiers that change depending on the source. Some common traps:

• Non-breaking spaces — TRIM is not always enough; use CLEAN and replace special characters.
• Automatic deletion keeps the first occurrence without business logic: you might delete the most up-to-date row. Prefer sorting by date before deletion if you want to keep the latest version.
• Partial duplicates in CRM systems often require business rules (merging contacts, keeping history).

FAQ

How to recover deleted rows if I saved the file?

If you saved after deletion, the safest option is to open a backup (copy) if you have one. Otherwise, depending on your setup (OneDrive, SharePoint), you can restore a previous version of the file via version history. The tip: enable automatic backups or work on a copy to avoid this situation.

How to handle duplicates that are not strictly identical?

For “close” duplicates, start by normalizing the data (TRIM, LOWER, replace accents if needed). Then, use Power Query to apply similarity rules or create a combined key (concatenation). For advanced fuzzy matching, external tools or scripts (Python, fuzzy matching) may be necessary.

Does the button delete all columns?

No: Excel deletes the entire row when the selected columns correspond to an already existing occurrence. If you choose only one column as a criterion, other columns will be deleted accordingly for redundant rows. That is why you need to precisely determine the column(s) for comparison.

Can I automate duplicate deletion every day?

Yes, via Power Query (automatic refresh) or VBA macros that apply the desired logic and keep a copy of deleted data. Power Query is generally preferable for its traceability and ease of maintenance.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How to recover deleted rows if I saved the file?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Open a backup if available or restore a previous version via OneDrive/SharePoint; automatic backups prevent losses.”
}
},
{
“@type”: “Question”,
“name”: “How to handle duplicates that are not strictly identical?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Normalize data (TRIM, LOWER), create a combined key, and use Power Query or fuzzy matching tools for complex cases.”
}
},
{
“@type”: “Question”,
“name”: “Can I automate duplicate deletion every day?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Yes: Power Query allows automated refreshes; VBA macros offer another option for customized processes.”
}
}
]
}

{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “Removing Duplicates in Excel”
},
“keywords”: [“duplicates”, “Excel”, “Remove duplicates”, “Power Query”, “CSV file”]
}