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.

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

1. Select cell A1.

2. Type =RANDBETWEEN(5075) and press Enter.

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

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.

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

1. Select cell A1.

2. Type =RANDBETWEEN(0100) 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:

Dim rng As Range cell As Range

Set rng = Selection

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

Result:

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