Startseite » Excel EN » How to Remove Duplicates in Excel

How to Remove Duplicates in Excel

TL;DR

To remove duplicates in Excel, select your data, go to the Data tab, and click Remove Duplicates. Use Alt + A + M as the keyboard shortcut. Excel identifies and deletes duplicate rows based on selected columns.

Why Removing Duplicates Matters in Excel

When working with large datasets, duplicates can lead to inaccurate calculations, skewed reports, and confusion. Whether you’re analyzing customer data, compiling survey results, or cleaning up exported lists, knowing how to remove duplicates in Excel ensures data integrity and simplifies your workflow.

Fortunately, Excel offers a built-in feature to help you detect and remove duplicate values without the need for formulas or external tools.

Steps to Remove Duplicates in Excel

Here’s a step-by-step guide to removing duplicate entries:

Step 1: Select Your Data

Click and drag to highlight the data range you want to clean. If your data has headers, include them in your selection.

Step 2: Go to the Data Tab

Navigate to the Data tab on the Excel ribbon.

Step 3: Click “Remove Duplicates”

In the Data Tools group, click Remove Duplicates.

Step 4: Choose Columns to Check

In the dialog box:

  • If your data includes headers, make sure the My data has headers box is checked.
  • Select the columns you want to examine for duplicates.

Click OK.

Step 5: Review the Results

Excel will show a summary message telling you how many duplicate values were removed and how many unique values remain.

Keyboard Shortcut to Remove Duplicates

  • Shortcut: Alt + A + M

Press these keys in sequence (not simultaneously). This opens the Remove Duplicates dialog box instantly.

Advanced Duplicate Removal Techniques

Remove Duplicates Across Specific Columns

You can select specific columns in the Remove Duplicates dialog to remove duplicates based only on certain fields.

For example:

  • Removing duplicates by email while keeping different names.
  • Removing entries based only on one unique identifier like a customer ID.

Use Conditional Formatting to Highlight Duplicates

Before removing, you may want to see duplicates first.

  1. Select the range
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values
  3. Choose a highlight style

This is useful if you want to review duplicates before deletion.

Use Formulas to Identify Duplicates

If you prefer a manual approach, use:

=COUNTIF(A:A, A2)>1

Apply this in a new column. Rows that return TRUE are duplicates.

What to Watch Out For

  • Case Sensitivity: Excel treats “Apple” and “apple” as the same.
  • Whitespace: Extra spaces can prevent Excel from detecting duplicates.
  • Merged Cells: These can cause issues when trying to remove duplicates.
  • Formula-driven Columns: Duplicates in formula results may not always behave as expected.

Alternatives to “Remove Duplicates” Tool

If you’re looking for a non-destructive way to remove duplicates (without deleting rows), consider:

Use Excel Filters

  1. Select your data
  2. Click Data > Filter
  3. Click the filter dropdown on your column
  4. Deselect duplicate values manually

Use Power Query

Power Query is ideal for cleaning large datasets and building reusable queries.

  1. Select your data > Data > From Table/Range
  2. In Power Query Editor, use Remove Duplicates
  3. Click Close & Load

Best Practices When Removing Duplicates

  • Always save a copy before making permanent deletions.
  • Use filters or conditional formatting first to review data.
  • Test on small datasets before applying to larger files.
  • Use Power Query for repeatable tasks.

FAQ

How do I remove duplicates in Excel without deleting rows?
Use conditional formatting to highlight duplicates or use Excel filters to hide them instead of deleting.

What is the shortcut for Remove Duplicates in Excel?
Use Alt + A + M to quickly open the Remove Duplicates dialog box.

Can Excel remove duplicates across multiple columns?
Yes. Select all relevant columns in the Remove Duplicates dialog for row-level duplicate detection.

How do I remove duplicates in Excel but keep one?
By default, Excel removes all but one instance of duplicate rows.

Does Excel’s remove duplicates feature work case-sensitively?
No. “Apple” and “apple” are treated as duplicates.

How do I undo a duplicate removal in Excel?
Press Ctrl + Z immediately after the action to undo it.

Conclusion

Knowing how to remove duplicates in Excel is essential for clean and accurate data. Whether you’re preparing reports, analyzing data, or managing lists, using Excel’s built-in Remove Duplicates tool and shortcuts like Alt + A + M saves time and reduces error.

For more advanced tasks, combine this with conditional formatting or Power Query to ensure full control over your data cleaning processes.

Complete List of Windows Keyboard Shortcuts

If you need help for Windows, you can find a whole list of all keyboard shortcuts here.

https://keyboard-shortcuts.org/

Advertisment