How to Check for Duplicate Data in Excel

How-to basics · Updated November 20, 2026

To check for duplicate values in Excel, select the cells you want to inspect and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated entries without deleting them. For a more controlled check, add a helper column with COUNTIF, then review or filter the results before you remove anything.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. Checking for duplicates can mean finding repeated values in one column, or finding rows that repeat across several columns; choose the method that matches your data. Save a copy before making changes that remove records.

What You’ll Need

  • An Excel workbook with the list or table you want to check.
  • A clear idea of what counts as a duplicate: the same value in one field, or the same combination of values across a row.
  • Optional: a spare column for a formula result, and a backup copy if you plan to delete duplicate records.

Step-by-Step

  1. Decide what should count as a duplicate. If you are checking an email list, repeated email addresses may be enough. For orders, the same customer name alone might not indicate a duplicate; you may need to match an order number and date together. Decide this first so you do not flag legitimate records. Also check whether your range has a header row, such as Email or Order ID, which should not be included in the check.
  2. Select the cells to inspect. For one field, select the values below the heading, such as A2:A100. Avoid selecting unrelated columns, because Excel will compare values in the selected range and may highlight repeated text that is not meaningful for your purpose. If the data is an Excel table, select the relevant table column’s data cells. You can also click a cell in the column, but selecting only the intended data makes the result easier to interpret.
  3. Highlight repeated values visually. On the ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a highlight style, and click OK. Excel formats every value that occurs more than once in the selected range, including the first occurrence. This is a quick review method; it does not change or delete the underlying values. On Excel for Mac, the Conditional Formatting command is also on the Home tab, though its placement or dialog layout can vary slightly. Excel for the web has conditional formatting controls as well, but the ribbon may be more compact.
  4. Check the highlighted results. Scan the marked cells and compare them with the surrounding records. If you see highlights in a column that contains blanks, check whether those blanks are actual empty cells or formulas returning empty text. Repeated blank-looking cells can be misleading. If you selected several columns at once, consider restarting with just the key field or use the row-level formula in step 6 instead.
  5. Use a formula when you need a clear TRUE/FALSE result. With the values to check in A2:A100 and the first data row in row 2, enter this in B2: =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). In a sample where A2:A6 contains Ana, Ben, Ana, Cara, and Ben, the formula returns TRUE for the second Ana and both Ben entries, and FALSE for Cara or an empty A cell. The blank check prevents empty cells from being reported as duplicates. Fill the formula down alongside the list by dragging the fill handle or double-clicking it. TRUE means the value occurs at least twice in the full range; FALSE means it does not.
  6. Use a count if you need to know how often each value occurs. With values in A2:A100, enter this in B2: =COUNTIF($A$2:$A$100,A2). If Ana appears three times in that range, the result on each Ana row is 3; a value appearing once returns 1. Fill the formula down. Filter the helper column to show numbers greater than 1 if you want to review only repeated values. Unlike the previous formula, this count also returns a number for a blank cell, so ignore blank rows or adapt the check if your list contains empty entries.
  7. Check for duplicate records across multiple columns. If a record is only a duplicate when both its ID and date match, use a formula that compares both fields. With IDs in A2:A100 and dates in B2:B100, enter this in C2: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1. If the ID-and-date combination on row 8 also appears on row 19, the formula returns TRUE on both rows. Fill down to classify the records. Add more criteria ranges and criteria pairs if a duplicate is defined by three or more fields. Keep each criteria range the same size as the other ranges.
  8. Filter and review before cleanup. If you used a helper column, select your data and use Data > Filter, then filter the helper column for TRUE or for counts greater than 1. Check each group against the original records and decide which entry, if any, should remain. To delete duplicates after review, make a backup, select the data, and choose Data > Remove Duplicates. Confirm which columns Excel should use to identify duplicates, and whether the table has headers, then click OK. Excel keeps one instance of each matching set and removes the others; it does not simply highlight them.

Common Mistakes

A frequent error is selecting the wrong range. Including a heading can make the result confusing, while selecting only part of the list can miss matches outside that selection. Check the first and last data rows before applying a rule or formula.

