Whether you are generating lottery numbers or sample data for a survey, random numbers are easy to create in Excel. While true randomness is difficult to simulate with software, Excel’s random functions come close and are a good option whenever you need to whip up a sequence of random numbers.
Generating Random Numbers in Excel
Random numbers can be generated to randomly select a winner from a list of names in a spreadsheet, choose respondents to participate in a survey from a large group, create sample data for testing or analysis, or run Monte Carlo simulations.
The Excel functions for generating random numbers are RAND, RANDBETWEEN, and RANDARRAY. Generating random numbers in Excel is as easy as typing in the appropriate function, adding parameters (if needed), and pressing Enter.
Random Functions in Excel
While the three random functions serve the same basic function, they work slightly differently and are best used in differing scenarios. RAND is best for continuous random numbers, RANDBETWEEN for whole numbers within defined ranges, and RANDARRAY for bulk random data generation. Let’s take a closer look.
Generate a Random Decimal Number
RAND is the simplest and easiest Excel random function for generating a random decimal number. It has a straightforward syntax and doesn’t take any parameters. To generate a random number using RAND, simply type =RAND() into an empty cell or the formula bar. This will produce a random decimal number between 0 and 1.
If you need a random number within a specific range, you can scale it by multiplying the RAND function with the upper limit of the range. For example, to generate a random number between 0 and 100, you would use =RAND() * 100. Similarly, if you need a random number between two specific values, say 10 and 50, the formula below will give you a random number within that range:
=RAND() * (50 - 10) + 10The RAND function is less likely to repeat numbers than RANDBETWEEN since it generates decimals, not whole numbers. You can use the RANK function in combination with the RAND function to sort values in a dataset.
Generate a Single Random Number
The RANDBETWEEN function offers a quick way to generate a single random number within a given range. It uses the syntax below:
RANDBETWEEN(bottom, top)RANDBETWEEN takes two arguments, bottom and top, with the bottom signifying the smallest number in the range and the top being the largest.
For example, to generate a random number between 1 and 100, you would use the formula below:
=RANDBETWEEN(1, 100)This function is handy when you need whole numbers, such as for creating random IDs or lottery numbers.
Unlike RAND, RANDBETWEEN returns a whole number and provides control over the upper and lower limits of the range.
All three random functions recalculate when the spreadsheet changes or is updated. To keep the generated values, select the cells, press Ctrl+C to copy, Ctrl+V to open the Paste Special dialog box, and pick Values from the list of options.
Generate an Array of Random Numbers
The RANDARRAY function allows you to generate a sequence of random numbers without using the fill handle or copying and pasting a formula. It allows you to fill a specified array of cells with random numbers within a certain range. It has the most complicated syntax, but it can be useful for creating large datasets in little time.
A RANDARRAY formula follows the syntax below:
=RANDARRAY([rows], [columns], [min], [max], [whole_number])You can specify the number of rows and columns to fill with random numbers, the lower and upper limit, and whether the function returns a whole number or a decimal value.
For example, to create a 5-row by 3-column array of random whole numbers between 50 and 100, you could use the formula below:
=RANDARRAY(5, 3, 50, 100, TRUE)The whole_number parameter can be set to FALSE or left undefined if you want decimal values instead of whole numbers.
The RANDARRAY function is particularly useful when you need to generate large datasets or random matrices for simulation, testing, or analysis. There are other array formulas in Excel you should have in your arsenal.
RANDARRAY is only available in Excel 2021 and Excel 365, and can’t be found in earlier versions of Excel.
Depending on your specific need, either function—RAND, RANDBETWEEN, or RANDARRAY—can be the perfect choice for generating the type of random numbers you require.
