Generate Random Numbers in Excel

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.

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

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.

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

RANDBETWEEN

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

1. Select cell A1.

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

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

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.

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.

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

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.

4. Press F9 to generate new random numbers repeatedly.

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

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:

Result:

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