How to Find Duplicate Data in Excel: Step-by-Step Guide

How-to basics · Updated November 20, 2026

To find duplicate data in Excel, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated values, including the first occurrence. For a reusable flag or to check duplicates across several columns, use a COUNTIF or COUNTIFS formula instead. Before you start, decide whether a duplicate means a repeated value in one column or a repeated record made up of multiple fields.

What You’ll Need

  • An Excel workbook with the data you want to check. These instructions are for Microsoft Excel for Microsoft 365 on Windows.
  • A clear idea of which cells or columns define a duplicate. For example, an email address may be enough, while a customer record may require matching name, email, and postal code.
  • Optional: a spare column if you want a formula to mark duplicates. Save a copy before making substantial changes to the data.

The main ribbon commands are also available in current Excel for Mac, although ribbon layout and shortcut keys can differ. Excel for the web has Conditional Formatting and Remove Duplicates too, but some dialog details may look different.

Step-by-Step

  1. Prepare the data range. Open the worksheet and locate the column or table to inspect. If the first row contains headings, keep those headings out of the selection; select the data cells beneath them. For example, if customer IDs are in A1:A101 and A1 is the heading, select A2:A101. You can click the first cell, then hold Shift and click the last cell, or enter the range in the Name Box to the left of the formula bar. If your data is an Excel table, you can select the relevant table column’s data cells. Checking one column at a time is usually the clearest way to find repeated IDs, email addresses, or product codes.
  2. Apply the Duplicate Values rule. With the range selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, confirm that Duplicate is selected in the left-hand list, choose a highlight style from the right-hand list, and select OK. Excel applies conditional formatting to the selected cells. Each value that appears more than once in that range is highlighted, including its first occurrence; the rule does not identify only the later copies.
  3. Check the highlighted results. Scan the marked cells and compare them with the surrounding records. A highlighted value means Excel found another equal value within the range covered by the rule. It does not necessarily mean the entire row is repeated. For example, if two customers share a last name, checking the last-name column alone will mark both even if their email addresses and customer IDs differ. If the highlight color is hard to see, use the same menu again and choose another formatting style.
  4. Use a formula to create a duplicate flag. A helper column is useful when you want a filterable result, want to retain a clear audit trail, or need a rule that can be copied to new rows. With IDs in A2:A100 and the formula entered in B2, use =COUNTIF($A$2:$A$100,A2)>1. The result is TRUE when the value in A2 appears more than once in the specified list, and FALSE when it appears once. The dollar signs keep the list range fixed when you fill the formula down. Enter it in B2, press Enter, then use the fill handle at the bottom-right corner of the cell to copy it through B100. Filter the helper column to show TRUE. This formula, like Excel’s built-in duplicate rule, marks every occurrence of a repeated value.
  5. Check whether complete records repeat. If a duplicate must match across multiple fields, count the matching combinations rather than checking just one column. For example, with first name in A2:A100, last name in B2:B100, and email in C2:C100, enter this in D2: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1. Fill the formula down through D100. TRUE means that the same first name, last name, and email combination appears more than once. Change the ranges and criteria to match the fields that define a duplicate in your own data. All ranges should cover the same number of rows.
  6. Review the scope of the check. Conditional formatting only compares values within the range to which its rule applies. If you selected A2:A50 but the list continues to A100, duplicates in the unselected rows will be missed. Likewise, separate checks on separate worksheets do not automatically find matches between sheets. Extend the selected range or use a formula that references the full data set when you need broader coverage.
  7. Decide what to do with matches. Finding a duplicate does not establish that a row should be deleted. Check the records and decide whether they are genuine repeats, valid shared values, or entries that only look alike. If you later choose to remove duplicates, make a backup first. Excel’s Data > Remove Duplicates command deletes repeated rows from the selected data and can change the remaining list; it is a separate action from highlighting or flagging.

Common Mistakes

  • Selecting the wrong range: If the heading is included, the rule might treat a repeated heading as a match; if part of the data is excluded, matches outside the selection will not be detected. Confirm the range before applying the rule.
  • Checking only one field when a record has several: Shared surnames or product categories are not necessarily duplicate records. Define the fields that make a record unique, then use a combination check such as COUNTIFS where appropriate.
  • Confusing a repeated value with a repeated row: A repeated value in one column says nothing by itself about whether every other value in the row matches. Compare the relevant fields before taking action.
  • Deleting before reviewing: Remove Duplicates changes the data and may discard information from rows you meant to keep. Work on a copy and verify which columns Excel should use to identify duplicates before confirming.
  • Assuming visually similar text is identical: Extra spaces, hidden characters, and different punctuation can prevent two entries from matching. Conversely, capitalization differences generally do not distinguish text in Excel’s standard duplicate rule or COUNTIF. Inspect suspicious entries rather than relying only on appearance.

