Random Numbers

 

Rand  |  RandBetween  |  RandArray  |  Random Number Generator

Excel has two very useful functions when it comes to generating random numbers. RAND and RANDBETWEEN.

Rand

The RAND function generates a random decimal number between 0 and 1.

1. Select cell A1.

2. Type =RAND() and press Enter. The RAND function takes no arguments.

RAND function

3. To generate a list of random numbers, select cell A1, click on the lower right corner of cell A1 and drag it down.

List of Random Numbers

Note that cell A1 has changed. That is because random numbers change every time a cell on the sheet is calculated.

4. If you want random numbers that remain constant once generated, simply copy the random numbers and paste them as values.

Paste As Values

5. Select cell C1 and look at the formula bar. This cell holds a value now and not the RAND function.

Values

RandBetween

The RANDBETWEEN function generates a random whole number between two boundaries.

1. Select cell A1.

2. Type =RANDBETWEEN(50,75) and press Enter.

RANDBETWEEN function

3. If you want to generate random decimal numbers between 50 and 75, modify the RAND function as follows:

Modified RAND function

RandArray

If you have Excel 365 or Excel 2021, you can use the magic RANDARRAY function.

1. By default, the RANDARRAY function generates random decimal numbers between 0 and 1. The array below consists of 5 rows and 2 columns.

RANDARRAY function

Note: this dynamic array function, entered into cell A1, fills multiple cells. Wow! This behavior in Excel 365/2021 is called spilling.

2. The RANDARRAY function below generates an array of decimal numbers, 1 row by 5 columns, between 99 and 100.

Generate Random Decimal Numbers

3. The RANDARRAY function below generates an array of integers, 10 rows by 1 column, between 20 and 80.

Generate Random Integers

Note: the Boolean TRUE (fifth argument) tells the RANDARRAY function to return an array of integers.

Random Number Generator

To create a random number generator in Excel, simply press F9 or use VBA (our favorite).

1. Select cell A1.

2. Type =RANDBETWEEN(0,100) and press Enter.

3. Select cell A1, click on the lower right corner of cell A1 and drag it down to cell A10.

Random Numbers Between 0 and 100

4. Press F9 to generate new random numbers repeatedly.

Random Number Generator in Excel

5. To generate random numbers with no duplicates, combine UNIQUE with RANDARRAY.

Generate Random Numbers with No Duplicates

Note: press F9 to refresh the random numbers. Sometimes you will see an array that consists of fewer than 10 numbers (when the RANDARRAY function generates duplicates).

You can also use VBA to create a random number generator in Excel. This approach enables you to quickly produce random numbers that remain constant once generated.

1. Select a range of any size! (for example, the range A1:D10).

2. Execute the following code lines:

Dim rng As Range, cell As Range

Set rng = Selection

For Each cell In rng
    cell.Value = WorksheetFunction.RandBetween(0, 100)
Next cell

Result.

Random Number Generator (VBA)

Note: if you're new to VBA, we're here to help you create and execute a macro.