Article updated on April 25, 2018

Microsoft Excel has two useful functions for generating random numbers, the RAND function and RANDBETWEEN function.

RAND() function returns a random number equal to or larger than 0 (zero) and smaller than 1. Example: 0.600842025092928

RANDBETWEEN function returns values in a range you specify. The function has two arguments: bottom and top.

RANDBETWEEN(bottom, top). Bottom is the lowest number in the range of numbers you want to return. Top is the highest number in the range.

Example, RANDBETWEEN(1,10) generates random numbers between 1 to 10.

generate random numbers

Formula:

=RANDBETWEEN(1,10)

As shown above, there are duplicates. If you don't want duplicates, read this post: How to create a list of random unique numbers

Back to top

Generate numbers with a decimal

In this example, the RANDBETWEEN function generates random numbers between 2 and 4 with a decimal.

generate random numbers1

Formula:

=RANDBETWEEN(20,40)/10

Since it actually can't generate numbers with decimals the function generates values between 20 to 40. Divide the output with 10 and the range becomes 2.0 to 4.0.

Back to top

Generate negative numbers with two decimals

In this example, the RANDBETWEEN function generates random numbers between -1 and -0.9 with two decimals.

generate random negative numbers

Formula:

=RANDBETWEEN(-100,-90)/100

The function generates numbers between -100 and -90. The returned number is then divided by 100. The range becomes -1.00 to -0.90.

Back to top

Generate negative numbers with three decimals

Here, the RANDBETWEEN function generates random negative numbers between -1010.000 and -1000.000

generate random negative numbers with decimals

Formula:

=RANDBETWEEN(-1010000,-1000000)/1000

The function generates numbers between -1010000 and -1000000. The returned number is then divided by 1000. The range becomes -1010.000 to -1000.000

Back to top

Generate random names

generate random names

Formula:

=INDEX($A$3:$A$13,RANDBETWEEN(1,11))

Step 1 - Generate random value between 1 and 11

RANDBETWEEN(1,11) returns a number between 1 to 11

Step 2 - Return a value of the cell at the intersection of a particular row and column

INDEX(array, row_num, [column_num])

=INDEX($A$3:$A$13, random_value)

The INDEX function allows you to return a value from a given range. The given range is $A$3:$A$13 and there are 11 cells in that range. Therefore, we need to return a random value between 1 and 11.

RANDBETWEEN(1,11) returns a number between 1 to 11. The INDEX function then returns a random value from cell range $A$3:$A$13 in cell B3.

You can use the formula in the following post to return unique distinct names: How to create a list of random unique numbers

Back to top

Generate a random list

generate random list

Let us rearrange the names in cell A3:A13 in random order.

  1. Select cell range B3:B13
  2. Type =RAND()
  3. Press and hold CTRL + Enter

generate random list - sort

Sort values

  1. Select cell range A3:B13
  2. Right-click on cell range
  3. Click Sort
  4. Click "Custom Sort..."

generate random list - sort1

  1. Sort by: Column B
  2. Order: Smallest to Largest
  3. Click OK

generate random list - sort2

Back to top

Create random text strings

generate random text strings

Formula:

=CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))

Step 1 - Return a random number between 97 and 122

RANDBETWEEN(97,122)

So why return a number between 97 and 122? All characters have a numeric code and the CODE function lets you convert a character to a numeric code. For example, a is 97 and z is 122. By generating random numbers between 97 and 122 we can create characters from a to z.

Step 2 - Return a character specified by a code number

CHAR(RANDBETWEEN(97,122))

returns a random character from a to z.

Step 3 - Combine four characters

The ampersand operator & combines different functions to a text string.

=CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))

Back to top

Download excel *.xlsx file

RANDBETWEEN function.xlsx

Back to top