How to Find Duplicate Values in Excel: Step-by-Step

To find duplicate values in Excel, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel will color values that appear more than once. For a reusable TRUE/FALSE check beside your data, use a COUNTIF formula. The steps below cover both methods and explain how to avoid false matches.

What You’ll Need

An Excel workbook containing the list or range you want to check. These instructions are for Microsoft Excel for Microsoft 365 on Windows.
A clear idea of what counts as a duplicate: a repeated value in one column, or a repeated combination of values across a row. The simplest method below checks one selected range at a time.
Optional: an empty column beside your data if you want a formula result for each row, rather than color highlighting.

You do not need to sort the data first, and you do not need to remove anything. If the workbook is important, make a copy before editing or deleting records. Finding duplicates and removing them are different tasks; the methods here identify repeats without changing your source data.

Step-by-Step

Decide which cells to check. If names are in column A with a header in A1, select the data cells, such as A2:A100, rather than the header. If you want to check a smaller section, select only that section. Selecting a single column is usually the clearest way to find repeated entries in a list. A duplicate is a value that occurs at least twice within the range you selected, so the range determines the result.
Use Conditional Formatting for a quick visual check. With the cells selected, open Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog box, make sure Duplicate is selected in the first dropdown. Choose a highlighting style from the second dropdown, then click OK. Excel applies a color to cells whose values occur more than once in the selected range. This method is useful when you want to scan a list and do not need a separate result column.
Review the highlighted cells before taking action. Conditional Formatting generally highlights every occurrence of a repeated value, including the first one. For example, if “North” appears in A2, A8, and A15, all three cells can be highlighted. A highlight does not tell you which occurrence to keep, nor does it delete anything. Check the surrounding row details before deciding whether records are genuinely redundant.
Add a formula when you want a clear flag beside each entry. With values in A2:A100 and an empty result column starting at B2, enter this in B2, then fill it down through B100: =IF(A2="","",COUNTIF($A$2:$A$100,A2)>1). The formula returns TRUE when the value in column A appears more than once in A2:A100, and FALSE when it appears only once. The IF part leaves a blank result for an empty cell, so blank rows are not mistakenly labeled as duplicates.
Check a small example to confirm how the formula behaves. Suppose A2:A5 contains “Pear”, “Plum”, “Pear”, and “Kiwi”. With the formula above filled down, the results are TRUE, FALSE, TRUE, and FALSE. Both Pear entries are TRUE because each occurs twice in the checked range. If a value occurs three times, all three matching rows return TRUE. This matches the usual meaning of “find duplicates,” but it may not be what you want if you need to flag only later repeats.
Flag only the second and later occurrences if needed. With values in A2:A100 and the check formula entered in B2, use this formula and fill it down: =COUNTIF($A$2:A2,A2)>1. In the same Pear, Plum, Pear, Kiwi example, the results are FALSE, FALSE, TRUE, and FALSE. The first Pear is not flagged; the second is. The expanding range, $A$2:A2, grows as the formula is copied down, counting how many times the current value has appeared up to that row.
Filter the formula results to review matches. Select the header row and data, then choose Data > Filter. Open the filter dropdown in the result column and select TRUE to show flagged rows. If you used conditional formatting instead, you can sort or filter the original list as needed, but avoid sorting only one column: that can separate values from their related row information. When finished, clear the filter to see the full list again.

For Excel for Mac, the Conditional Formatting route is also available from the Home tab; the ribbon layout and dialog appearance can vary slightly by release. Excel for the web also includes conditional formatting, though some dialog details may differ. The COUNTIF formulas work in current desktop Excel for Windows and Mac, and in Excel for the web.

Common Mistakes

Selecting the wrong range: Excel only compares cells inside the selected range. If you select A2:A20 but a matching value is in A21, Excel will not treat that outside cell as part of the check. Conversely, selecting an entire column can make the check harder to review and may include unrelated data. Choose the actual data cells, including all rows that should be compared.

Including a header: If a heading happens to appear more than once, it may be highlighted or counted. Start with the first data row, not the column label. Make sure the formula’s fixed range begins at that same first data row.

Assuming the first highlighted item is the extra one: The standard duplicate rule marks all occurrences, not just copies after the first. Use the expanding COUNTIF formula if your workflow needs to preserve the first occurrence and identify only later ones.

