How to Remove Duplicates in Excel
No time for long YouTube tutorials? Try Guidy and get it all done in 2 minutes. It sits right on your screen and points you to exactly what to do in real time. And it works on any software or browser, in any language.
Try for free
Duplicate rows can mess up your totals, break your lookups, and make any spreadsheet harder to trust. If you've got a lot of rows of data to deal with, here's how to find, highlight, and remove duplicates in Excel in less than a minute.
No time for reading a blog tutorial? Guidy is an AI for Excel that sees your screen and walks you through this in real time in less than one minute, in any language. Try now.
How to find duplicates in Excel
Before you delete anything, you need to know what's duplicated. The quickest way is Conditional Formatting. Select your data range, then go to:
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
Excel instantly colors every duplicate so you can scan the sheet and decide what to keep.
This works for a single column. If you need to find duplicates in an Excel column based on a combination of values (like name + date), select multiple columns before applying the rule.
How to highlight duplicates in Excel
The method above highlights duplicates in the default red, but you can customize it. When the Duplicate Values dialog opens, use the dropdown to pick a different format, or choose Custom Format to set your own fill color, font, or border.
To highlight duplicates in Excel across an entire table, select all your data columns first. Excel will flag any row where the full combination of selected values appears more than once.
How to remove duplicates in Excel
Once you've spotted duplicates, removing them takes three clicks. Select any cell inside your data. Go to Data → Remove Duplicates.
Excel opens a dialog where you pick which columns to check. Click OK and it deletes every duplicate row, keeping only the first occurrence. That's it. Excel tells you how many duplicates were removed and how many unique values remain.
Important: This action can't be undone after you save. If you're nervous, copy the sheet first.
How to delete duplicates in Excel but keep one
The Remove Duplicates button already does this. It keeps the first instance and deletes the rest. But if you want more control over which one to keep (say, the most recent entry), sort your data first. For example, sort by date descending so the newest entry sits on top. Then run Remove Duplicates. Excel keeps the first row it finds, which is now your most recent one.
Cool fact: Skipping tutorials is now possible! Get real-time help, right on your PC - in any language, for all software and browsers. Download Guidy now.
Formula to find duplicates in Excel
If you'd rather flag duplicates with a formula instead of conditional formatting, COUNTIF is your friend. In a helper column next to your data, enter:
This returns TRUE for every duplicate and FALSE for unique values.
You can then filter by TRUE to see all duplicates, or sort and delete them manually. This is useful when you want to find duplicate values in Excel using a formula and review them before making any changes.
Key takeaways
You can use Conditional Formatting to find and highlight duplicates, the Remove Duplicates button to delete duplicates in Excel, and COUNTIF when you want a formula-based approach. Sort first if you need to control which row stays.
