How to generate random numbers in Excel

Whether you're generating lottery numbers or sample data for a survey, generating random numbers in Excel is easy. While true randomness is hard to simulate with software, Excel's random number generator functions are a good choice whenever you need to generate a series of random numbers.

Generate random numbers in Excel

You can generate random numbers to pick winners from a list of names in a spreadsheet, select survey respondents from a large group, generate sample data for testing, analysis, or run Monte Carlo simulations.

The Excel functions for generating random numbers are RAND, RANDBETWEEN, and RANDARRAY. Generating random numbers in Excel is easy by entering the appropriate function, adding parameters (if needed), and pressing Enter .

Random number generator functions in Excel

Although the three random number generators have the same basic function, they work slightly differently and are best used in different situations. RAND is best for continuous random numbers, RANDBETWEEN is best for integers within a given range, and RANDARRAY is for generating random data in batches. Let's take a closer look.

Generate random decimals

RAND is the simplest and easiest Excel random number generator function to generate random decimal numbers. It has a simple syntax and does not use any parameters. To generate random numbers using RAND, simply enter =RAND() in an empty cell or formula bar. This will generate a random decimal number between 0 and 1.

How to generate random numbers in Excel Picture 1

If you need a random number within a specific range, you can scale it by multiplying the RAND function by 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 10 and 50, the formula below will give you a random number within that range:

=RAND() * (50 - 10) + 10

The RAND function is less likely to produce duplicate numbers than RANDBETWEEN because it produces decimals, not whole numbers. You can use the RANK function in conjunction with the RAND function to sort values ​​in a data set.

Generate a single random number

The RANDBETWEEN function provides a quick way to generate a single random number within a given range. It uses the following syntax:

RANDBETWEEN(bottom, top)

RANDBETWEEN takes two arguments, bottom and top , with bottom representing the smallest number in the range and top being the largest number.

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 integers, such as to generate random IDs or lottery numbers.

How to generate random numbers in Excel Picture 2

Unlike RAND, RANDBETWEEN returns an integer and provides control over the upper and lower bounds of the range.

Note : All three random number generators recalculate when the spreadsheet changes or is updated. To preserve the generated values, select the cells, press Ctrl+C to copy, Ctrl+V to open the Paste Special dialog box , and select Values ​​from the list of options.

Generate a random array of numbers

The RANDARRAY function allows you to generate a series of random numbers without using a fill handle or copying and pasting a formula. This function allows you to fill a specified array of cells with random numbers within a given range. This function has the most complex syntax, but can be useful for generating large data sets in a short amount of time.

How to generate random numbers in Excel Picture 3

The 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 limits, and whether the function returns an integer or a decimal value.

For example, to generate a 5 row x 3 column array of random integers 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 undefined if you want a decimal value instead of an integer.

The RANDARRAY function is especially useful when you need to generate large data sets or random matrices for simulation, testing, or analysis. There are other array formulas in Excel that you should have in your arsenal.

Note : RANDARRAY is only available in Excel 2021 and Excel 365 and cannot be found in earlier versions of Excel.

Depending on your specific needs, one of two functions - RAND, RANDBETWEEN, or RANDARRAY - may be the perfect choice to generate the type of random numbers you require.

David Pac

Update 08 October 2024