Akib Bin Rashid
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.
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.
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","")
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.
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.
- 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
- How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)
- How to Repeat Cell Value X Times in Excel (6 Easy Methods)
- [Fixed!] Excel Rows to Repeat at Top Not Working (4 Solutions)
- How to Repeat Rows in Excel When Printing (3 Effective Ways)
- How to Repeat Column Headings on Each Page in Excel (3 Ways)
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 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.
- 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: 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
- How to Repeat Rows in Excel at Bottom (5 Easy Ways)
- How to Make a Pattern Repeat in Excel (8 Easy Ways)
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
- How to Repeat Header Row When Scrolling in Excel (6 Ways)
- Repeat Rows at Top in Excel (3 Suitable Ways)
- How to Repeat Rows at Top of Specific Pages in Excel
