Duplicate rows make totals, email lists, order sheets, and ID columns harder to trust. Excel gives you a direct remover when you are ready to delete repeats, and safer review options when you want to check them first. Start with the built-in command, then use the later methods when you need a unique copy, a formula result, Power Query cleanup, or automation.
1. Run Remove Duplicates from the Data tab
The fastest desktop fix is built into Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 on Windows.
Select your cell range, or click one cell inside an Excel table.
Then choose Data > Data Tools > Remove Duplicates.
In the dialog, choose the columns Excel should compare.
Use Select All or Unselect All when needed.
Select OK, then select OK again after the results message.
Use Undo or Ctrl+Z right away if the cleaned range is not what you wanted.
2. Clean rows in Excel for the web
- 1.Open the workbook in Excel for the web.
- 2.Select the cell range you want to clean, or click inside the table.
- 3.Go to Data and select Remove Duplicates.
- 4.Unselect any columns that should not be used to decide whether rows are duplicates.
- 5.Select OK, then select OK again to dismiss the message.
3. Strip duplicates from an Excel table
This fixes duplicate rows when your data is already formatted as an Excel table.
Click anywhere inside the table.
Open Table Design.
In the Tools group, select Remove Duplicates.
Under Columns, select the columns that contain the duplicate values you want Excel to compare, or use Unselect All and Select All.
Confirm the dialog.
4. Highlight repeats before you delete anything
Use this when you want to inspect duplicate entries before removing rows.
Select one or more cells in a range, table, or PivotTable report.
Next, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Choose the formatting, select OK, and review the highlighted cells.
After review, delete unwanted duplicate rows manually or run Remove Duplicates.
Microsoft notes that duplicate-value formatting does not apply to fields in the Values area of a PivotTable report.
5. Copy unique records with Advanced Filter
This fixes the risky part of cleanup by creating a unique view or copy before you replace source data.
Select the range, or click inside the table.
Go to Data > Advanced in the Sort & Filter group.
Choose Filter the list, in-place to hide duplicates, or choose Copy to another location to create a separate unique list.
For a copied list, enter the destination cell in Copy to.
Check Unique records only, then select OK.
6. Generate a clean list with UNIQUE
Click an empty cell where the cleaned list should spill.
Enter =UNIQUE(array), replacing array with the source range.
Use =UNIQUE(range) for distinct rows or values.
Use =UNIQUE(range,,TRUE) when you only want items that occur exactly once.
Copy and paste values over the original range only when you want to replace the source data.
Use UNIQUE when you want a separate cleaned output instead of changing the original list immediately.
7. Rebuild repeat cleanup in Power Query
Power Query is the right tool when imported data needs the same duplicate cleanup more than once.
Open the query by selecting a cell in data loaded from Power Query Editor.
Then choose Query > Edit.
In Power Query Editor, select the column header.
Use Shift+Click or Ctrl+Click to select more columns.
Then choose Home > Remove Rows > Remove Duplicates.
To review matching rows first, choose Home > Keep Rows > Keep Duplicates.
Then remove or filter them as needed in the query.
8. Match duplicates between two columns
Start Excel and add a helper column beside the data.
Enter a formula such as =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1).
Select the cells to fill.
In the Editing group, select Fill, then select Down.
Use the displayed matches to filter or delete duplicate rows manually.
9. Automate duplicate removal for managed workbooks
This fixes repeat business cleanup when manual commands no longer fit the workflow.
Use a VBA macro on desktop Excel when your workbook supports macros, calling Microsoft’s Range.RemoveDuplicates method with selected columns and an optional Header setting.
Use an Office Script from the Automate tab when your Microsoft 365 environment supports Office Scripts.
Use Power Automate with an Office Script when you need a scheduled or triggered business flow, and you have a Microsoft 365 business license that supports it.
For organization controls, use Microsoft 365 Cloud Policy service settings for Let users automate their tasks in Excel, script sharing, and running scripts with Power Automate.
Do not rely on the older Microsoft 365 admin center Office Scripts toggle, because Microsoft says admins could no longer change those old settings after October 27, 2025.
Frequently Asked Questions
What columns should I select in Remove Duplicates?
Select the columns that define a duplicate for your sheet. For example, compare an email column for repeated contacts, or compare multiple columns when the whole row combination must match.
Can I remove duplicates without changing my original data?
Yes. Use Advanced Filter with Copy to another location, or use UNIQUE in an empty cell to generate a separate de-duplicated result.
Does Conditional Formatting remove duplicate rows?
No. Conditional Formatting highlights duplicate values so you can review them. You still delete unwanted rows manually or run Remove Duplicates afterward.
Can a PivotTable remove duplicates from the source data?
No. A PivotTable can collapse repeated items into a unique summarized view, but it does not delete duplicates from the original range.
