How to Find Duplicates in Excel Quickly and Accurately

To find duplicate values in Excel, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated entries; use a COUNTIF formula if you want a clear duplicate flag in a separate column, or Data > Remove Duplicates if you intend to delete repeats. Start by deciding whether you mean repeated values in one column or repeated records across several columns.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows. The instructions also work in recent desktop versions; some menus may look slightly different in Excel for Mac or Excel for the web.
The worksheet and range you want to check. If you have headers, identify the first row of actual data so you do not accidentally include the column heading.
A backup or copy of the workbook if you plan to remove duplicates. Highlighting and formulas are easy to reverse; deleting rows is not always easy to reconstruct.

Step-by-Step

Decide what counts as a duplicate. For a list of email addresses, a duplicate usually means the same address appears more than once in that column. For a customer table, you might mean a repeated email address, or a record where the name, address, and phone number all match. Decide this before selecting a method. Excel’s Duplicate Values rule checks the selected cell values; it does not automatically decide whether two entire rows represent the same record.
Select the data range carefully. Click the first data cell and drag to the last, or click the first cell, hold Shift, and click the last. For example, if email addresses are in A2:A100 and row 1 contains the heading, select A2:A100 rather than the whole worksheet column. If you want to check full records, select the complete table, including all columns that define a record, but exclude unrelated columns such as a unique transaction ID if that should not affect matching.
Highlight repeated values with the built-in rule. With the range selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style, and select OK. Excel highlights every occurrence of values that appear more than once in the selected range, including the first occurrence. This is the quickest visual check and does not change or delete any data.
Review the highlighted cells. Scan the results and confirm that Excel has highlighted the right kind of repetition. The rule is useful for finding likely matches, but it cannot tell whether similar-looking entries should be treated as the same person or product. For example, Alex Lee and Alex Lee followed by a space may appear identical at a glance, while Excel may treat them as different text. Check spelling, spaces, and the meaning of the column before taking action.
Add a formula flag if you need a sortable result. For example, with names in A2:A100 and a blank helper column B, enter this in B2: =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"). Copy the formula down through B100. COUNTIF counts how many times the value in A2 appears in the checked range; if the count exceeds one, the result is Duplicate. If A2 contains Sam and Sam appears three times in A2:A100, the formula returns Duplicate for each of those three rows. The dollar signs keep the search range fixed as you fill the formula down.
Flag only later occurrences when that is more useful. With values in A2:A100 and the helper formula starting in B2, use =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence"), then fill down. The expanding range checks from the first data row through the current row. If Sam appears in A2, A8, and A20, the first row is labeled First occurrence and the later two are labeled Duplicate. This is handy when you want to retain one copy and identify the extra entries. If blank cells are present, use the next step to avoid labeling blanks as repeated values.
Exclude blank cells from a formula check. With values in A2:A100 and a helper formula in B2, enter =IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")) and fill down. This leaves the helper cell blank when A2 is blank, rather than treating empty cells as a value to classify. Review the range for cells containing spaces: a cell with a space is not truly empty, even though it can look blank.
Check repeated records using more than one column. Suppose first names are in A2:A100 and last names are in B2:B100. In C2, enter =IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"Duplicate record","Unique record") and fill down. COUNTIFS counts rows where both the first name and last name match the current row. If Taylor is in A2 and Chen is in B2, the formula marks the row as a duplicate record only when the pair Taylor and Chen occurs more than once. Add another paired range and criterion for each additional field that must match.
Filter the helper results to inspect matches. Click a cell in your table and choose Data > Filter, then use the drop-down in the helper column to show only Duplicate or Duplicate record. On Windows, Ctrl+Shift+L toggles filters; in Excel for Mac, Command+Shift+F is commonly used. If the shortcut differs in your setup, use the Data tab. Read the full row before deleting anything, since other columns may explain why records that share one value are both valid.
Remove duplicates only after checking your criteria. Save a copy first. Select a cell in the table, then choose Data > Remove Duplicates. In the dialog, confirm that My data has headers is selected if your table has a heading row. Check only the columns that define a duplicate: selecting Email alone removes rows with repeated email addresses, while selecting several columns requires those selected values to match as a set. Choose OK and read Excel’s summary of how many duplicate values were removed and how many unique values remain. This action deletes rows from the current data; use Undo immediately if the result is not what you intended.

Common Mistakes

Selecting the wrong columns: A duplicate-value highlight applied to several columns evaluates values in the selected range, not whole-row identity. If your goal is to identify repeated customer records, use a formula that checks the defining fields together, or use Remove Duplicates with the correct columns selected.

Including a heading: If the header is inside the selected range, Excel may compare it like ordinary data. Usually it will not cause a problem, but it can be highlighted if the same text occurs elsewhere. Exclude the heading in formulas, or confirm My data has headers in the Remove Duplicates dialog.

