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 shade every duplicate entry in that range with color so you can see them at a glance.

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 version of each entry. This is permanent, so make a backup copy of your file first if you are not sure you want to lose the duplicates.

For larger spreadsheets or when you need more control over which duplicates to keep, a filter or sort approach gives you more options. Both methods are covered in detail below.

Key Takeaways

  • Conditional Formatting highlights duplicates with color so you can see them without changing your data.
  • The Remove Duplicates button deletes extra copies permanently, so back up your file first.
  • Sorting your data by the column you want to check groups duplicates together so you can spot them manually.
  • The COUNTIF function counts how many times each entry appears, which helps you find duplicates in very large datasets.

Using Conditional Formatting to highlight duplicates

Conditional Formatting is the easiest way to see duplicates without changing your spreadsheet. Start by clicking the column header or dragging to select all the cells you want to check. If you have a column of customer names and want to find which names appear more than once, select the entire name column.

Once your range is selected, go to the Home tab at the top of the ribbon. Click Conditional Formatting, which is usually on the right side of the Home tab. A menu will open. Click Highlight Cell Rules, then choose Duplicate Values. A dialog box will appear asking what color you want to use. The default is light red, but you can pick any color. Click OK.

Every cell in your selection that contains a value appearing more than once will now be highlighted. Scroll through your data to see which entries are duplicated. This method does not change your data — it only marks the duplicates so you can review them. If you want to undo the highlighting later, go back to Conditional Formatting, click Manage Rules, select the rule you created, and delete it.

Removing duplicates permanently with the Data tab

If you want Excel to delete the duplicate entries and keep only one copy of each value, use the Remove Duplicates feature. This action cannot be undone by pressing Ctrl+Z if you close the file, so save a backup copy of your spreadsheet before you start.

Select the range of data you want to check. Go to the Data tab on the ribbon. Look for the Remove Duplicates button — it is usually in the Data Tools section. Click it. A dialog box will open showing all the columns in your selection. By default, all columns are checked. If you want Excel to consider only certain columns when deciding what is a duplicate, uncheck the columns you want to ignore. For example, if you have columns for Name, Email, and Phone Number, you might check only Name and Email so that two people with the same name but different phone numbers are treated as duplicates.

Click OK. Excel will scan your data, find all the duplicates, and delete them, keeping the first occurrence of each value. A message will tell you how many duplicates were removed. Your data is now reduced to unique entries only.

Sorting to group duplicates together

Sorting is useful when you want to see duplicates grouped together so you can decide which ones to keep or delete manually. Select your entire data range, including headers if you have them. Go to the Data tab and click Sort. A dialog box will open asking which column to sort by. Choose the column you want to check for duplicates and click OK.

Excel will rearrange your rows so that identical values in that column are next to each other. Scroll through your data and look for rows with the same value appearing multiple times in a row. This method is slower than Conditional Formatting if you have thousands of rows, but it gives you a clear view of each duplicate group so you can decide what to do with them.

After sorting, you can manually delete the rows you do not want, or you can use the Remove Duplicates feature on the sorted data. Sorting also helps you spot typos — if you have "John Smith" and "Jon Smith" in the same column, sorting will put them next to each other so you can see they are probably the same person spelled differently.

Using COUNTIF to find duplicates in large datasets

The COUNTIF function counts how many times a value appears in a range. This is helpful when you have a very large spreadsheet and want to identify which entries appear more than once without highlighting or deleting anything. In a new column next to your data, type the formula =COUNTIF($A$2:$A$1000,A2), replacing A2 and A1000 with the actual range of your data. This formula counts how many times the value in A2 appears in the entire range.

Press Enter. The cell will show a number — 1 if the entry is unique, 2 if it appears twice, 3 if it appears three times, and so on. Copy this formula down to every row in your dataset. Now you can see at a glance which entries are duplicated and how many times they appear. Sort by this new column to group all the duplicates together, or filter to show only entries with a count of 2 or higher.

This method takes a bit longer to set up but gives you the most control. You can see exactly which entries are duplicated, how many times, and decide what to do with each one without permanently deleting anything until you are ready.

Handling duplicates when some columns should match and others should not

Sometimes you need to find duplicates based on only certain columns. For example, you might have a customer list where two rows with the same name but different email addresses should be treated as different people, not duplicates. The Remove Duplicates feature handles this by letting you choose which columns to check.

Select your data range and go to Data > Remove Duplicates. In the dialog box, uncheck any columns that should not be part of the duplicate check. If you only want to check the Name column, uncheck Email, Phone, and any other columns. Excel will then treat two rows as duplicates only if the Name column matches, regardless of what is in the other columns.

If you want to keep duplicates but just flag them for review, use Conditional Formatting on only the columns that matter. Select just the Name column, explore Conditional Formatting, and it will highlight only the names that repeat, leaving the other columns out of the check.

Common mistakes when finding duplicates

One frequent mistake is selecting only part of your data. If your customer list runs from row 2 to row 5000 but you only select rows 2 to 100, Excel will only check those 100 rows and miss duplicates in the rest of the spreadsheet. Always scroll to the bottom of your data before selecting to make sure you have the full range.

Another mistake is forgetting that Excel is case-sensitive in some functions but not others. Conditional Formatting treats "John" and "john" as different values, so they will not be highlighted as duplicates. If you suspect this is happening, use Find and Replace to standardize the capitalization before checking for duplicates.

A third mistake is not checking for extra spaces. A cell containing "John Smith " with a space at the end will not match "John Smith" without the space, even though they look identical. Use the TRIM function in a helper column to remove extra spaces before checking for duplicates if you suspect this is a problem.

Frequently Asked Questions

Can I undo Remove Duplicates after I close the file?

No. Once you close the file after using Remove Duplicates, you cannot undo the deletion. Always save a backup copy of your spreadsheet before removing duplicates. If you accidentally delete data you needed, you can recover it from the backup.

Will Conditional Formatting find duplicates across multiple columns?

Conditional Formatting checks each column separately by default. If you select columns A and B together and explore Conditional Formatting, it will highlight duplicates within column A and duplicates within column B, but not rows where A and B match together. To find duplicates based on multiple columns, use Remove Duplicates or COUNTIF with a formula that combines columns.

What if I have blank cells in my data?

Excel treats blank cells as values. If you have multiple empty cells in a column, Conditional Formatting will highlight all of them as duplicates. If this is not what you want, delete the blank rows first or use Remove Duplicates with the option to ignore empty cells if available in your version of Excel.

How do I find duplicates across two different sheets?

Excel's built-in tools only check within a single sheet. To find duplicates across sheets, copy all the data from both sheets into one temporary sheet, then use Conditional Formatting or Remove Duplicates. After you are done, delete the temporary sheet.

Can I find near-duplicates, like "John Smith" and "Jon Smith"?

Excel's standard tools only find exact matches. To find similar but not identical entries, sort your data by the column in question and scan manually for typos and variations. Some versions of Excel have a Fuzzy Match feature in Power Query, but it requires more advanced setup.