If you want to build a Random Number Generator in Excel, there are two functions to generate an Excel Random Number. The two functions are called RAND() and RANDBETWEEN().
The first function is generating a random number with decimals between 0 and 1. The second function is generating a random integer number (a number without decimals) between 2 numbers passed as parameters.
RAND() – random number between 0 and 1
The RAND() function generates a random number between 0 and 1. The number generated has decimals.
The Excel random number is recalculated with every refresh of the worksheet. (use F9 to recalculate all formulas).
In the example below, a random number generated using the function RAND() is 0.525861
For larger numbers, multiply the number with 10, 100, 1000 or any larger number
If you want a number between 0 and 10, use the function =RAND()*10. The resulting number will have decimals. If you want to round the number to an integer use the function ROUND like this.
= ROUND(RAND()*10) – this generates a random number between 0 and 10
RANDBETWEEN() – random numbers between limits
Using the function RAND() will always use zero as the bottom limit. RAND() can only generate a random number between 0 and 1, or 0 and 10, or 0 and 100 etc. If you want to change the lower limit and set the upper limit as well, use the function RANDBETWEEN()
RANDBETWEEN() generates a random number between a bottom limit number and a top limit number.
The random numbers generated, unlike the RAND() function, are integers, no decimals.
RAND() and RANDBETWEEN() for random words or letters
A great usage example for random number functions, aside from generating numbers, is for picking up random words in a list using a vlookup function.
In the example below, you can generate a number from 1 to 5
Using a VLOOKUP function, you can pull the corresponding word from the list.
NOTE: Use the functions RAND() and RANDBETWEEN() to generate random letters using the vlookup function. That can be useful if you are trying to generate a word search game.
