3 Ways to Remove Duplicates in Excel: Remove Duplicates, UNIQUE, and Power Query

Remove duplicates in Excel

3 Ways to Remove Duplicates in Excel: Remove Duplicates, UNIQUE, and Power Query

Removing duplicates in Excel comes down to three methods: the fastest option, Remove Duplicates; the UNIQUE function, which preserves the source data; and Power Query, which is ideal for large datasets and reusable workflows. Follow the steps below to clean up your work data right away.

Quick Fix: Remove Duplicates in 20 Seconds

  1. Select the range (or select the entire table: create a table with Ctrl+T, then click any cell).
  2. Select Data > Remove Duplicates.
  3. Select the column(s) to use as duplicate criteria and click OK.
  4. Review the results summary, including the number removed and remaining unique values.

Method 1. Remove Duplicates (Deletes Data) — Including Multiple Columns

  • The fastest method. Because it deletes actual data, consider making a backup or using Undo before running it.
  • When you select multiple columns, only rows with the same combination of columns are considered duplicates.
  • To highlight duplicates instead of deleting them: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Method 2. UNIQUE Function (Non-Destructive) — Unique Values or Values That Appear Exactly Once

=UNIQUE(range, [by_col], [exactly_once])
  • Unique values in one column: =UNIQUE(B2:B1000)
  • Unique rows across multiple columns: =UNIQUE(A2:D1000) (compares by row)
  • Values that appear exactly once: =UNIQUE(A2:A1000,,TRUE)
  • Preserves the source data and expands automatically as a dynamic array.

Method 3. Power Query (Large Datasets and Reuse) — A Repeatable Workflow

  1. Use Data > Get & Transform Data to load the range as a query.
  2. In the Power Query Editor, select the key column(s), then choose Home > Remove Rows > Remove Duplicates.
  3. Select Close & Load to output the results as a table. You can Refresh it with new data.

If you need to preserve the order, add a sort step before removing duplicates.

Practical Example & Sample Data

Customer IDOrder DateItemQuantity
C0012025-01-03T-Shirt2
C0012025-01-03T-Shirt2
C0022025-01-04Pants1
C0032025-01-04T-Shirt3
C0022025-01-04Pants1

Unique Customers in One Column

=UNIQUE(A2:A6)

Values That Appear Exactly Once (exactly_once)

=UNIQUE(A2:A6,,TRUE)

Remove Complete Duplicates Based on Customer ID + Order Date

  • Remove Duplicates: Select <Customer ID> and <Order Date>, then run the command.
  • UNIQUE: =UNIQUE(A2:D6)
  • Power Query: Select the two columns → Remove Duplicates.

Alternative Methods & Notes

  • Advanced Filter → Unique records only: Copies results to another location as a static result. Run it again when data needs updating.
  • The AutoFilter drop-down displays only up to 10,000 unique items. Use another method for a large number of unique values.
  • Power Query does not preserve sort order when removing duplicates. Add a sort step before removing duplicates.

Troubleshooting

IssueCauseSolution
Some duplicates remainIncorrect key columns selectedReset the criteria using a combination of multiple columns, or use UNIQUE with multiple columns.
The unique list does not updateStatic copy created with Advanced FilterUse UNIQUE or refresh Power Query.
exactly_once produces different resultsThe third argument is not specifiedUse =UNIQUE(range,,TRUE).
Results are in an unexpected orderNo sort order specified in Power QueryAdd a sort step before removing duplicates.

Related Articles

1 thought on “3 Ways to Remove Duplicates in Excel: Remove Duplicates, UNIQUE, and Power Query”

  1. Pingback: Keep Only the Latest Record for Each ID in Excel (Without Deleting History)

Leave a Reply

Your email address will not be published. Required fields are marked *