Find Duplicate Numbers in Excel: A Step-by-Step Guide

How-to basics · Updated November 20, 2026

To find duplicate numbers in Excel, select the cells you want to check and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated values, including the first occurrence. For more control, use a COUNTIF formula to flag duplicates, ignore blanks, or identify only later occurrences. Start with a copy of your data if you plan to remove anything.

What You’ll Need

  • A workbook open in Excel for Microsoft 365 on Windows, and a clearly identified range or column of numbers.
  • Optional: a spare column if you want a formula-based duplicate label, or permission to add formatting to the selected cells.
  • If the data is important, save a separate copy before sorting, filtering, or deleting rows.

Step-by-Step

  1. Identify the range to check. Decide what counts as a duplicate before applying a rule. To find repeated numbers in one list, select just that list, such as A2:A500, leaving the header out. If you select several columns together, Excel checks the values across the whole selected range; that can flag a number appearing in a different column even when the columns represent different things. If separate columns are separate lists, check each one on its own.
  2. Highlight all repeated values with the built-in rule. With the target cells selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a fill or font style, and click OK. Every value that occurs more than once in the selected range is highlighted, including its first appearance. For example, if the cells contain 12, 7, 12, 0, -3, 7, and 9, Excel highlights both 12s and both 7s; zero and the negative number are checked like other numeric values.
  3. Check the highlighted cells before changing the data. Read the values in context and confirm that the range is right. Conditional formatting marks matching values; it does not delete them or decide whether two rows are duplicates as a whole. If two customers share the same account number, the number is repeated even if other information in their rows differs. Keep the distinction in mind before treating a highlight as an error.
  4. Use a formula rule when you need to ignore blanks. A custom conditional-formatting formula lets you add a blank check. With numbers in A2:A9 and the range A2:A9 selected, use this formula as the conditional-formatting rule; it returns TRUE for each nonblank number that appears at least twice, and FALSE for unique values and blank cells:
    =AND(A2<>"",COUNTIF($A$2:$A$9,A2)>1)
    To add it, select the range, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose “Use a formula to determine which cells to format,” enter the formula, click Format to choose the highlight, then click OK. The reference A2 should be the top-left cell of the selected range. Excel adjusts that relative reference for each cell, while the dollar signs keep the counted range fixed. If your data begins in a different row or column, update both the range and the first-cell reference.
  5. Add a helper column to label duplicates. A helper column is useful when you want a visible text result that you can filter or review. With numbers in A2:A9 and the formula entered in B2 then filled down through B9, this formula labels only the second and later occurrences as Duplicate, labels the first as First occurrence, and leaves a blank source cell blank:
    =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence"))
    For the example list 12, 7, 12, 0, -3, 7, 9, blank, B2 and B3 show First occurrence, B4 shows Duplicate, B5 and B6 show First occurrence, B7 shows Duplicate, B8 shows First occurrence, and B9 stays blank. The expanding range $A$2:A2 grows as you fill down, so each row is compared with the values above it and itself. To fill the formula, select B2 and drag the small square at the bottom-right corner of the cell down to B9, or copy B2 and paste into B3:B9.
  6. Filter the results if you need a shorter review list. If you used a helper column, select your data and choose Data > Filter. Use the dropdown in the helper-column header to show only Duplicate. To filter a built-in highlight, add filters to the relevant table or range and use that column’s dropdown, then choose Filter by Color and the fill color used for duplicates. Review the filtered rows before deleting anything. On Mac, the equivalent ribbon commands are also available; if the ribbon layout differs, use the Data and Home tabs rather than relying on a Windows shortcut. Excel for the web has conditional formatting and filters too, though dialog names or placement can vary slightly.
  7. Remove duplicates only if that is your goal. Finding duplicates and removing them are different tasks. If you intentionally want to keep one copy of each number, first make a backup, select the correct data, and use Data > Remove Duplicates. Check the selected columns carefully: this command removes rows based on the columns you specify, not merely the cell color. If you only want to mark or inspect repeats, do not use Remove Duplicates.