Another mistake is assuming that every repeated name means a repeated record. Two customers can share a name, and one customer can legitimately have several orders. Use the field or combination of fields that defines a duplicate for your task. When using Remove Duplicates, review the checked column names carefully: if you select only Name, Excel may remove rows with the same name even when their other details differ.

Formatting is also easy to misread. Conditional formatting marks every member of a repeated group, not only later copies. And COUNTIF does not distinguish uppercase from lowercase, so Excel generally treats entries such as ABC and abc as the same value for this purpose. Finally, spaces or hidden characters can make entries that look identical compare as different values.

Tips

  • Keep the original data intact while you investigate. Add a helper column or copy the data to another worksheet before sorting, filtering, or removing records.
  • Convert a well-structured list into a table with Insert > Table. Tables make it easier to work with headers and can expand when new rows are added. Check that a formula or conditional formatting rule covers new entries.
  • Sort by the key field after identifying repeats. Matching values will sit together, making it easier to decide which record is correct and whether other columns differ.
  • For a one-off visual check, conditional formatting is fast. For a repeatable audit, a helper formula is more useful because you can filter, count, and revisit its results.
  • Do not treat a duplicate check as automatic proof that a record is wrong. Confirm the source data and the business rule before deleting anything.

Troubleshooting

Nothing is highlighted, but you can see repeated entries. Verify that both copies are inside the selected range and that the cells contain the same underlying value. Extra spaces, nonprinting characters, different punctuation, or numbers stored as text can make entries look alike while Excel treats them as different. You can inspect a cell in the formula bar and compare the raw values. If the source data is inconsistent, clean it in a separate column or copy before checking again.

Too many cells are highlighted. Confirm that you selected only the intended field and not a broad block of unrelated data. Conditional formatting checks repeated cell values, not whether entire records are identical. For whole-record checks, compare the key columns with COUNTIFS or check each relevant field before removing rows.

The helper formula shows an error or the same result everywhere. Check that the formula starts in the row you intended, the column references match your layout, and the fixed range uses dollar signs as shown. After entering a formula in B2, fill it down; otherwise only the first row is checked. If your Excel uses semicolons rather than commas as formula separators because of regional settings, use the separator Excel expects in your installation.

Remove Duplicates deleted more than expected. Press Ctrl+Z immediately if the action is still undoable, or restore the saved backup. Then repeat the command with the correct columns selected. On Windows, Ctrl+Z undoes the last action; on Mac, use Command+Z. If you are unsure which columns define a match, do not run the deletion step until you have reviewed the formula or highlighted results.

Frequently Asked Questions

Does Excel have a built-in duplicate checker?
Yes. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values highlights repeats in a selected range. Data > Remove Duplicates is a separate command that deletes repeated rows based on the columns you choose.

Does the duplicate formatting remove or change data?
No. It only applies formatting so you can spot repeated values. To delete records, use Remove Duplicates after checking the selection and making a backup.

How can I highlight only the second and later occurrence?
Use a helper formula with an expanding range instead of the built-in duplicate rule. With values in A2:A100 and the formula entered in B2, =COUNTIF($A$2:A2,A2)>1 returns FALSE for the first occurrence and TRUE for each later occurrence. Fill it down. Add a blank-cell condition if blank rows are in the range.

Can Excel find duplicate rows when only some columns matter?
Yes. Use COUNTIFS with one criteria range for each field that defines a match, or use Remove Duplicates and check only those columns. For example, matching an invoice number and date is different from requiring every column in the row to match.

Are duplicates case-sensitive?
COUNTIF and the standard duplicate-highlighting rule are not case-sensitive for ordinary text comparisons. If letter case must distinguish entries, use a case-sensitive approach such as a formula based on EXACT and review the results carefully.

Will a duplicate rule include new rows I add later?
Not always. A rule applied to a fixed cell range may not cover later rows. If you use an Excel table, check the rule’s Applies to range after adding data; you can also extend the range through Home > Conditional Formatting > Manage Rules.

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.