The fastest way to spot duplicates in Excel

Excel has a built-in tool called Conditional Formatting that highlights duplicate values automatically. Open your spreadsheet, select the column or range of cells you want to check, then go to the Home tab, click Conditional Formatting, choose Highlight Cell Rules, and select Duplicate Values. Excel will when ready color every duplicate entry so you can see them at a glance.

If you need to remove duplicates rather than just see them, Excel also has a dedicated Remove Duplicates button on the Data tab. Select your data, click Data > Remove Duplicates, choose which columns to check, and Excel deletes the extra copies in seconds. This is the simplest path when you have a straightforward list with no complex structure.

For more control — when you need to keep some duplicates, compare two separate lists, or understand which entries appear more than once — the other methods below give you more options.

Key Takeaways

  • Conditional Formatting highlights duplicates in place so you can review them before taking action.
  • The Remove Duplicates button on the Data tab deletes extra copies in one click, but you cannot undo it after saving.
  • A COUNTIF formula lets you mark duplicates without deleting them, giving you time to decide what to keep.
  • Sorting your data first makes duplicates easier to spot manually and reduces the chance of missing entries that are slightly different.
  • When comparing two separate lists, a VLOOKUP or INDEX/MATCH formula shows which entries exist in one list but not the other.

Using Conditional Formatting to highlight duplicates

Conditional Formatting is the safest first step because it shows you the problem without changing your data. Select the cells you want to check — click the column header to select an entire column, or click and drag to select just a range. Then go to the Home tab at the top, find the Conditional Formatting button (usually on the right side of the ribbon), click it, hover over Highlight Cell Rules, and click Duplicate Values.

A dialog box appears asking what color you want duplicates to be. The default is light red with dark red text, which works fine. Click OK, and every duplicate value in your selection turns that color. If you have 500 rows and the name "John Smith" appears three times, all three instances highlight. This lets you review what Excel found before you delete anything.

To remove the highlighting later, select the same range again, go back to Conditional Formatting, click Manage Rules, select the rule you just created, and click Delete Rule. The highlighting disappears but your data stays exactly as it was.

Removing duplicates with the Data tab button

If you are certain you want to delete duplicates and keep only one copy of each entry, the Remove Duplicates button is the fastest route. Select your entire data range — including headers if you have them — then go to the Data tab and click Remove Duplicates. A dialog box lists every column in your selection and asks which ones to check.

By default, all columns are checked. This means Excel looks at the entire row and only considers it a duplicate if every single column matches. If you want to check only one column — for example, to find duplicate email addresses even if the names are spelled differently — uncheck all columns except the one you care about. Then click OK.

Excel deletes the extra copies and tells you how many rows were removed. This action cannot be undone after you save the file, so save a backup copy first if you are working with important data. If you realize you made a mistake, you can close without saving and start over.

Using COUNTIF to mark duplicates without deleting them

A COUNTIF formula gives you more control by marking duplicates in a helper column instead of deleting them. This way you can review each one and decide what to keep. In a blank column next to your data — say column B if your names are in column A — click the first cell and type =COUNTIF($A$1:$A$100,A1). Replace A1:A100 with the actual range of your data.

Press Enter. The formula returns a number: 1 if that entry appears once, 2 if it appears twice, 3 if it appears three times, and so on. Copy this formula down to every row in your data. Now every cell in column B shows how many times that entry repeats. Any number higher than 1 is a duplicate.

You can then sort by column B to group all the duplicates together, review them, and manually delete the ones you do not need. This takes longer than Remove Duplicates, but you keep full control and can see exactly what is being removed before it happens.

Sorting to spot duplicates manually

Before using any automated tool, sorting your data can make duplicates obvious. Select your entire data range including headers, go to the Data tab, and click Sort. Choose the column you want to sort by — usually the column most likely to have duplicates — and click OK. Excel arranges your data alphabetically or numerically, which puts identical entries right next to each other.

Now scroll through and look for entries that repeat. This is slower than Conditional Formatting for large datasets, but it works well for lists under 500 rows and catches duplicates that might be slightly different — like "John Smith" versus "john smith" or "Smith, John" — which automated tools might miss depending on how they are set up.

After you finish reviewing, you can sort back to your original order if you saved the original sort column, or you can leave it sorted if the new order works for you.

Comparing two lists to find entries that appear in only one

Sometimes you need to know which entries exist in one list but not another — for example, which customer names are in your current database but not in a new import. Put one list in column A and the other in column B, then use a VLOOKUP formula in column C to check if each entry from column A exists in column B.

In cell C1, type =IFERROR(VLOOKUP(A1,$B$1:$B$100,1,FALSE),"Not Found"). Replace B1:B100 with the actual range of your second list. Press Enter. If the entry from A1 exists anywhere in column B, the formula shows that entry. If it does not exist, it shows "Not Found". Copy this formula down for every row in column A.

Now you can see at a glance which entries are missing from the second list. You can sort by column C to group all the "Not Found" entries together, or use Conditional Formatting to highlight them. This method works even when the lists are in different sheets or different files — just adjust the range reference to point to the other sheet.

Handling duplicates that look different but are the same

Sometimes duplicates hide because of extra spaces, different capitalization, or slight spelling variations. "John Smith", " John Smith" (with a leading space), and "john smith" look like three different entries to Excel, even though they are the same person. Before you search for duplicates, clean your data first.

Use the TRIM function to remove extra spaces: in a helper column, type =TRIM(A1) and copy it down. This removes leading and trailing spaces. For capitalization, use =UPPER(A1) to convert everything to capitals, which makes "john smith" and "John Smith" match. Copy the cleaned version back over your original data, or work from the cleaned column instead.

For spelling variations — like "Jon" versus "John" — there is no automatic fix. You have to review them manually or use Find and Replace to standardize common misspellings. Go to Home > Find & Replace, type the wrong spelling, type the correct one, and click Replace All.

Frequently Asked Questions

Can I undo Remove Duplicates after I save the file?

No. Once you save after using Remove Duplicates, the deleted rows are gone permanently. Always save a backup copy of your original file before using Remove Duplicates. If you realize you made a mistake, close the file without saving and reopen the backup.

Does Conditional Formatting find duplicates across multiple columns?

Conditional Formatting checks each column independently by default. If you select columns A and B together, it highlights duplicates within column A and separately within column B, not rows where both columns match. For that, use a formula instead.

What if my duplicates have different capitalization or extra spaces?

Clean your data first using TRIM to remove spaces and UPPER to standardize capitalization. Then run your duplicate check. This prevents Excel from treating "John Smith" and "john smith" as different entries.

How do I find duplicates if my data is in multiple sheets?

Use a VLOOKUP or COUNTIF formula that references the other sheet. Type the sheet name followed by an exclamation point before the range, like =COUNTIF(Sheet2!$A$1:$A$100,A1). This checks whether each entry in Sheet1 appears anywhere in Sheet2.

Can I highlight duplicates in only one column while ignoring the others?

Yes. Select only that column before opening Conditional Formatting. If you select the entire table, Conditional Formatting checks all columns. To check only column A, click the column A header, then explore Conditional Formatting.