# How to Generate Random Numbers in Excel

What do lottery balls, dice, and a roulette wheel have in common? They are based on randomness, which cuts across numerous fields and exists in different forms.

You might want to generate random numbers to test a financial model, create sample data, or simulate sports fixtures.

This article takes a look at how to generate random numbers in Excel.

CONTENTS 1.Download Examples2.Generate Random Numbers with RANDa) Syntax b) Generate Random Decimals Between a Range c) Generate Random Integers3.Generate Random Numbers Between a Range with RANDBETWEENa) Syntax b) Generate Random Decimals4.Generate Random Numbers with RANDARRAYa) Syntax5.Generate Non-Repeating Random Numbers6.Final Words7.Resources

### Download Examples

### Generate Random Numbers with RAND

**RAND** is one of the two original functions for generating random numbers.

It generates a number with up to 17 decimal places that is equal to or greater than 0 and less than 1.

There are no arguments, so just place an empty pair of parentheses next to the function.

#### Syntax

`=RAND()`

Only the first 9 decimal places are displayed in these examples, even though the actual amount is greater. Repeatedly click the

Increase Decimalbutton inHome(tab) >Number(group) to see the others.

#### Generate Random Decimals Between a Range

To return a random decimal greater than or equal to 0 and less than ** max**, multiply

`RAND()`

by **.**

*max*`=RAND()*`*max*

Formula to return a random decimal greater than or equal to 0 and less than 5:

`=RAND()*5`

To start from 1, multiply `RAND()`

by the result of ** max** minus 1, and then add 1.

`=RAND()*(`*max-1*)+1

Formula to return a random decimal greater than or equal to 1 and less than 5:

`=RAND()*4+1`

If you want control over the minimum value, use this syntax:

`=RAND()*(`*max*-*min*)+*min*

Formula to return a random decimal greater than or equal to 10 and less than 20:

`=RAND()*(20-10)+10`

#### Generate Random Integers

With the help of **ROUND**, a decimal can be rounded to the nearest whole number by setting `num_digits`

to 0.

The number multiplied by `RAND()`

needs to be one less than the intended upper limit.

Formula to return a random integer greater or equal to 0 and less than 5:

`=ROUND(RAND()*4,0)`

If you want to include the upper number as a possibility, you can either go one higher in the previous formula, or you can wrap **INT **around the calculation to chop off the decimal part.

The given number must be one greater than the upper limit.

Formula to return a random integer between (and including) 0 and 5:

`=INT(RAND()*6)`

### Generate Random Numbers Between a Range with RANDBETWEEN

**RANDBETWEEN **is the other original function. It returns a random integer between (and including) two specified values.

#### Syntax

`=RANDBETWEEN(bottom,top)`

`bottom`

— the minimum value to return`top`

— the maximum value to return

Formula to return a random integer greater than or equal to 1 and greater or equal to 5:

`=RANDBETWEEN(1,5)`

#### Generate Random Decimals

**RANDBETWEEN** is designed to work with integers only; however, it is possible to coerce it to return decimals.

To return a random number rounded to one decimal place, multiply the `bottom`

and `top`

values by 10, and then divide the result by the same number.

`=RANDBETWEEN(`*bottom**10,*top**10)/10

Formula to return a random decimal (to 1 d.p.) between (and including) 1 and 5:

`=RANDBETWEEN(1*10,5*10)/10`

If you wanted to round to two decimal places, you would use 100 instead.

Formula to return a random decimal (to 2 d.p.) between (and including) 1 and 5:

`=RANDBETWEEN(1*100,5*100)/100`

### Generate Random Numbers with RANDARRAY

So far, you’ve seen how to generate one random number for one cell. **RANDARRAY **enables you to generate a set of random numbers using a single formula. It is only available in Excel for Microsoft 365 and Excel 2021, however.

#### Syntax

`=RANDARRAY([rows],[columns],[min],[max],[integer])`

`[rows]`

— the number of rows to return`[columns]`

— the number of columns to return`[min]`

— the minimum value to return`[max]`

— the maximum value to return`[integer]`

— specify**TRUE**to return whole numbers or**FALSE**(default) for decimal ones

**RANDARRAY** can be used with empty parentheses like **RAND**; however, the former includes 1 as the highest possible number.

By setting a `[rows]`

, `[min]`

and `[max]`

number, you can return multiple random decimals with a sole formula.

