Generate Random Numbers in Excel | No Duplicates
If you want to pick participants randomly from a given list or want to get a list of random numbers then this article is for you.
After reading this article you’ll know how to generate random numbers in excel using Random function in excel along with using Randbetween in excel. Let’s get started.
Table of Contents
Using excel Rand function to generate random numbers in Excel
- Select the range of cells. This is where the random numbers will be generated.
- Type the random function in excel =RAND() and hit CTRL+SHIFT+ENTER.
- Now you can see the random numbers in excel.
- Now copy the cell range and paste in another column but as values. So that you can use the random numbers. The reason behind this is listed below under notes.
- Now if you’re happy with the random numbers in excel being integers, great. But in case you want whole numbers, then we’ll use a slightly different formula.
Type =INT(RAND()*(100-10)+10)
This will first generate random numbers between 10 & 100 and then convert them into whole numbers. - You have successfully generated random numbers in excel.
Note: Excel Rand function is dynamic, which means it re-runs whenever there is a change in the sheet. So let’s say you enter a value in another cell the formula will re-run and generate random numbers again. To avoid this you should copy and paste the numbers as values so they remain static or constant.
Tip: Once you have used excel random number generator you should check for duplicate values. You can use conditional formatting to highlight duplicates or directly remove them.
Using Randbetween to generate random numbers in excel
The best part about randbetween formula is that it generates random numbers between 2 numbers. Let’s see how we can create excel random number generator using this function.
- Select the cell.
- Type in the formula =RANDBETWEEN(1,5) and hit Enter.
- Drag the formula and you’ll see something like this.
- Now because the range we gave was small the random numbers are getting repeated. To avoid this always use a wider range as compared to the cell range. So now type =RANDBETWEEN(1,100)
Now you can see there are no more duplicates.
Note: Excel Randbetween is also dynamic just like Rand function. So make sure after generating the random numbers you copy and paste them as values. Also, always check for duplicates as a precaution.
Conclusion
We hope you liked our tutorial on generate random number excel.
In case you have any follow up questions or want to suggest other topics for us to cover, make sure to add them in the comments below. Take care.
Advanced Excel Course
Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.
- 10+Hrs of Learning Content
- 50+ Practical Exercises
- Certification
- Doubt Resolution