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.


Download Practice Workbook

Download this workbook and practice while going through this article.


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.

how to find repeated numbers in Excel


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.

how to find repeated numbers in excel

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

Read More: How to Repeat Number Pattern in Excel (5 Methods)


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","")

how to find repeated numbers in excel

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.

how to find repeated numbers in excel

Read More: How to Repeat Formula Pattern in Excel (Easiest 8 ways)


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.

how to find repeated numbers in excel

  • 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: Repeat Text in Excel Automatically (5 Easiest Ways)


Similar Readings


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.

how to find repeated numbers in excel

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

how to find repeated numbers in excel

  • Excel will format the cells.

Read More: How to Autofill in Excel with Repeated Sequential Numbers


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.

how to find repeated numbers in excel

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

how to find repeated numbers in excel

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

how to find repeated numbers in excel

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

  • Excel will count the Order Number.

how to find repeated numbers in excel

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

Read More: How to Repeat Cell Values in Excel (6 Quick Methods)


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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo