How to Remove Duplicate Rows in Excel
Use the Data tab's Remove Duplicates tool to delete repeat rows, and see why highlighting them with Conditional Formatting first is the safer move.
Excel can find and delete duplicate rows on its own: select your data, open the Data tab, and click Remove Duplicates. Before you commit to that, though, run Conditional Formatting first so you can actually see which rows are about to disappear, since the removal tool deletes silently and permanently. Below is the full process for both, plus how to decide which columns should count toward a “duplicate.”
Remove duplicates from the Data tab
This is the built-in tool, and it works on any selected range or table.
| Step | What to do |
|---|---|
| 1 | Select the range of cells with duplicates, or click any cell inside a table |
| 2 | Go to Data > Remove Duplicates (in the Data Tools group) |
| 3 | In the dialog, check or uncheck the columns that should define a duplicate |
| 4 | Click OK |
| 5 | Read the confirmation message showing how many duplicate values were removed and how many unique values remain |
Excel keeps the first occurrence of each duplicate and deletes the rest. Removal happens across every column in the row, even the ones you left unchecked in the dialog — the checked columns only decide which rows count as matches, not which cells get deleted. Because the deletion is permanent (Undo works only until you close the file or run something else), copy your original data to another sheet or workbook before you run the tool, as Microsoft’s own guidance recommends.
Choose which columns define a duplicate
The column checkboxes in the Remove Duplicates dialog are the part people get wrong. If you select every column, two rows only count as duplicates when all of them match exactly. That is fine for a clean row-level export, but it misses near-duplicates that differ in one throwaway field.
A common real-world example: a customer list where the same person appears twice with a different “Last Contacted” date. If you leave that date column checked, Excel treats both rows as unique and keeps both. Uncheck it, and Excel matches only on name and email, so it now recognizes and removes the repeat.
| If you want to match on | Do this in the dialog |
|---|---|
| The entire row (exact duplicates only) | Leave all columns checked |
| A subset, like name + email only | Uncheck every other column |
| One identifier, like an order ID | Click Unselect All, then check just that column |
Think about which fields actually identify a “same record” before you click OK — Excel will not warn you if your column choice is too broad or too narrow.
Highlight duplicates first with Conditional Formatting
Because Remove Duplicates deletes without asking which specific rows it found, it is worth previewing the matches with color first, especially on data you cannot easily recreate.
- Select the range you want to check.
- On the Home tab, click the arrow next to Conditional Formatting.
- Choose Highlight Cells Rules > Duplicate Values.
- Pick a fill color in the dialog and click OK.
Every cell that repeats within your selection now lights up, so you can scroll through and confirm the matches are actually rows you want gone before deleting anything. If you need to check duplicates across two or more columns together rather than cell by cell, use the formula-based version instead: Home > Conditional Formatting > New Rule > Format only unique or duplicate values, then set the “Format all” dropdown to duplicate and choose a format.
Once you have reviewed the highlighted rows and are confident they are true duplicates, sort by that highlight color (or the highlighted column) to group them together, then delete manually, or go ahead and run Data > Remove Duplicates with the column selection you already verified visually.
Related fixes
If your spreadsheets keep breaking in other ways, see how to wrap text in Excel so long entries stop hiding data next to them, how to lock cells in Excel to stop a cleaned-up sheet from getting edited again, or how to use VLOOKUP in Excel to cross-check values instead of just matching exact duplicates.
Remove Duplicates is fast and fine for a quick, well-understood list. For anything you would regret losing, spend the extra thirty seconds on Conditional Formatting first — it costs nothing and turns a blind delete into a checked one.