Formula to return 5 random decimals between (and including) 1 and 5:

`=RANDARRAY(5,,1,5)`

**RANDARRAY** also makes it easier to return between any two numbers.

Formula to return 5 random decimals between (and including) 10 and 20:

`=RANDARRAY(5,,10,20)`

Include **TRUE** in `[integer]`

to ensure only integer numbers are returned.

Formula to return 5 integer numbers greater than or equal to 1 and less than or equal to 5:

`=RANDARRAY(5,,1,5,TRUE)`

You can return a multi-dimensional array by setting a `[columns]`

value.

Formula to return a 5×3 (rows x columns) array of random integer numbers between (and including) 1 and 100:

`=RANDARRAY(5,3,1,100,TRUE)`

### Generate Non-Repeating Random Numbers

So far, all the methods you’ve seen *can* return repeating numbers. This is very rare for decimals as they are rounded up to 17 places, but it’s far more likely for integers.

Thankfully, there is a workaround that uses **RANDARRAY** in conjunction with **SEQUENCE **and **SORTBY**.

`=SORTBY(`

SEQUENCE(*max*),

RANDARRAY(*max*)

)

** Max** is the upper value included in the

`rows`

and `[rows]`

arguments of **SEQUENCE**and

**RANDARRAY**, respectively.

**SEQUENCE** guarantees an array of unique numbers sorted by the decimals **RANDARRAY **produces. This makes it impossible to get duplicates, as the **SEQUENCE **figures will always take precedent if **RANDARRAY **returns repeats.

Formula to return 10 integers between (and including) 1 and 10:

`=SORTBY(`

SEQUENCE(10),

RANDARRAY(10)

)

If you require a larger range of possible values than the quantity itself, place the statement you just saw in **INDEX**’s `array`

argument and use **SEQUENCE** in `row_num`

to return a certain amount of ** rows**.

`=INDEX(`

SORTBY(

SEQUENCE(20),

RANDARRAY(20)

),

SEQUENCE(*rows*)

)

Formula to return 10 integers between (and including) 1 and 20:

`=INDEX(`

SORTBY(

SEQUENCE(20),

RANDARRAY(20)

),

SEQUENCE(10)

)

If you want the minimum number to be something other than 1, you’ll need to include a minimum value in the `[start]`

argument of the first **SEQUENCE **and ensure the formula conforms to this syntax:

`=INDEX(`

SORTBY(

SEQUENCE(*max-min*,,*min*),

RANDARRAY(*max-min*)

),

SEQUENCE(*rows*)

)

Formula to return 10 integers greater than or equal to 10 and less than 50:

`=INDEX(`

SORTBY(

SEQUENCE(40,,10),

RANDARRAY(40)

),

SEQUENCE(10)

)

To create a grid of random unique numbers, specify a value in the `[columns]`

argument of the second **SEQUENCE**.

Formula to return a 5×3 array of unique integers greater than or equal to 10 and less than 50:

`=INDEX(`

SORTBY(

SEQUENCE(40,,10),

RANDARRAY(40)

),

SEQUENCE(5,3)

)

### Final Words

You’ve just learnt a multitude of ways to generate random numbers in Excel, whether that’s integers, decimals, or non-repeating numbers.

It’s important to remember that despite the range of options, the numbers are never truly random.

Excel 2003 and Excel 2007 used the Wichman & Hill RNG algorithm to generate random numbers between zero and one; however, it was flawed as negative numbers were also returned.

Its successor, Mersenne Twister (Excel 2010 and beyond), does pass all the standard randomness tests, but the numbers are still pseudo-random. That means, in theory, it is possible to unravel the algorithm to predict the next number.

For true randomness, there would have to be an event(s) occurring outside the computer environment. Websites like random.org use atmospheric noise for their generators, but Excel’s functions are sufficient for most purposes.

### Resources

I first posted about generating random numbers on LinkedIn.

https://www.linkedin.com/feed/update/urn:li:activity:6888158407568752640/

You should learn how to convert formulas into lambdas — it’s a real time-saver! I founded **RANDUNIQUE**, which returns a set of unique random numbers based on given parameter values.

https://www.linkedin.com/feed/update/urn:li:activity:6888158407568752640/

I also built an Excel cup draw simulator, which mimics the ones you see on the telly! It’s a good practical example of how you might apply the concepts discussed.

https://www.linkedin.com/feed/update/urn:li:activity:6888158407568752640/