How to Identify Duplicate Numbers in Excel

To find duplicate numbers in Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated values, including text if it is in the selection. If you want to highlight only cells that contain actual numbers, use the formula-based rule below. These methods mark duplicates without deleting or changing your data.

What You’ll Need

An Excel workbook with the numbers you want to check. The steps use Microsoft Excel for Microsoft 365 on Windows.
A clear idea of the range to check, such as A2:A100. Include the data cells but not a header such as “Amount.”
Optional: an empty column beside your data if you want a written duplicate label instead of colored highlighting.

On Excel for Mac, the Conditional Formatting commands are also on the Home tab, though dialog layouts can differ slightly. The formula approach works in current desktop versions of Excel, including Microsoft 365.

Step-by-Step

Check the data range. Find the column or group of cells that contains the numbers. Make sure the range does not include a title, total, or unrelated values. For example, if the header is in A1 and the numbers run from A2 through A100, use A2:A100. If you want to check two or more columns together, select the full rectangular range; Excel will look for repeated values across that selection.
Select the cells to inspect. Click the first data cell, then drag to the last cell. You can also click the Name Box to the left of the formula bar, type a range such as A2:A100, and press Enter. Selecting only the relevant cells helps prevent a header or an unrelated value from being reported as a duplicate.
Apply the built-in duplicate highlight. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style, and click OK. Excel colors every cell whose value appears more than once in the selected range. This is the fastest method when you want a visual check and do not need to distinguish numeric values from text.
Use a formula rule to check numbers only, if needed. If the selection might include text, select the numeric data range again and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With actual numbers in A2:A100 and A2 as the active, top cell of the selected range, enter =AND(ISNUMBER(A2),SUMPRODUCT(($A$2:$A$100=A2)*ISNUMBER($A$2:$A$100))>1) in the formula box. Choose Format, select a fill or font style, and click OK, then click OK again if the rule dialog remains open. The rule checks that the current cell is numeric and that its value occurs in more than one numeric cell in the range. The dollar signs keep the checked range fixed while the reference A2 adjusts for each cell.
Confirm what the highlighting means. The built-in rule and the number-only formula highlight all occurrences of a repeated value, not just the second and later appearances. For example, if 42 appears in A2, A8, and A15, all three cells are marked. A number that appears only once remains unmarked. This lets you review the complete set of matching cells before deciding what to do.
Optionally add a duplicate label in a helper column. With numbers in A2:A100 and an empty helper column B, enter the following formula in B2, then fill it down through B100: =IF(AND(ISNUMBER(A2),SUMPRODUCT(($A$2:$A$100=A2)*ISNUMBER($A$2:$A$100))>1),"Duplicate",""). Each repeated numeric value receives the word Duplicate; unique numbers and nonnumeric cells return a blank. This gives you a filterable label while leaving the original values untouched. To fill down, drag the fill handle from B2 to B100 or copy B2 and paste into B3:B100.
Review the matches before taking action. Sort or filter the data only after checking that the highlighted values are genuinely duplicates. Two records with the same number may still be valid if that number is an identifier reused in different categories or periods. If you need to remove repeated rows, make a copy of the worksheet first and confirm which record should be kept; highlighting itself does not remove anything.

The formula rule is especially useful when a column contains a mixture of numbers, words, and numeric-looking text. For example, the value 42 entered as a number is different in purpose from an entry stored as text, even though both may look alike on the worksheet. The ISNUMBER check restricts the formula rule to cells Excel recognizes as numbers.

Common Mistakes

Selecting the header or extra cells. If the selection includes a label or data from another section, Excel checks those cells too. Start with the first data row and include only the intended area.
Assuming the built-in rule checks numbers only. Duplicate Values can flag repeated text as well as repeated numbers. Use the formula-based rule when the range contains mixed data types and only numeric duplicates matter.
Confusing displayed values with stored values. Two cells may both display 10.0 because of number formatting, even if one stores 10 and the other stores 10.04. The formula compares the underlying values, not just the rounded display. Increase decimal places or inspect the formula bar to investigate.
Treating every repeated identifier as an error. Repeated account, product, or transaction numbers may be intentional in a data set. Decide what makes a record unique before deleting rows or editing values.
Forgetting that dates are numbers in Excel. Excel stores dates as serial numbers. A number-only check can therefore include dates. If a range mixes dates and other numeric values, separate the data or apply the rule only to the relevant numeric section.

