How to spot duplicates in Excel while keeping all your data intact

Excel has built-in tools to highlight duplicate rows or values without deleting anything. The most straightforward method is Conditional Formatting, which colors cells that match others in your selection. You can also use the Remove Duplicates feature in preview mode before committing to any changes, or write a formula to flag duplicates yourself. Which method works best depends on whether you want a visual marker, a list of which rows are duplicates, or a count of how many times each value appears.

The reason to identify without deleting is practical: you often need to review duplicates before removing them. A customer name appearing twice might be a data entry error, or it might be two separate orders from the same person. A duplicate address might indicate fraud or a legitimate shared household. Seeing the duplicates first lets you decide what to do with each one.

Key Takeaways

  • Conditional Formatting highlights duplicates by color without changing your spreadsheet, and works on any column or range you select.
  • The Remove Duplicates feature shows you a preview of what will be deleted before you confirm, so you can back out if needed.
  • A COUNTIF formula in a helper column counts how many times each value appears, letting you see duplicates at a glance.
  • Filtering by color or by formula result lets you isolate duplicates for review without affecting the rest of your data.

Using Conditional Formatting to highlight duplicates

Conditional Formatting is the fastest visual method. Select the column or range where you want to find duplicates — for example, a column of email addresses or customer IDs. Go to the Home tab, click Conditional Formatting, then choose Highlight Cell Rules and Duplicate Values. Excel will when ready color any cell that appears more than once in your selection.

By default, duplicates turn red. You can change the color by clicking Conditional Formatting again, selecting Manage Rules, editing the rule, and choosing a different format. This method works on a single column at a time. If you need to find duplicates across multiple columns — for instance, rows where both the first name and last name match — you will need a formula instead.

The advantage of Conditional Formatting is that it leaves your data untouched. You can print the spreadsheet with the colored cells, share it with a colleague to review, or filter to show only the highlighted rows. When you are done reviewing, you can remove the formatting by selecting the range again, clicking Conditional Formatting, and choosing Clear Rules.

Using a formula to count and flag duplicates

A COUNTIF formula gives you more control and works across multiple columns. In a new column next to your data, type =COUNTIF($A$2:$A$100,A2), replacing A with your column letter and 100 with the last row of your data. This formula counts how many times the value in A2 appears in the entire range A2:A100. Copy the formula down the column.

Any value that appears more than once will show a number greater than 1. A value appearing three times will show 3 in every row where it appears. This method lets you see at a glance which entries are duplicated and how many times. You can then sort by the formula column to group all duplicates together, or filter to show only rows where the count is greater than 1.

For duplicates across multiple columns — finding rows where both the name and email match — use COUNTIFS instead. The formula would be =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2), checking both columns A and B. This is slower on very large spreadsheets but more precise when you need to match on more than one field.

Previewing duplicates before the Remove Duplicates feature commits to deletion

Excel's Remove Duplicates feature has a preview step that shows you what will be deleted. Select your data including headers, go to the Data tab, and click Remove Duplicates. A dialog will appear asking which columns to check. If you want to find duplicates based on one column only, uncheck the others. Click OK, and Excel will show you how many duplicate rows it found and how many it removed.

The catch is that this feature removes duplicates when ready — it does not show you a preview of the actual rows first. To see which rows will be deleted before committing, use Conditional Formatting or a formula instead. If you do use Remove Duplicates and regret it, press Ctrl+Z (or Cmd+Z on Mac) right away to undo.

One useful workaround: copy your data to a new sheet, run Remove Duplicates there, and compare the two sheets side by side. This way you can see what was removed without losing the original. Then delete the test sheet and keep your original data intact.

Filtering to isolate duplicates for review

Once you have identified duplicates — either through Conditional Formatting colors or a formula column — you can filter to show only those rows. If you used Conditional Formatting, select your data, go to the Data tab, click Filter, then click the dropdown arrow in the column header. Uncheck the blank cells (the non-duplicates) to show only the colored ones.

If you used a COUNTIF formula, click the dropdown in the formula column and uncheck the value 1, so only rows with a count of 2 or higher appear. This isolates all duplicates in one view, making it easier to decide whether to delete them, merge them, or keep them as separate records.

Filtering does not delete anything — it just hides rows. When you remove the filter, all your data reappears. This is a safe way to review duplicates without risk.

Comparing two columns to find matches

Sometimes you need to find duplicates across two separate lists — for example, checking whether names in one column appear in another. Use a COUNTIF formula pointing to the other column. In a helper column, type =COUNTIF($B$2:$B$100,A2) to see if each value in column A appears anywhere in column B. A result of 0 means no match; any number higher means it appears in the other list.

You can also use VLOOKUP or INDEX/MATCH to return the matching row from the other list, which is useful if you need to see the full record alongside the match. These formulas are more complex but let you see not just whether a duplicate exists, but what data is associated with it in the other list.

Handling large spreadsheets efficiently

On spreadsheets with thousands of rows, Conditional Formatting can slow down your file. If your spreadsheet becomes sluggish, use a formula instead — formulas are faster on large datasets. You can also break the data into smaller chunks, find duplicates in each chunk, then combine your results.

Another option is to sort your data by the column you are checking. Duplicates will sit next to each other, making them straightforward to spot by eye. This works well for small to medium datasets and requires no formulas or special features.

Frequently Asked Questions

Can I find duplicates across multiple columns at once?

Conditional Formatting checks one column at a time. For multiple columns, use a COUNTIFS formula that references all the columns you want to match on. This shows you rows where the combination of values repeats, not just individual columns.

What if I want to keep one copy of each duplicate and delete the rest?

Use Conditional Formatting or a formula to identify which rows are duplicates, then manually delete the ones you do not need. Or use the Remove Duplicates feature, which keeps the first occurrence of each value and removes all later ones. Test it on a copy of your data first.

How do I find duplicates if my data has headers?

When using Conditional Formatting or formulas, start your selection or formula range on the first data row, not the header. For example, if your headers are in row 1, start at row 2. This prevents Excel from flagging the header as a duplicate of itself.

Can I highlight duplicates in different colors based on how many times they appear?

Conditional Formatting applies one format to all duplicates. For color-coding by frequency, use a formula column with COUNTIF, then explore Conditional Formatting to that column with multiple rules — one for values equal to 2, another for values equal to 3, and so on.

What happens if I undo the Remove Duplicates feature after closing the file?

Once you close and reopen the file, undo is no longer available. Always save a backup copy of your spreadsheet before using Remove Duplicates, or test the feature on a copy first. This way you can recover your original data if needed.