How to spot duplicates in your Excel spreadsheet

Excel has a built-in tool that highlights duplicate values so you can see them without manually scanning thousands of rows. The fastest way is to select the column or range where duplicates might exist, then use the Conditional Formatting feature to color them. This works whether you have 50 entries or 50,000 — Excel marks every duplicate in seconds.

The duplicates stay in your spreadsheet; this method just makes them visible. You decide what to do with them afterward. This is safer than deleting first and asking questions later, because sometimes what looks like a duplicate is actually a legitimate repeat entry.

Key Takeaways

  • Select your data range, go to Conditional Formatting, choose Highlight Cell Rules, then Duplicate Values to color all duplicates at once.
  • The Remove Duplicates feature deletes duplicate rows permanently, so back up your file first and make sure you understand what will be removed.
  • Excel considers two entries duplicates only if every column in the selected range matches exactly — capitalization, spacing, and punctuation all matter.
  • Use the COUNTIF function to count how many times each value appears, which helps you decide whether to keep one copy or remove all of them.

Using Conditional Formatting to highlight duplicates

Open your spreadsheet and click on the first cell of the data you want to check. Hold Shift and click on the last cell of that range to select everything in between. If your data is in column A from row 2 to row 500, click A2, then hold Shift and click A500.

Go to the Home tab at the top of the screen. Find the Conditional Formatting button — it is usually in the Styles group on the right side of the ribbon. Click the dropdown arrow next to it. Select Highlight Cell Rules, then Duplicate Values. A dialog box appears. Leave the default color (usually light red) selected and click OK. Every duplicate value in your range now appears highlighted in that color.

If you want a different color, click Conditional Formatting again, choose Highlight Cell Rules, Duplicate Values, and pick a new color before clicking OK. You can also use this same process on multiple columns at once by selecting them together — just click the first column header, hold Ctrl, and click the other column headers you want to include.

Removing duplicates with the Remove Duplicates feature

Before you use this feature, save a backup copy of your file. The Remove Duplicates tool deletes rows permanently, and there is no undo if something goes wrong. Once you are ready, select the entire data range that contains duplicates — include the header row if your data has one.

Go to the Data tab at the top. Look for the Remove Duplicates button. It is usually in the Data Tools group. Click it. A dialog box opens showing all the columns in your selection. By default, all columns are checked. This means Excel will only mark a row as a duplicate if every single column matches the other row exactly. If you want to check only certain columns — for example, only the Name column — uncheck the columns you want to ignore.

Click OK. Excel removes every row it identifies as a duplicate and tells you how many rows were deleted. The remaining data stays in place. If the result is not what you expected, close the file without saving and reopen your backup copy to start over.

Understanding what Excel considers a duplicate

Excel is very literal about duplicates. Two entries are duplicates only if they match exactly in every column you selected. "John Smith" and "john smith" are not duplicates because of the capital letters. "Smith, John" and "John Smith" are not duplicates because the order is different. A space at the end of one entry but not the other makes them different too.

This exactness is usually helpful — it means you will not accidentally delete entries that are similar but not identical. However, it also means you might miss duplicates that are spelled slightly differently or have extra spaces. If you suspect this is happening, use Find and Replace to clean up formatting first. Go to Home, click Find and Replace, and search for common problems like extra spaces at the beginning or end of entries.

Using COUNTIF to count how many times each value appears

If you want to know how many duplicates exist before you delete anything, use the COUNTIF function. This creates a helper column that counts occurrences. Click on an empty column next to your data — if your data is in column A, use column B. Click on the first cell in that column and type this formula: =COUNTIF($A$2:$A$500,A2). Replace A2:A500 with your actual data range and A2 with the first cell of your data.

Press Enter. The cell now shows a number — 1 if that entry appears only once, 2 if it appears twice, and so on. Click on that cell again, then drag the small square at the bottom right corner down to the last row of your data. This copies the formula down and counts occurrences for every entry. Now you can see at a glance which values are duplicated and how many times they appear. You can sort by this column to group all the duplicates together, making it easier to decide what to keep.

Removing duplicates while keeping one copy

If you want to keep one copy of each duplicate entry and remove only the extras, the Remove Duplicates feature does this automatically. When you use it, Excel keeps the first occurrence of each value and deletes all the others. So if "John Smith" appears three times in rows 5, 12, and 18, Excel keeps the one in row 5 and removes rows 12 and 18.

This works well if your data is in the order you want to keep. If the first occurrence is not the one you want to keep, sort your data first so the version you want appears earliest. Then run Remove Duplicates. Alternatively, use the COUNTIF method described above, sort by the count column to put all duplicates together, and manually delete the ones you do not want to keep.

Checking for duplicates across multiple columns

Sometimes you need to find duplicates based on more than one column. For example, you might have two people named John Smith, and you only want to flag them as duplicates if they also have the same email address. Select all the columns that matter — in this case, Name and Email. Go to Data, click Remove Duplicates, and make sure both columns are checked. Excel will only mark a row as a duplicate if both the name and email match another row exactly.

You can also use a formula approach for this. Create a helper column and use COUNTIFS instead of COUNTIF. The formula would be: =COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2). This counts how many times the combination of values in columns A and B appears together. A result of 1 means it is unique; a result of 2 or higher means it is a duplicate.

Frequently Asked Questions

Does highlighting duplicates change my data?

No. Conditional Formatting only adds color to cells; it does not modify or delete anything. Your data stays exactly as it is. You can remove the highlighting anytime by selecting the range, going to Conditional Formatting, and choosing Clear Rules.

What happens if I remove duplicates and regret it?

If you have not closed the file, press Ctrl+Z to undo the deletion. If you have closed it, the changes are permanent unless you saved a backup copy first. Always save a backup before using Remove Duplicates.

Can I find duplicates in two different sheets?

The built-in tools only work within a single sheet. To compare two sheets, copy one sheet's data into a temporary column in the other sheet, then use COUNTIF or Remove Duplicates on the combined range. Alternatively, use a formula like =COUNTIF(Sheet2!$A:$A,A2) to check if each value in Sheet1 appears in Sheet2.

Why does Excel say there are no duplicates when I know there are?

Check for extra spaces, different capitalization, or different punctuation. Excel treats "John" and "John " (with a space at the end) as different values. Use Find and Replace to clean up formatting, or use a formula like =TRIM(A2) to remove extra spaces before checking for duplicates.

Can I undo Remove Duplicates if I already closed the file?

No. Once you close the file after deleting duplicates, the deletion is permanent. This is why backing up your file before using Remove Duplicates is essential. If you did not back up, check your computer's file recovery or version history features, but there is no may provide they will have a saved copy.