Generate random numbers in Excel with RAND, RANDBETWEEN and RANDARRAY

Creating random numbers in Excel can be useful for a number of reasons.

From a data or business context random numbers can help create a dataset to test models, create random samples for statistical analysis or even run simulations to help forecast outcomes under uncertainty.

If you do need to generate random numbers in Excel then instead of having to choose the numbers that you will need you can use the RAND, RANDBETWEEN and RANDARRAY functions to generate random numbers for you.

Each function works differently and is covered below.

Download File

If you would like to follow along download the attachment below.

Generate random numbers in Excel with RAND, RANDBETWEEN and RANDARRAY.xlsx

Download XLSX • 12KB

Understanding volatility with random number functions

All random functions in Excel are known as volatile functions.

This means that their output constantly changes whenever Excel refreshes the worksheet.

In practice this means that every time you enter a value or formula in another cell the random output generated from your random functions will change.

This can be useful if you are running a simulation, but if you want to generate a random number or set of numbers and just keep the output then you will need to paste the output as a value.

To do this select the cell or cells that contain the random numbers and press Ctrl + C to copy, and then press Ctrl + Shift + V to paste the output as a value.

An example of how this could work is outlined below:

The RAND function has generated 0.73884 as an output in cell A1.

Every time that an action is taken in Excel e.g. a value or formula is typed in another cell, then the RAND function in A1 will generate a new random number.

To avoid this happening and to always see 0.73884 press Ctrl + C when selecting A1 to copy the cell. You will see a moving dashed green line around the cell indicating that it has been copied.

Once it has been copied press Ctrl + Shift + V and this will paste the number as a value.

The RAND formula will no longer show in the formula bar, just the number that it generated, and it will not change when any updates are made to the spreadsheet.

The same process of copying and pasting as a value to remove volatility can be done with all random functions.

Using the RAND function

The RAND function generates a random decimal number greater than or equal to 0 and less than 1.

Entering RAND is quite simple as it doesn’t require any parameters, just type:

=RAND()

If you want a random decimal number between 0 and 100 instead of between 0 and 1 than you can just multiply the RAND by 100.

Using the RANDBETWEEN function

If you want to generate random whole numbers between a range, then you can use RANDBETWEEN function.

The syntax for RANDBETWEEN is:

=RANDBETWEEN(bottom, top)

• bottom – The lowest possible number you want to be generated

• top – The highest possible number you want to be generated

The output is inclusive of both the top and bottom number, so if your top number is 100 and your bottom number is 1 it could generate 1 or 100 as well as all numbers in between.

The function also only generates whole numbers between the top and bottom values, it will not generate a decimal number.

Using RANDARRAY

RANDARRAY is a dynamic array function and can return random numbers that spill over a range of cells, rather than just a single output.

The syntax for RANDARRAY is:

=RANDARRAY([rows], [columns], [min], [max], [whole_number])

• rows: how many rows of random numbers

• columns: how many columns of random numbers

• min (optional): smallest value

• max (optional): largest value

• whole_number (optional): TRUE for integers, FALSE (or omitted) for decimals

To get random numbers a value only needs to be entered in the rows or columns parameters.

If you leave the min, max and whole_number options blank the function will assume the following defaults:

• The min is 0

• The max is 1

• The whole_number will be FALSE, so the random numbers generated will be decimal values

In the example below only the rows parameter has a value in it, 5.

As a dynamic array the function has returned 5 rows worth of random decimal numbers between 0 and 1.

As there is a dynamic output when you click on a value in the spilled array you will see a blue box around the cells the function has spilled into.

If you want to make any changes to the function you will only be able to make changes in the first cell that you entered the function.

You can tell which cell you are able to edit by looking at the formula bar, if you click on the first cell in the array the colour of the text in the formula bar will be black, all other values will be grey, and you will not be able to edit these.

If you only want decimal numbers from 0 to 1 to spill across columns and not rows leave the rows parameter blank and only enter a number in the columns parameter.

When all parameters are filled in you have more control over the output.

In the example below the RANDARRAY function has spilled across four rows, three columns, and has generated random number between 1 and 100 as whole numbers.

Conclusion

If you want to generate random numbers in Excel you can use three different functions to achieve this.

The RAND function will generate a decimal number in a single cell.

The RANDBETWEEN function will generate a whole number between two specified numbers in a single cell.

The RANDARRAY function will generate random numbers across multiple cells, with the option to specify how many rows or columns, as well as a high and low number and whether or not you want the output to be a whole number or decimal number.

All functions are volatile, so will update constantly as changes are made to the workbook.

If you do not want the values they generate to update then you will need to copy the output and paste as a value.