Tips

  • Turn on filters for easier review. Select your heading row and data, then choose Data > Filter. You can filter a formula helper column to TRUE, or sort and inspect the highlighted cells. A filter changes what is displayed, not the underlying records.
  • Use a table for lists that grow. Select the data and choose Insert > Table, making sure the header option is set correctly. Tables make it easier to work with structured columns and extend formulas as new rows are added. Check that conditional formatting covers added rows; rules created on a fixed cell range may need their Applies to range expanded.
  • Keep an untouched source copy. Duplicate checks often lead to data cleanup. Preserve the original worksheet or workbook so you can compare records and recover information if you later remove or edit a row.
  • Check leading and trailing spaces. If entries that should match are not highlighted, a space at the start or end may be the cause. You can make a cleaned helper column with TRIM. With the original text in A2, enter =TRIM(A2) in B2 and fill down; compare the cleaned results, and retain the original data until you confirm the changes are appropriate. TRIM removes ordinary extra spaces but does not remove every kind of nonprinting character.
  • Use consistent data types. A number stored as text may behave differently from a numeric value, and dates can be stored as serial numbers or text. Standardize the column when practical before checking it. Do not convert identifiers such as postal codes to numbers if leading zeros are meaningful.

Troubleshooting

Nothing is highlighted: Check that the correct range was selected and that the Duplicate option—not Unique—was chosen in the dialog. Verify that the values are truly the same, not just visually similar. Look for added spaces, different punctuation, or a number stored as text in one row and as a number in another. You can also reopen Home > Conditional Formatting > Manage Rules to inspect the rule and the range it applies to.

Too many cells are highlighted: The selected field may contain values that are commonly shared, such as a city or status. Check a more specific identifier, or use COUNTIFS to compare a combination of columns. If the highlight appears outside the intended area, inspect the rule’s Applies to range in Manage Rules and adjust it.

The formula shows an error or does not fill correctly: Check that the formula starts with an equals sign, that parentheses are balanced, and that the ranges cover the same rows. The examples use commas between arguments, as is standard in many English-language Excel installations. Some regional settings use semicolons instead; if Excel rejects a formula with commas, use the separator required by your installation. When filling down, keep dollar signs around the fixed list range, but leave the row reference for the value being checked relative so it changes from A2 to A3, A4, and so on.

New rows are not included: A rule or formula written for a fixed range such as A2:A100 will not necessarily cover records added below row 100. Extend the rule’s Applies to range and copy the formula into new rows, or use an Excel table so its formula column can expand with the table. Then verify the new records are included in the comparison.

The result seems to miss duplicates on another sheet: A rule applied to one sheet’s range checks that range, not every worksheet in the workbook. Put the records into one comparison range or create a formula that explicitly references the other range. For a large multi-sheet cleanup, first decide how to combine the data and preserve each row’s source so you can validate any matches.

Frequently Asked Questions

Does Excel highlight the first occurrence too?

Yes. The Duplicate Values rule highlights all cells whose value occurs more than once in the selected range, including the first occurrence. It does not label the original separately from later copies.

Can I find duplicate rows instead of duplicate cells?

Yes. Define which columns make a row a duplicate, then use COUNTIFS across those columns, as in the example above. Checking one column only finds repeated values in that column, not fully repeated rows.

Can Excel find duplicates across two worksheets?

Yes, but selecting a range and applying the built-in rule on one sheet will not automatically compare it with another. You can use a formula that refers to the other sheet, or combine the data into one range and check it there. Make sure the comparison includes the full range of interest.

Does the duplicate check distinguish uppercase and lowercase?

Excel’s built-in duplicate highlighting and COUNTIF are generally case-insensitive. For example, customer and CUSTOMER are treated as the same text for these checks. If capitalization must count as a difference, use a case-sensitive method rather than relying on the standard rule.

How do I remove duplicates after finding them?

First save a copy and review which columns define a duplicate. Then select the data and choose Data > Remove Duplicates, select the columns Excel should compare, and confirm. This removes rows, so check the result before saving over your original data.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.