The fastest way to spot duplicates in Excel

Excel has a built-in tool called Conditional Formatting that highlights duplicate values automatically. Select the column or range where you want to find duplicates, 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 color — usually red or another highlight — so you can see them when ready without scrolling through thousands of rows manually.

If you need to remove duplicates rather than just see them, Excel also has a Remove Duplicates button on the Data tab. Select your data range, click Data, then Remove Duplicates, and choose which columns to check. Excel will delete the extra copies and keep one instance of each unique entry. Be careful: this action cannot be undone, so copy your data to a new sheet first if you want to keep the original.

Key Takeaways

  • Conditional Formatting highlights duplicates in place so you can review them before taking action.
  • The Remove Duplicates tool deletes extra copies permanently, so save a backup before using it.
  • You can check for duplicates across multiple columns at once, not just one column.
  • The COUNTIF formula lets you mark duplicates yourself if you need more control over which copies to keep.

Using Conditional Formatting to highlight duplicates

Conditional Formatting is the safest way to find duplicates because it does not change your data — it just marks the cells so you can see them. Click on the first cell in the range you want to check. Hold Shift and click the last cell to select the entire range, or click the column header to select a whole column. Then go to Home, click Conditional Formatting, point to Highlight Cell Rules, and click Duplicate Values.

A dialog box will appear asking what color to use for duplicates. The default is light red fill with dark red text. You can change this by clicking the dropdown next to Format only cells with, but the default works fine. Click OK, and every cell that appears more than once in your range will be highlighted. If a value appears three times, all three cells will be marked — not just the second and third copies.

To remove the highlighting later, select the same range again, go to Conditional Formatting, click Manage Rules, select the rule you created, and click Delete Rule. This removes only the highlighting, not the data itself.

Removing duplicates to clean up your data

If you want to delete duplicate rows instead of just marking them, use the Remove Duplicates tool. First, select all the data you want to check — include headers if your data has them. Go to the Data tab and click Remove Duplicates. A dialog will open showing all your columns with checkboxes next to each one.

By default, all columns are checked. This means Excel will only delete a row if every single column matches another row exactly. If you want to check only certain columns — for example, only the Name column — uncheck the columns you do not want to compare. Then click OK. Excel will delete the duplicate rows and show you a message saying how many rows were removed.

This action cannot be undone with Ctrl+Z if you close the file, so always save a copy of your original data first. A straightforward way to do this: right-click the sheet tab at the bottom, click Move or Copy, select Create a Copy, and work on the copy instead of the original.

Using COUNTIF to mark duplicates yourself

If you want more control — for example, to keep the first copy and mark only the second and later copies — use a formula instead. In a new column next to your data, type this formula in the first row: =COUNTIF($A$1:A1,A1). Replace A with your actual column letter. This formula counts how many times the value in that row has appeared from the top of the range down to the current row.

Press Enter, then copy the formula down to every row in your data. The first copy of each value will show 1, the second copy will show 2, the third will show 3, and so on. Now you can sort or filter by this column to see only the duplicates (where the number is 2 or higher), or delete only those rows. This method takes more steps but gives you the chance to review what you are deleting before it is gone.

Finding duplicates across multiple columns

Sometimes you need to find rows that are duplicates based on more than one column. For example, you might have a list of customer orders and want to find cases where the same person ordered the same item twice. Both Conditional Formatting and Remove Duplicates can do this.

For Conditional Formatting, select all the columns you want to check together, then explore Conditional Formatting as usual. Excel will only highlight a row if all the selected columns match another row exactly. For Remove Duplicates, select all your data including all columns, click Remove Duplicates, and make sure only the columns you care about are checked. Uncheck any columns that should not be part of the comparison.

What to do when duplicates are legitimate

Not every duplicate is a mistake. If you have a list of transactions, the same amount might appear many times legitimately. If you have a customer list, multiple people might have the same last name. In these cases, highlighting all duplicates will create noise — you will see hundreds of marks that do not mean anything is wrong.

Instead, create a helper column with a formula that checks for duplicates based on the columns that actually matter. For a transaction list, you might check for the same amount on the same date from the same person. For a customer list, you might check for the same name and address together. Use COUNTIFS (with an S) instead of COUNTIF to check multiple columns at once: =COUNTIFS($A$1:A1,A1,$B$1:B1,B1) checks both column A and column B. This way you only flag the duplicates that are actually suspicious.

Frequently Asked Questions

Can I undo Remove Duplicates if I made a mistake?

If you have not closed the file, Ctrl+Z will undo it. If you have closed and reopened the file, the deletion is permanent. This is why you should always save a backup copy before using Remove Duplicates. Copy your sheet to a new tab, work on the copy, and delete the copy only after you are sure the results are correct.

Will Conditional Formatting find duplicates across different sheets?

No. Conditional Formatting only looks within the range you select on the current sheet. If you need to find duplicates across multiple sheets, copy all the data into one sheet first, explore Conditional Formatting, then delete the temporary sheet when you are done.

What if I want to keep the first copy and delete only the later ones?

Remove Duplicates keeps the first occurrence and deletes the rest, so it does exactly this by default. If you used COUNTIF to mark duplicates instead, sort by the helper column and delete all rows where the count is 2 or higher, leaving only the rows where the count is 1.

Can I find duplicates in a column that has blank cells?

Yes. Excel treats blank cells as a value, so if you have two or more empty cells in the same column, they will be marked as duplicates. If this is not what you want, filter out the blank rows first before explore Conditional Formatting or Remove Duplicates.

How do I find near-duplicates, like "John Smith" and "john smith"?

Excel's built-in tools treat these as different values because of the capital letters. If you need to find these, use Find and Replace with the Match Case option unchecked, or create a helper column that converts all text to lowercase using the LOWER function, then check that column for duplicates instead.