How to build random number generator in excel
In this article we’ll cover how to generate random number in excel using two main excel functions – Excel RAND & Excel RANDBETWEEN. You can use these 2 functions to create your own excel random number generator.
This is used if you ever want to just create mock data in excel, get a random data list for analysis or create random selection between values like choosing winners from list etc.
Using functions to generate Excel random numbers
Among various worksheet functions we’ll use the following two:
- Excel RAND Function: This will generate random numbers in excel without any repetition. But this only generates numbers between 0 & 1. We have to use RANK function with it to generate unique excel random numbers.
- Excel RANDBETWEEN Function: This will also generate random numbers but chances are the numbers would be repetitive.
Generate Excel random numbers using RANDBETWEEN in Excel
RANDBETWEEN functions creates random numbers between two specified numbers or a given range.
This is the function’s syntax:
Bottom is the smallest integer that this function will return.
Top is the largest integer this function will return.
When you run this function it’ll take the bottom integer, the top integer, the range of integers between them and act as a randomizer for excel for these integers values.
Remember when you’re generating random numbers in excel using this, it can also return the bottom & top value.
Let’s say we want to create excel random number generator which generates numbers between 1 & 20.
- Select the cell where we want the random numbers.
- Type =RANDBETWEEN(1,200)
- Hit Enter
- Drag the formula down as per your requirements.
With a RANDBETWEEN the chances of repetition are high. If we use =RANDBETWEEN(1,10) then we get such a result:
You can see it is generates duplicates as well, one way is to give a wide range and then remove duplicates in excel.
Also RANDBETWEEN in excel is a volatile function, it recalculates or runs every time whenever there is a change in worksheet.
To avoid this, once you have generated excel random numbers and are happy with it, just convert the formulas to text/values.
Generate random number excel using Excel RAND Function
This is also known as the random function in excel.
- Select the cells where you want to generate random number excel.
- Type =RAND()
- Press Enter.
- Now select all cells in column A (where you used Rand) and covert the formulas to text/values.
Generally there isn’t a high probability of repetitions but as a precaution I’ll recommend just remove duplicates from the column using either conditional formatting to highlight duplicates or using remove duplicates option.
You might also be wondering that how can we get generate random integers which don’t have a decimal values or which are not between 0 & 1. Then there are a few variations which you can use, as Excel RAND Function only generates integers between 0 & 1.
To generate random numbers between 10 & 50 using Excel Rand:
To generate random integers (not having any decimal places) between 10 & 50:
Also, just like RANDBETWEEN this random function in excel is also volatile meaning it changes value if anything gets changed in worksheet so make sure to convert formulas to text once you generate random number excel. If you get a repetitive values just remove duplicates from the column using either conditional formatting to highlight duplicates or using remove duplicates option.
That was all about how to generate random numbers in excel.
I hope this article was helpful to you and explained you can build random number generator excel and much more.
If you have any questions or tips, I’d love to hear them in the comment section below.