How to spot duplicates in a single column
The fastest way to find duplicates in Excel is to use conditional formatting, which highlights cells that repeat. Open your spreadsheet, select the column where you want to find duplicates, then go to the Home tab and click Conditional Formatting. Choose Highlight Cell Rules, then Duplicate Values. Excel will when ready color any cell that appears more than once in that column.
This works best when your data is in a single column — names, email addresses, ID numbers, or product codes. If you have 500 rows of customer names and three of them say "John Smith", all three will turn red or yellow so you can see them at a glance. The color makes it straightforward to scroll through and decide which copies to keep or delete.
If you want to remove the highlighting later, go back to Conditional Formatting and click Clear Rules. The duplicates stay in the spreadsheet; the highlighting just disappears.
Key Takeaways
- Conditional formatting highlights duplicate values in a column with color so you can see them without scrolling through every row.
- The Remove Duplicates feature deletes duplicate rows entirely, keeping only the first occurrence of each entry.
- If your data spans multiple columns, you can tell Excel to check only certain columns for duplicates, ignoring others.
- Sorting your data alphabetically before looking for duplicates makes it easier to spot them manually if you prefer not to use built-in tools.
Using Remove Duplicates to delete copies automatically
If you want Excel to delete the duplicate rows instead of just highlighting them, use the Remove Duplicates feature. First, select all the data you want to check — including headers if you have them. Go to the Data tab and click Remove Duplicates. A dialog box will appear asking which columns to check.
By default, Excel selects all columns. This means it will only mark a row as a duplicate if every single cell matches another row exactly. If you want to check only certain columns — for example, only the email column — uncheck the columns you want to ignore. Then click OK. Excel will delete every row that is a duplicate based on the columns you selected, keeping the first occurrence of each entry.
This is permanent, so make a copy of your spreadsheet first if you are not sure. Once you click OK, the duplicates are gone and you cannot undo it by closing the file without saving.
Finding duplicates across multiple columns
When your data has many columns — like a customer list with name, email, phone, and address — you need to decide whether duplicates mean the same person appears twice or just that two people share a last name. Excel's Remove Duplicates feature lets you choose which columns matter.
Select all your data, go to Data > Remove Duplicates, and uncheck any columns you want to ignore. For example, if you only care about duplicate email addresses, uncheck Name, Phone, and Address. Now Excel will only flag rows where the email is identical, even if the names are different. This is useful when you have imported data from two different sources and want to find records that refer to the same person.
If you just want to see the duplicates without deleting them, use conditional formatting instead and select only the columns that matter. Highlight the email column, explore conditional formatting, and you will see which emails repeat without losing any data.
Sorting to spot duplicates by eye
Sometimes the simplest approach is to sort your data alphabetically and look for duplicates yourself. Select your data, go to the Data tab, and click Sort. Choose the column you want to sort by — usually the one where duplicates are most likely — and click OK. Excel will rearrange all rows so that identical entries sit next to each other.
Once sorted, duplicates are straightforward to spot because they appear in consecutive rows. This method takes longer than conditional formatting if you have thousands of rows, but it gives you full control. You can see the context around each duplicate and decide which one to keep based on other information in that row.
After you have identified the duplicates, you can delete the rows you do not need by right-clicking and selecting Delete Row. This is slower than Remove Duplicates, but safer if you want to review each one before it disappears.
Handling duplicates when you have headers
If your first row contains column names — like "Name", "Email", "Phone" — tell Excel not to treat it as data. When you use Remove Duplicates or conditional formatting, look for a checkbox that says "My data has headers" and make sure it is checked. This tells Excel to ignore the header row and only check the actual data below it.
If you forget to check this box, Excel might treat your header row as a duplicate of another row that has the same values, which can cause confusion. Always check the box before you start if your spreadsheet has headers.
Using a formula to find duplicates in a helper column
If you want to mark duplicates without deleting them or using conditional formatting, you can add a formula in a new column. Click on a blank column next to your data and type this formula in the first data row: =COUNTIF($A$2:$A$1000,A2)>1. Replace A2 and A1000 with the actual range of your data, and replace the second A2 with whichever column you want to check.
This formula counts how many times each value appears in that column. If it appears more than once, the formula returns TRUE. If it appears only once, it returns FALSE. Copy the formula down to every row, and you will see TRUE next to every duplicate. You can then sort by this column to group all the duplicates together, or filter to show only the TRUE values.
This method is useful when you want to keep the original data untouched and just mark which rows are duplicates. You can delete the helper column later once you have decided what to do with the duplicates.
Frequently Asked Questions
What if I only want to find duplicates in part of my spreadsheet?
Select only the rows and columns you want to check before you use conditional formatting or Remove Duplicates. Excel will only look at the cells you have selected, ignoring the rest of the spreadsheet. This is useful if you have multiple data sets in one file and only want to clean up one of them.
Can I undo Remove Duplicates if I delete something by mistake?
Not if you have already saved the file. Remove Duplicates is permanent once you click OK. Always save a copy of your spreadsheet before using Remove Duplicates, so you can go back to the original if something goes wrong.
How do I find duplicates if the entries are slightly different, like "John Smith" and "john smith"?
Excel treats uppercase and lowercase as different, so "John" and "john" will not be flagged as duplicates. You can use Find and Replace to standardize the text first — for example, converting everything to lowercase — before you look for duplicates. Go to Home > Find and Replace, use regular expressions if needed, and clean up the formatting before running your duplicate check.
What if two rows are almost identical but not exactly the same?
Excel's built-in tools only find exact matches. If you need to find rows that are similar but not identical — like "123 Main Street" and "123 Main St" — you will need to clean up the data manually or use a more advanced tool. Start by standardizing abbreviations and spacing so that matching entries are exactly the same, then use Remove Duplicates.