Common Mistakes

  • Selecting headers or unrelated columns. A header can be treated as a value, and checking multiple unrelated columns together can produce misleading highlights. Select only the data range that represents one list, or check each list separately.
  • Assuming a highlight means a whole row is duplicated. The built-in rule compares cell values in the selection. It does not compare complete records. If you need to find duplicate rows based on several fields, you must compare those fields together or create a combined key; matching one number alone does not prove the records are identical.
  • Confusing repeated values with repeated entries after the first. The Duplicate Values rule highlights every occurrence, including the first. The helper-column formula above labels only later occurrences. Choose the method that matches whether you want to see all members of a duplicate group or just the extra entries.
  • Deleting rows before checking what they contain. Two rows with the same number may contain different dates, names, or other information. Make a copy, confirm which record should remain, and only then remove rows.
  • Expecting displayed rounding to define a match. Two numbers may look the same because cells display only a few decimal places while their stored values differ. Conversely, formatting differences such as currency symbols or decimal places do not necessarily make different underlying numbers. Decide whether you need exact values or values rounded to a chosen precision.

Tips

  • Use a table for a list that will grow. Select the list and choose Insert > Table, confirming whether it has headers. Tables make it easier to manage filters and keep a list organized as you add rows. When adding new data, check that the conditional-formatting range includes the new rows; a table or an intentionally extended range can reduce the need to reset it.
  • Use a consistent numeric format. Numbers imported from text files may include text values that look numeric, extra spaces, or different decimal precision. If results seem inconsistent, convert the values to genuine numbers and standardize precision before searching. Do not convert data blindly if leading zeros are meaningful identifiers; codes such as 00125 may need to remain text.
  • Keep the original data intact during review. Conditional formatting and helper labels are reversible and let you inspect matches without changing the source values. Save a copy before sorting or removing rows, and record the criteria you used if someone else needs to verify the result.
  • Use a descriptive highlight. Pick a fill color with enough contrast to read the numbers, especially if the sheet already uses colors to communicate status. You can change or remove the rule later through Home > Conditional Formatting > Manage Rules.
  • Separate the search question from the cleanup decision. A repeated invoice number, product code, or measurement can mean different things in different sheets. First identify the matches, then confirm what the values represent and whether repetition is actually an error.

Troubleshooting

Nothing is highlighted. Confirm that the selected range includes the data and that the Duplicate Values dialog is set to Duplicate rather than Unique. Check whether the rule applies to the cells you selected: open Home > Conditional Formatting > Manage Rules and inspect the “Applies to” range. If the list starts on row 2, make sure the rule covers row 2 onward rather than only the header.

Values that look identical are not being treated as duplicates. Check the underlying values in the formula bar and increase the displayed decimal places temporarily from Home > Increase Decimal. Imported data may mix numbers and text, or values may differ in hidden decimal places. Normalize the data only after confirming that doing so will not damage identifiers or meaningful precision.

Unique values are highlighted unexpectedly. Look for an overly broad selection, an existing conditional-formatting rule, or a rule whose “Applies to” range includes extra cells. Open Manage Rules and review the rules for the current selection. If using the formula method, confirm that the top-left cell reference matches the first cell in the selected range and that the counted range is fixed with dollar signs.

Blank cells are marked or counted in an unwanted way. Use the custom rule with the A2<>"" check, adjusting A2 to the top-left cell of your selection. For a helper column, the outer IF returns an empty string for an empty source cell. Also check for cells that appear blank but contain spaces or formulas returning an empty string; those are not always equivalent to truly empty cells.

The helper column labels every row the same way or shows an error. Make sure the formula starts in the row matching the first data value, then fill it down without changing the fixed start reference $A$2. Confirm that the source values are in column A as assumed. If your Excel uses a regional list separator other than a comma, Excel may require semicolons when entering formulas; use the separator expected by your installation.

Frequently Asked Questions

Does Excel highlight the first occurrence too?
Yes. The built-in Duplicate Values rule highlights every cell whose value appears more than once, including the first copy. Use a running COUNTIF helper formula if you want to label only later occurrences.

Can I find duplicates in only one column?
Yes. Select the data cells in that column, excluding the header, before applying the rule. This prevents values in other fields from affecting the result.

Can Excel find duplicate numbers without a formula?
Yes. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values is the fastest built-in method. A formula is helpful when you need to ignore blanks, label repeats, or customize exactly what counts.

Does the method work with zero and negative numbers?
Yes. Zero and negative numeric values can be duplicates just like positive values. The rule compares the values in the selected range; it does not exclude a number because of its sign.

How do I remove duplicate numbers after finding them?
Back up the data, select the correct range, and use Data > Remove Duplicates. Review the columns selected in the dialog first, because Excel removes rows according to those columns and keeps one copy of matching records.

Why are two numbers with the same display not duplicates?
They may have different stored decimal values that are rounded for display, or one may be stored as text. Check the underlying values and data types, then decide whether to normalize the values or retain the distinction.

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.