How to Find Repeated Numbers in Excel (5 Easy Methods)

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we end up having some repeated numbers in Excel. In this article, I will show how to find repeated numbers in Excel.

How to Find Repeated Numbers in Excel: 5 Easy Methods

This is the dataset for today’s article. We have some products and their order no along with the date. Some numbers are repeated here. So I will show you how to find these numbers.

1. Use COUNTIF Function to Find Repeated Numbers in Excel

First of all, I will show how to find repeated numbers in Excel using the COUNTIF function. For the repeated numbers, Excel will return TRUE as the output.

Steps:

• First of all, go to E5 and write down the formula

Here, Excel will find if the number in D5 is repeated in the range D5:D12. In case of repetition, Excel will return TRUE as output. Else the output will be FALSE.

• Then, press ENTER. Excel will return the output.

• Then, use the Fill Handle to AutoFill up to D12.

Read More: How to Find Duplicate Rows in Excel

2. Apply a Combination of IF and COUNTIF Functions to Find Repeated Numbers

Now, I will show another method. This time, I will use a combination of the IF and COUNTIF functions to find repeated numbers in Excel.

Steps:

• First of all, go to E5 and write down the following formula

• Then, press ENTER. Excel will return the output.

• After that, use Fill Handle to AutoFill up to D12.

Read More: How to Find Repeated Cells in Excel

3. Find Repeated Numbers Without Mentioning the First Case

In this section, I will show you how to find repeated numbers without mentioning the first case. This time, I will again use the IF and COUNTIF functions.

Steps:

• Go to E5 and write down the following formula

• This formula will determine whether the number in D5 is repeated or not in D5:D12. Since this is the first case of 220, Excel will avoid it and the output will be blank. Press ENTER to get the output.

• Now, use the Fill Handle to AutoFill up to D12.

Notice that Excel returns “Duplicate” in E6. That’s because this is the second case. As I mentioned earlier, for the first case, Excel has returned a blank.

Read More: How to Filter Duplicates in Excel

4. Apply Conditional Formatting to Find Repeated Numbers in Excel

Now, I will apply conditional formatting to find repeated numbers in Excel.

Steps:

• Select D5:D12.

• Then, go to the Home
• After that, select Conditional Formatting.
• Then, select Highlight Cell Rules.
• Finally, select Duplicate Values.

• A box will appear. Set Duplicate values to be formatted with Light Red Fill with Dark Red Text.
• Then, press OK.

• Excel will format the cells.

Read More: How to Compare Rows for Duplicates in Excel

5. Use Pivot Table to Find Repeated Numbers in Excel

You can also use the Pivot Table to find repeated numbers. I will show you how to do so step by step.

Steps:

• First of all, select the entire dataset.
• Then, go to the Insert
• After that, select PivotTable.

• A box will pop up. Select New Worksheet to create a pivot table in a new worksheet.
• Then, press OK.

• Excel will create a pivot table.

• After that, drag Order No to Rows and Values. Excel will by default make it Sum of Order No. You have to change that later.

• Then, Select the drop-down (see image). After that, select Value Field Settings.

• The Value Field Settings box will pop up. Select Count.
• After that, press OK.

• Excel will count the Order Number.

From the image above, you can easily find out that 125, 220, and 222 are repeated numbers.

Read More: Excel Find Duplicate Rows Based on Multiple Columns

Things to Remember

• Use absolute reference to lock a cell.
• If you have earlier versions of Excel, you may need to press CTRL+SHIFT+ENTER for array formulas.

Download Practice Workbook

Download this workbook and practice while going through this article.

Conclusion

In this article, I have demonstrated 5 effective methods how to find repeated numbers in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.

Related Articles

• How to Compare Two Excel Sheets for Duplicates
• How to Find Matching Values in Two Worksheets in Excel
• How to Find Duplicates in Excel and Copy to Another Sheet
• Excel VBA to Find Duplicate Values in Range
• How to Find Duplicates in a Column Using Excel VBA
• How to Use VBA Code to Find Duplicate Rows in Excel

<< Go Back to Find Duplicates in Excel | Duplicates in Excel | Learn Excel