Tips

Use a consistent range. When adding rows later, extend the conditional formatting rule to include them. To inspect or adjust it, choose Home > Conditional Formatting > Manage Rules, select the rule, and check the Applies to range.
Choose a noticeable but readable format. A pale fill is often easier to review than a strong font or fill color, especially in a long list. Keep the original number visible so you can compare neighboring records.
Sort a copy to group repeats. If the data is safe to reorder, copy the worksheet and sort the relevant column from smallest to largest. Matching numbers will sit together, making it easier to inspect surrounding information. Do not sort a single column on its own if other columns belong to the same rows.
Check whether “duplicate” means the whole row. A repeated number in one column does not necessarily mean the entire record is duplicated. Compare the other fields before removing a row; two people or transactions can share one value while differing elsewhere.
Keep a record of the original data. Conditional formatting is reversible, but deleting rows or replacing values can be harder to undo later. Save a copy or use a duplicate worksheet before cleaning a data set.

Troubleshooting

No cells are highlighted: Confirm that the selection and the rule’s Applies to range cover the cells you are checking. For the formula rule, the first reference must match the top-left cell of the selected range. For example, selecting A2:A100 requires A2 in the formula; starting the range at A3 while leaving A2 in the formula can produce incorrect results. Also verify that the cells contain numeric values rather than numbers stored as text.

Text or blanks appear to be involved: The built-in Duplicate Values rule can highlight repeated text. Replace it with the formula rule if you only want actual numbers. If cells contain formulas that return an empty string, inspect the source formulas and the conditional formatting range; do not assume a visually blank cell is a truly empty cell.

Values that look identical do not match: Check for hidden decimal places, leading or trailing spaces in text, or numbers stored in different formats. Click each cell and inspect the formula bar, and use Home > Number to review how values are displayed. If entries are numeric text, convert them to numbers consistently before relying on a number-only check. You can often select the affected cells and use the warning icon’s Convert to Number option when Excel displays it; otherwise, use a suitable conversion method and verify the results.

Unexpected cells change color after editing: Conditional formatting updates automatically when values change. Open Home > Conditional Formatting > Manage Rules and check the selected rule, its formula, and Applies to range. If another rule also applies to the cells, review the rule list and order to identify overlapping formats.

The worksheet becomes slow: Keep the formula rule’s range limited to the actual data instead of applying it to an entire column in a large workbook. A rule that repeatedly checks many cells can take longer to recalculate over a very large range. Expand the range when you add data, or use the built-in duplicate rule if checking text as well as numbers is acceptable.

Frequently Asked Questions

Does Excel highlight every occurrence or only later repeats?
The Duplicate Values rule highlights every occurrence of a value that appears more than once in the selected range. If a number appears three times, all three cells are highlighted.

Can I find duplicates without changing the cell colors?
Yes. Use the helper-column formula above to display Duplicate beside repeated numeric values. You can also use the built-in rule and later remove its formatting through Home > Conditional Formatting > Clear Rules, choosing the option that applies to the selected cells or the whole sheet.

Can I check duplicates across multiple columns?
Yes. Select the full range before applying the built-in rule to find repeated values anywhere in that selection. That checks individual cell values, not whether entire rows are identical. For row-level duplicates, define which columns together make a record unique and compare those fields.

Will the formula find numbers stored as text?
The number-only formula intentionally excludes text because ISNUMBER returns FALSE for a number stored as text. Convert those entries to real numbers if they should be included, or use the built-in Duplicate Values rule when you want Excel to check repeated text too.

Does this work in Excel for Mac?
Yes. Excel for Microsoft 365 on Mac has conditional formatting and supports the formulas shown. The ribbon and dialog layout may look a little different, but use the Home tab’s Conditional Formatting commands. The steps above give the Windows path.

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.