Confusing repeated values with repeated records: Two rows can share a name but represent different people, orders, or events. A duplicate value in one column does not prove that the entire record is duplicated. Compare other fields, such as an ID, date, or address, before deleting a row.

Overlooking spaces and inconsistent entry: “River Road” and “River Road ” may look identical on screen but are different text values because one has a trailing space. Similarly, “A-104” and “A104” do not match. Excel’s COUNTIF matching is not case-sensitive, so entries that differ only by capitalization are normally treated as matches.

Tips

Keep the original values intact while you check them. A helper column makes the result transparent and easy to filter, while conditional formatting is faster for a one-time visual scan. You can use both methods together: highlight the range, then use a formula column to confirm which rows meet your chosen rule.

If the same list will receive new entries regularly, convert it to an Excel table with Insert > Table. Tables make it easier to manage growing lists and formulas. Still verify the formula’s checked range when data is added; a formula that explicitly ends at row 100 will not automatically include row 101 unless its range expands or you update it.

For a test across multiple fields, first decide what combination makes a record unique. For instance, a person’s name alone may repeat legitimately, while the combination of name and date of birth may be more meaningful. A simple single-column duplicate check cannot make that decision for you. Consider creating a helper key that combines the relevant fields, or checking the fields together before marking a record as an actual duplicate.

Use a consistent data type as well. A number stored as text may not behave like a numeric value in every comparison or calculation. If a result seems surprising, inspect the source cells, remove unintended spaces, and make sure dates and numbers are stored consistently. Always check a few known examples before relying on a large result set.

Troubleshooting

Nothing gets highlighted: Confirm that you selected the whole intended range and chose Duplicate in the Conditional Formatting dialog. Check that the values really match; hidden spaces, punctuation, or different characters can make entries distinct. Try copying one value and using Find to locate it elsewhere, or compare the cells directly.

Too many cells are highlighted: The selected range may include repeated headers, blank areas, or unrelated columns. Select just the data column you mean to check. If the same text appears in separate categories and should not count as a duplicate across categories, a single-column check is too broad; include the category in your review or build a check based on both fields.

The formula shows FALSE for something that looks repeated: Check for leading or trailing spaces and inconsistent punctuation. Also verify that the repeated cell is inside the formula’s fixed range. A formula using $A$2:$A$100 will not find a match located in A101. If you copied the formula, confirm that the first reference changes by row while the dollar signs keep the overall comparison range fixed.

The formula flags blank rows: Use the IF version shown in the steps, which returns an empty result when the current cell is empty. A plain COUNTIF formula can count blank cells as matching blanks, so it may mark empty rows when there is more than one blank in the checked range.

Highlighting remains after the data is corrected: Conditional formatting is a rule, not a permanent mark. If corrected cells still have a color, check whether another formatting rule or manually applied fill is responsible. To inspect rules, choose Home > Conditional Formatting > Manage Rules. Review the rule’s Applies to range and edit or remove the relevant rule if necessary.

Frequently Asked Questions

Does Excel highlight the first occurrence of a duplicate?
Yes. The Duplicate Values conditional formatting rule usually highlights all matching occurrences, including the first. Use the expanding COUNTIF formula in this guide to flag only the second and later occurrences.

Can I find duplicates without deleting them?
Yes. Conditional Formatting and the COUNTIF formulas only highlight or label cells. They do not remove data. Review the full records before choosing whether to delete anything.

Does Excel treat uppercase and lowercase as different values?
COUNTIF is not case-sensitive, and the standard duplicate highlighting rule generally treats text that differs only by capitalization as a match. If capitalization is important to your definition, use a case-sensitive method rather than relying on the basic duplicate rule.

Can I check duplicates across two columns?
Yes, but selecting both columns checks repeated cell values across the selected cells; it does not identify repeated pairs of values by row. To find duplicate records based on a two-column combination, define the pair as the key and compare both fields together, rather than treating either column alone as decisive.

How do I remove duplicates after I find them?
Make a copy first, then select the relevant data and choose Data > Remove Duplicates. In the dialog, choose which columns determine whether a row is a duplicate. This changes the data, so check the selected columns carefully and keep a backup if you may need the original records.

Why are two entries that look the same not counted as duplicates?
They may contain invisible spaces, different punctuation, or characters that look alike but are not identical. Check the cell contents in the formula bar and standardize the text before rerunning the check. Also confirm that both cells are inside the selected or formula comparison range.

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.