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

Get FREE Advanced Excel Exercises with Solutions!

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 Suitable Ways)

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
`=COUNTIF(\$D\$5:\$D\$12,D5)>1` 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. ### 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
`=IF(COUNTIF(\$D\$5:\$D\$12,D5)>1,"Repeated","")` Formula Breakdown:

• COUNTIF(\$D\$5:\$D\$12,D5)>1 → This is the logical test. This is TRUE. 220 is present more than once in D5:D12.
• =IF(COUNTIF(\$D\$5:\$D\$12,D5)>1,”Repeated”,””) → This is the formula.
• Output: Repeated.
• Then, press ENTER. Excel will return the output. • After that, use Fill Handle to AutoFill up to D12. ### 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
`=IF(COUNTIF(\$D\$5:\$D5,D5)>1,"Duplicate","")` • 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.

### 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. ### 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.

## 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.

## 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. Please visit Exceldemy for more useful articles like this.

## Related Articles Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  