Does Remove Duplicates delete rows permanently?

Yes. Remove Duplicates removes duplicate rows from the selected range. Copy your data first if you need a backup.

What is the safest way to remove duplicates in Excel?

Use UNIQUE when you want a clean duplicate-free output without changing the original data.

Sort the data so the latest record appears first, then use Remove Duplicates on the key columns.

Yes. Power Query can remove duplicates as a repeatable transformation, which is useful for recurring imports.

Tips & Tricks - 2026-05-12

Remove duplicate rows in Excel safely using Remove Duplicates, UNIQUE, COUNTIF, conditional formatting, and backup checks.

Removing duplicates sounds simple, but it can permanently delete rows if you choose the wrong columns. Before using the Remove Duplicates command, decide what makes a record duplicate. Examples: Same email address Same invoice number Same customer ID Same product and date combination Always save a backup or copy the data to a new sheet first.

Select the data range. Go to Data -> Remove Duplicates. Check the columns that define a duplicate. Click OK. If your data has headers, make sure My data has headers is checked. Use this method when you want to permanently remove duplicate rows.

Use this when you want to review duplicates before deleting anything. Select the column. Go to Home -> Conditional Formatting. Choose Highlight Cells Rules -> Duplicate Values. Excel highlights duplicate values so you can inspect them manually.

In Microsoft 365, UNIQUE creates a clean list without changing the original data. \\\ =UNIQUE(A2:A100) \\\ For full rows: \\\ =UNIQUE(A2:D100) \\\ This is safer because the original data remains unchanged.

COUNTIF can mark duplicates with a helper column. \\\ =COUNTIF($A$2:A2,A2)>1 \\\ This returns TRUE for repeated values after the first occurrence.

Use Remove Duplicates when you are confident about the duplicate definition. Use UNIQUE when you want a clean output without touching the source. Use COUNTIF or conditional formatting when you want to audit first.

UNIQUE function COUNTIF function COUNTIF Not Counting Text? Excel Formula Audit Checklist

Goal Best method Review duplicates before deleting Conditional Formatting Flag duplicates with formulas COUNTIF helper column Delete exact duplicate rows Data -> Remove Duplicates Keep latest record only Sort first, then remove duplicates Repeat cleanup every month Power Query

Before deleting duplicates, define what "duplicate" means. Two rows may share the same customer name but have different invoice numbers. In that case, the customer name alone is not enough. Safe workflow: Copy the original data. Identify key columns. Sort by the record you want to keep. Remove duplicates using only the key columns. Reconcile counts before and after.

Use Find Duplicate Values in Excel for review workflows, COUNTIF for flags, and Power Query for repeatable cleanup.

Method Updates automatically Best for Remove Duplicates No One-time cleanup UNIQUE formula Yes (M365) Live distinct lists Power Query On refresh Monthly imports

Does Remove Duplicates delete rows permanently? Yes. Remove Duplicates removes duplicate rows from the selected range. Copy your data first if you need a backup.
What is the safest way to remove duplicates in Excel? Use UNIQUE when you want a clean duplicate-free output without changing the original data.
How do I remove duplicates but keep the latest row? Sort the data so the latest record appears first, then use Remove Duplicates on the key columns.
Can Power Query remove duplicates? Yes. Power Query can remove duplicates as a repeatable transformation, which is useful for recurring imports.