The fastest way to spot duplicates in Excel

Excel has a built-in tool called Conditional Formatting that highlights duplicate values in seconds. Select the column or range of cells you want to check, go to the Home tab, click Conditional Formatting, choose Highlight Cell Rules, then select Duplicate Values. Excel will shade every duplicate entry in that range with a color — usually light red — so you can see them when ready.

This method works best when you want to see duplicates without removing them, or when you need to review them before deciding what to do. The highlighting stays in place until you remove it, so you can work with the data and come back to it later.

If you have a smaller list and want to scan manually instead, you can also sort your data alphabetically. Duplicates will sit next to each other, making them obvious to spot by eye. This takes longer but gives you full control over what you delete.

Key Takeaways

  • Conditional Formatting highlights duplicates with color in seconds and works on any size list.
  • The Remove Duplicates feature deletes duplicate rows permanently, keeping only the first occurrence of each value.
  • Sorting your data alphabetically groups duplicates together so you can review them before removing anything.
  • Always copy your original data to a backup sheet before using Remove Duplicates, since the deletion cannot be undone.
  • If you need to find duplicates across two separate columns or sheets, a COUNTIF formula can count how many times each value appears.

Using Remove Duplicates when you want to clean the data

If you want Excel to delete duplicate rows entirely, use the Remove Duplicates feature. Select all the data you want to clean — including headers if your data has them — then go to the Data tab and click Remove Duplicates. Excel will show you a dialog asking which columns to check. By default it selects all columns, which means a row is only removed if every single cell matches another row exactly.

Excel keeps the first occurrence of each duplicate and removes the rest. So if you have the name "Sarah Chen" in rows 3 and 7, row 7 will be deleted and row 3 will stay. This is permanent — you cannot undo it after you close the file — so always save a copy of your original data first.

If your data has a header row (like "Name" or "Email" at the top), check the box that says "My data has headers" before you click Remove Duplicates. This tells Excel not to treat the header as data and accidentally delete it.

Finding duplicates across two columns or sheets

When you need to check whether values in one column appear anywhere in another column — or in a different sheet entirely — Conditional Formatting alone is not enough. Instead, use a COUNTIF formula. In a blank column next to your data, type =COUNTIF($A$2:$A$100,A2) (replacing A2:A100 with your actual data range). This formula counts how many times each value appears in that range.

If the result is 1, the value appears only once. If it is 2 or higher, you have a duplicate. You can then sort by this formula column to group all the duplicates together, or use it with Conditional Formatting to highlight only the rows where the count is greater than 1.

To compare two separate columns, use =COUNTIF($B$2:$B$100,A2) instead. This counts how many times each value in column A appears anywhere in column B. A result of 0 means it does not appear in column B at all; any number above 0 means it does.

Sorting to review duplicates before deleting

Sorting is the safest way to handle duplicates if you are not sure which ones to keep. Select all your data including headers, go to the Data tab, and click Sort. Choose the column you want to sort by — usually the column where duplicates are most likely — and click OK. Excel will rearrange your entire dataset so that identical values sit next to each other.

Now you can scroll through and see exactly which rows are duplicates. You might find that some "duplicates" are actually slightly different — a name spelled two ways, or an email with a typo — and you will want to keep both. Sorting lets you make these decisions one by one instead of letting Excel delete them automatically.

Once you have reviewed everything, you can delete the duplicate rows manually by selecting them, right-clicking, and choosing Delete Row. This takes longer than Remove Duplicates, but you have complete control over what stays and what goes.

When duplicates are actually useful data

Sometimes what looks like a duplicate is actually important information. If you are tracking sales orders, for example, the same customer name appearing twice is not a duplicate — it is two separate orders. In this case, you would not want to remove duplicates at all. Instead, you might want to count how many times each customer appears using a pivot table or a COUNTIF formula.

Before you use any duplicate-removal tool, ask yourself whether each row represents a unique transaction or event. If it does, duplicates are probably errors and should be removed. If each row is a separate record that happens to share some values with other rows, you should not remove them.

Preventing duplicates in the first place

If you are building a new spreadsheet and want to avoid duplicates from the start, use Data Validation. Select the column where you want to prevent duplicates, go to the Data tab, click Data Validation, and set it to reject entries that already exist in that column. This does not stop someone from typing a duplicate by accident, but it will warn them and ask them to enter something different.

You can also use a formula-based approach: add a helper column with a COUNTIF formula that flags any new entry that matches an existing one. This gives you a visual warning without blocking the entry, so you can decide whether it is truly a duplicate or a legitimate repeat.

Frequently Asked Questions

Can I undo Remove Duplicates if I delete something by mistake?

No. Remove Duplicates cannot be undone after you close the file. If you have not saved yet, you can press Ctrl+Z to undo, but once you save and close, the deleted rows are gone. Always save a backup copy of your original data before using Remove Duplicates.

What if I have duplicates in some columns but not others?

When you use Remove Duplicates, you can choose which columns to check. If you only want to find rows where the Name column matches but the Email column is different, uncheck the Email column in the Remove Duplicates dialog. Excel will then only look at the columns you selected.

Does Conditional Formatting work on very large spreadsheets?

Yes, but it may slow down your file. If you have more than 100,000 rows, consider using Remove Duplicates or a formula instead. Conditional Formatting applies formatting rules to every cell, which takes more processing power on huge datasets.

How do I find duplicates if the values have extra spaces or different capitalization?

Conditional Formatting treats "Sarah" and "sarah" as different values, and "Sarah " (with a space) as different from "Sarah". Use Find and Replace to clean up spacing first: press Ctrl+H, search for extra spaces, and replace them. For capitalization, you can use the EXACT function in a formula to catch these differences, or manually review flagged duplicates to see if they are truly the same.

Can I highlight duplicates across multiple sheets?

Conditional Formatting only works within a single sheet. To find duplicates across sheets, use a COUNTIF formula that references the other sheet, like =COUNTIF(Sheet2!$A$2:$A$100,A2). This counts how many times each value in your current sheet appears in Sheet2.