Deleting rows before reviewing them: Two records can share an email address but contain different order details, or a family can share one phone number. Removing every row with a repeated value may discard valid information. Inspect the whole record and keep a backup before deletion.

Assuming look-alike text is identical: Leading or trailing spaces, inconsistent punctuation, and different spellings can prevent a match. Conversely, number formats can make different underlying values appear alike. For instance, a date displayed without its time may hide different time values. Check the actual cell contents and the column’s intended meaning.

Forgetting that COUNTIF is not case-sensitive: COUNTIF normally treats uppercase and lowercase text as equivalent, so ABC and abc count as matches. It also interprets the asterisk and question mark as wildcard characters in criteria. If your values contain those characters or capitalization matters, verify the results rather than relying on a basic COUNTIF check.

Tips

Turn the dataset into an Excel table before working with a frequently updated list. Select a cell in the data and choose Insert > Table, making sure the header option is correct. Tables expand as you add rows, which helps keep the data organized; check conditional-formatting and formula ranges when new data is added, especially if you used fixed cell references such as A2:A100.

Use one method to locate matches and a separate review step to decide what to do. Conditional formatting is best for a quick visual scan, a helper formula is better when you need to filter or count flagged rows, and Remove Duplicates is for deleting records after you have defined the matching columns. Keeping those tasks separate reduces the chance of removing good data by mistake.

If spaces are a known issue, make a cleaned helper column rather than altering the original values immediately. For example, if source text is in A2, =TRIM(A2) removes ordinary leading and trailing spaces and reduces repeated spaces between words. Copy the cleaned results, compare them, and keep the original column until you have confirmed the cleanup did not change meaningful text. TRIM does not remove every kind of invisible character, such as some nonbreaking spaces copied from websites.

Keep a note of the columns used to define a duplicate. “Same email” and “same name plus date of birth” are different rules and can produce different results. Recording the rule helps you repeat the check consistently next time and explain why a row was kept or removed.

Troubleshooting

No cells are highlighted: Confirm that the selected range includes the data and that the values really repeat within that range. A rule applied to A2:A100 cannot find a matching entry in column C. Also check that the conditional formatting rule is still applied: choose Home > Conditional Formatting > Manage Rules and inspect the Applies to range. Expand or correct it if the data range changed.

Expected duplicates are not matching: Compare the cells more closely for spaces, punctuation, spelling, or different underlying values. If you suspect extra spaces, test a TRIM helper column as described above. If numbers were imported as text in some rows and stored as numbers in others, standardize the data type in a separate column and compare the standardized values. Do not convert the original data until you know the conversion is safe.

Too many rows are marked: Check whether the formula is looking at the intended range and whether a common value such as blank cells or a placeholder like N/A is repeated. For record matching, include enough columns to distinguish legitimate records. If only later occurrences should be flagged, use the expanding-range formula rather than the formula that marks every instance.

Remove Duplicates removed more than expected: Use Ctrl+Z on Windows or Command+Z on Mac immediately, if the action is still undoable, and confirm the restored rows. Then repeat the command on a backup and review the selected columns in the dialog. If you have performed other actions since removal, restore from your saved copy or version history rather than assuming Undo will recover the original table.

The formula shows an error or does not fill correctly: Check that the formula was entered in the intended helper column and that its range references match your data. Excel formulas in this guide use commas as argument separators; some regional settings use semicolons, in which case replace commas with semicolons. When copying down, keep the dollar signs on the fixed range and make sure the row reference for the current value changes as expected.

Frequently Asked Questions

Does Excel distinguish uppercase and lowercase when finding duplicates?
Not with the standard Duplicate Values conditional-formatting rule or COUNTIF method; they generally treat ABC and abc as the same text. If capitalization is meaningful, use a case-sensitive comparison approach and verify a small sample before applying it to a large list.

Does the Duplicate Values rule delete anything?
No. It changes only the cell formatting so repeated values are easier to see. To delete repeated records, use Data > Remove Duplicates after checking the matching columns and saving a copy.

Can I find duplicate rows rather than duplicate cells?
Yes. Use COUNTIFS to compare the fields that define a record, as in the first-name and last-name example, and add more field pairs when needed. Or use Remove Duplicates and select the full set of columns that must match for a row to count as repeated.

Will removing duplicates keep the first occurrence?
Excel’s Remove Duplicates feature removes repeated entries based on the selected columns and retains one occurrence. Sort the table first if you need a particular record, such as the newest or most complete row, to be the one retained; make sure the sort includes the entire table.

Can I check for duplicates in Excel for Mac or Excel for the web?
Yes. The core conditional-formatting, formula, and duplicate-removal methods are available in current Excel versions on Mac and the web, though dialog layouts and shortcuts can differ. If a shortcut does not work, use the named ribbon path, such as Home > Conditional Formatting or Data > Remove Duplicates.

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.