Random numbers in Excel are easier than most people think, and you have three main tools to choose from: RAND() for decimals between 0 and 1, RANDBETWEEN() for whole numbers in a range, and RANDARRAY() for filling whole blocks of cells at once. This guide shows you how to make random numbers in Excel for any range you want, force whole numbers only or roll virtual dice
How to make random numbers in Excel
Typing the =rand() function into a cell in an Excel spreadsheet will return a random number that is greater than or equal to 0 and strictly less than 1, with up to 9 numbers after the decimal place (e.g. 0.037595172 or 0.999999999).
If you then press F9 (refresh), a new random number will take the place of the old one. This is because F9 re-calculates the formulas on all open worksheets based on the current data, including all random numbers.
OK … We Really Mean “Pseudo-Random” Numbers
The =rand() function in Excel cannot be easily analyzed to produce a predictable pattern, but it is not a truly random process. To be perfectly correct, the numbers generated are in fact approximations of actual random numbers known as “pseudo-random” numbers.
Computers are by definition “deterministic” – when given a particular input, they will always produce the same correct output. This is an inherent problem when it comes to producing a purely random output, and so algorithms have been developed to simulate random behavior.
For people who don’t rely on a heavy use of random numbers, the =rand() function in Excel should be more than enough.
In some heavy-use situations however, it won’t. If you are serious about your need for a very good random number generator, then you may want to look at a number of third-party alternatives.
Note that the =rand() function does not try to avoid repeating a number. Duplication is possible but very unlikely.
Naturally, this change introduced a bug that allowed for negative random numbers! We will talk about this bug below.
Three Random number functions in Excel
Modern Excel has three different random number functions
RAND()
Returns a random decimal number from 0 up to, but not including, 1. It has no arguments. Example: =RAND()
This function has been in Excel for decades and many versions.
RANDBETWEEN()
Returns a random whole number/integer between two numbers you specify, inclusive.
RANDBETWEEN(bottom, top)
Example: =RANDBETWEEN(1,100)
Introduced as full function in Excel 2007. Excel 2003 had it as part of the Analysis ToolPak add-in.
RANDARRAY()
Returns a dynamic array of random numbers. You can choose the array size, minimum and maximum values, and whether to return decimals or whole numbers.
RANDARRAY([rows], [columns], [min], [max], [whole_number])
Example: =RANDARRAY(10,1,1,100,TRUE)
A modern dynamic array function available in Microsoft 365 and Office 2021 & later.
Random numbers between a range of values
So far we know about random numbers between 0 and 1, but in many cases we want larger random numbers. If you want to increase the range to find numbers between 0 and 50, use the =rand()*50 formula (where the * character represents the multiplication sign). This acts to “scale” the random numbers between a range of 0 and 49.999999999.
What if you want a random number in a certain range – say between 20 and 70?
RandBetween() is the easiest way to do this e.g. RandBetween(20,70).
The old fashioned method is, =rand()*(y-x)+x where x is the smallest number in the range and y is the largest number in the range. In our example, x=20 and y=70.
When you plug in the values to our formula it becomes =rand()*(70-20)+20, which in turn becomes =rand()*(50) + 20. We know from above that =rand()*50 will produce a random number between 0 and 50. If we then add 20 to the result, all we are doing is “shifting” the range of possible numbers by 20. Hence, it produces a random number between 20 and 70 (or 20 and 69.999999999 if you want to be more precise).
Whole random numbers only
If you only want whole numbers (integers), then either RandBetween() or RandArray() can do that.
Back in the past we used the rand() and int() functions to obtain only the integer part of the random number. For example, typing in =int(rand()*100) would generate a random integer between 0 and 99 inclusive.
Similarly if you wanted whole numbers between 1 and 100 inclusive, then simply shift the range by adding 1 as follows: =int(rand()*100) + 1.
Random roll of the dice
To emulate the roll of a standard dice use
=int(rand()*6) + 1
or
=RandBetween(1,6)For a ‘Dungeons and Dragons’ ten-sided dice (0 to 9) use
=int(rand()*10)
or
=RandBetween(0,9) Put the formula in two cells if you want a percentile (00 to 99) roll.
Lots of Random Numbers
Excel 365 can produce many random numbers from a single RandArray() formula with options to set a minimum-maximum range plus integers only.
Negative Random Numbers in Excel 2003
Microsoft’s attempt to improve the =rand() function for Excel 2003 was originally flawed as it unwittingly introduced occasional negative random numbers.
Office Watch broke the news of the Excel 2003 RAND() bug at the time.
I love this quote from that issue:
“The company says that ‘The new generator passes all standard tests of randomness.’ which is great but a pity it wasn’t tested for plain and simple accuracy.”
Ouch …
This problem was fixed in the Microsoft Excel 2003 Hotfix Package , so unless you are still running an un-patched version of Excel 2003, you can be assured that your random numbers will always be positive.
Truly random numbers are possible in Excel
RandArray, Excel’s best way to get random numbers
Sabotaging Excel’s Random Numbers
Two ways to make fake data lists for Excel
Excel SUMIF Function: Add Up Only the Numbers You Want
Best AI for Spreadsheets: Why Claude and ChatGPT Beat Gemini and Copilot
