Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function.
RAND() function returns a random number between 0 and 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.
As shown above, there are duplicates. If you don´t want duplicates, read this post:
Generate numbers with a decimal
In this example, the RANDBETWEEN function generates random numbers between 2 and 4 with a decimal.
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.
Generate negative numbers with two decimals
In this example, the RANDBETWEEN function generates random numbers between -1 and -0.9 with two decimals.
The function generates numbers between -100 and -90. The returned number is then divided by 100. The range becomes -1.00 to -0.90.
Generate negative numbers with three decimals
Here, the RANDBETWEEN function generates random negative numbers between -1010.000 and -1000.000
The function generates numbers between -1010000 and -1000000. The returned number is then divided by 1000. The range becomes -1010.000 to -1000.000
Generate random names
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])
The INDEX function let´s us 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.
Generate a random list
Let´s rearrange the names in cell A3:A13 in random order.
- Select cell range B3:B13
- Type =RAND()
- Press and hold CTRL + Enter
- Select cell range A3:B13
- Right click on cell range
- Click Sort
- Click "Custom Sort..."
- Sort by: Column B
- Order: Smallest to Largest
- Click OK
Create random text strings
Step 1 - Return a random number between 97 and 122
So why return a number between 97 and 122? All characters have a numeric code and the CODE function let´s 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
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))
Download excel *.xlsx file
Functions in this post:
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
Returns a numeric code for the first character in a text string, in the character set used by your computer
Returns the character specified by the code number from the character set for your computer