Excel Tutorials
Find and delete duplicate rows safely, preview what would be removed first, and control exactly which columns count toward a match. This lesson uses the Basic Budget workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Remove Duplicates scans a range top to bottom and permanently deletes any row whose values, in the columns you choose, exactly match a row that appeared earlier. Only the first occurrence of each unique combination survives.
It's a genuinely destructive tool, rows are actually deleted, not just hidden, which is why previewing duplicates before running it is worth doing on anything you can't easily rebuild.
Step-by-step, matching the video above.
Include the header row. An incomplete selection can misalign which columns Excel actually compares.
Click Data on the ribbon.
Found in the Data Tools group. A dialog opens listing every column in your selection as a checkbox.
Leave every column checked to require an exact match across the whole row, or uncheck columns that shouldn't factor into what counts as a duplicate.
Excel deletes matching rows and shows a summary: how many duplicate values were removed and how many unique values remain.
Ctrl+Z reverses it right away, but only until you save or close the file, after that the deleted rows are gone.
| Row | A: Order ID | B: Item | Result |
|---|---|---|---|
| 2 | 1001 | Chairs | Kept (first) |
| 3 | 1002 | Tables | Kept (unique) |
| 4 | 1001 | Chairs | Deleted (duplicate) |
| 5 | 1001 | Linens | Kept if only Order ID is checked; deleted if both columns are checked |
Row 4 is deleted no matter what, since it exactly matches row 2 in both columns. Row 5 is the case that shows why column selection matters: it shares an Order ID with row 2 but has a different Item, so it only counts as a duplicate if you checked Order ID alone, not if you required both columns to match.
Since Remove Duplicates is permanent, previewing first is worth the extra minute on any data you can't easily rebuild.
Select your range, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Every duplicate gets colored without anything being deleted.
Scroll through and confirm the highlighted rows are genuinely duplicates you want gone, not legitimately similar records that happen to share one column's value.
Once you've confirmed what would be affected, go to Data > Remove Duplicates and proceed knowing exactly what's about to happen.
When you'd rather flag duplicates for manual review than delete anything automatically.
=COUNTIF(A:A,A2)>1 in a helper column returns TRUE for any row whose value in column A appears more than once anywhere in that column.
Nothing gets deleted, you keep every row and simply filter or sort by the helper column to review flagged duplicates on your own terms.
A partial selection can leave some columns out of the comparison entirely, or worse, misalign rows if the selection doesn't match your actual data boundaries.
Requiring a match on just one column, like Name, can delete legitimately different records that happen to share that one value, like two different customers with the same first name.
Ctrl+Z only works immediately, before the file is saved or closed. Preview with Conditional Formatting first if the data matters and you're not certain.
Conditional Formatting's Duplicate Values rule only colors cells, it doesn't delete anything. Remove Duplicates is the separate tool that actually deletes rows.
A record shouldn't need a cleanup pass to stay accurate.
Free 14-day trial. No credit card required.
Select your data range including headers, go to the Data tab, click Remove Duplicates, choose which columns to check, and click OK.
The whole row. If a duplicate is found based on the columns you selected, Excel deletes that entire row, not just the matching cell.
The first occurrence. Excel scans top to bottom and keeps whichever row it encounters first, deleting any later rows that match.
Yes, the Remove Duplicates dialog lists every column as a checkbox, uncheck any column you don't want counted toward what makes a row a duplicate.
Yes, but only immediately with Ctrl+Z. If you save or close the file first, the deleted rows are gone for good.
Use Conditional Formatting > Highlight Cells Rules > Duplicate Values first, it highlights every duplicate without deleting anything, so you can review what would be affected.
Remove Duplicates permanently deletes rows. Conditional Formatting just highlights duplicate values visually, leaving every row intact for you to review or handle manually.
Yes, click any cell in the Table, then Table Design > Remove Duplicates, it works the same way and the Table automatically adjusts to the new row count.
Every business starts with a spreadsheet. Updoot is where you scale past it, no duplicate cleanup required.
Start Your Free Trial →