How to Remove Duplicates in Excel (Step-by-Step Guide)

Remove Duplicates from Excel: Automate with VBA and Power Query

Overview

Automating duplicate removal saves time and reduces errors for repeated data-cleaning tasks. Two strong automation approaches in Excel are VBA (Visual Basic for Applications) for customizable macros and Power Query for repeatable, no-code transformations. Use VBA when you need workbook-specific automation, custom logic, or integration with other Excel features. Use Power Query when you prefer a maintainable, auditable, and user-friendly pipeline that easily refreshes on updated data.

When to use each

• VBA
  • Needs: custom workflows, integration with forms/buttons, or complex logic not covered by Power Query.
  • Pros: highly flexible, can manipulate workbook UI and other objects.
  • Cons: macro-enabled files (.xlsm) required; security warnings; harder to maintain for non-developers.
• Power Query
  • Needs: repeatable, transparent data transforms from tables, CSVs, or external sources.
  • Pros: no code needed, steps are recorded and editable, easy refresh, works across Excel desktop and newer Excel for the web versions (where available).
  • Cons: less granular control of UI elements; some advanced logic may require M-language.

Power Query method (quick steps)

1. Convert data range to a table: select range → Insert → Table.
2. Data → Get & Transform → From Table/Range opens Power Query Editor.
3. In Power Query Editor, select the column(s) to deduplicate.
4. Home → Remove Rows → Remove Duplicates.
5. Close & Load to return the cleaned table to Excel.
6. To repeat, update source and click Refresh (or set automatic refresh).

Common Power Query tips

• Select multiple columns to consider a full-row duplicate key.
• Use Remove Duplicates after sorting if you want to keep the first/last occurrence deterministically.
• Use Group By or Table.Distinct in M for advanced selection of which row to keep.
• Keep the original raw query source as a separate query if you need to preserve unmodified data.

VBA method (example macro)

Use a macro to remove duplicates from a specific table or range:

vbnet
Copy CodeCopied
Sub RemoveDuplicatesInRange()
Dim ws As Worksheet     Dim rng As Range     Set ws = ThisWorkbook.Worksheets(“Sheet1”)
    Set rng = ws.Range(“A1”).CurrentRegion ‘adjust as needed
    rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes ‘adjust columns
End Sub

• Change Columns:=Array(1,2) to the column indices used for duplicate comparison.
• Use Header:=xlYes if the first row is a header.
• Run via Developer → Macros or assign to a button.

VBA tips

• Backup data before running destructive macros; consider copying results to a new sheet.
• Add error handling and user prompts for safer operation.
• Combine with Workbook events (e.g., Worksheet_Change) to trigger deduplication automatically on updates.
• Digitally sign macros or instruct users to enable macros if sharing.

Choosing and implementing

• For most repeatable, source-driven cleaning, prefer Power Query for transparency and ease.
• For UI automation, complex branching, or legacy workbooks, use VBA.
• Consider hybrid: use Power Query for core deduplication and VBA only to trigger refreshes or manage workbook layout.

If you want, I can:

• Provide a ready-to-use VBA script tailored to your sheet layout.
• Give step-by-step Power Query M code to keep the last occurrence or prefer rows with non-empty values. Which would you like?