How to generate random numbers in Excel: RAND and RANDBETWEEN functions

Excel random number generator - the basics

Although the Excel random generator passes all standard tests of randomness, it does not generate true random numbers. But don't write it off immediately :) Pseudo-random numbers produced by the Excel random functions are fine for many purposes.

Let's take a closer look at the Excel random generator algorithm so that you know what you can expect from it, and what you cannot.

Like most computer programs, Excel random number generator produces pseudo-random numbers by using some mathematical formulas. What it means for you is that, in theory, random numbers generated by Excel are predictable, provided that someone knows all the details of the generator's algorithm. This is the reason why it has never been documented and will hardly ever be. Well, what do we know about the random number generator in Excel?

• The Excel RAND and RANDBETWEEN functions generate pseudo-random numbers from the Uniform distribution, aka rectangular distribution, where there is equal probability for all values that a random variable can take on. A good example of the uniform distribution is tossing a single die. The outcome of the toss is six possible values (1, 2, 3, 4, 5, 6) and each of these values is equally likely to occur. For a more scientific explanation, please check out wolfram.com.
• There is no way to seed either Excel RAND or RANDBETWEEN function, which are rumored to be initialized from the computer's system time. Technically, a seed is the starting point for generating a sequence of random numbers. And every time an Excel random function is called, a new seed is used that returns a unique random sequence. In other words, when using the random number generator in Excel, you cannot get a repeatable sequence with the RAND or RANDBETWEEN function, nor with VBA, nor by any other means.
• In early Excel versions, prior to Excel 2003, the random generation algorithm had a relatively small period (less than 1 million nonrecurring random number sequence) and it failed several standard tests of randomness on lengthy random sequences. So, if someone still works with an old Excel version, you'd better not use the RAND function with large simulation models.

If you are looking for true random data, you can probably use a third-party random number generator such as www.random.org whose randomness comes from atmospheric noise. They offer free services to generate random numbers, games and lotteries, color codes, random names, passwords, alphanumeric strings, and other random data.

Okay, this quite lengthy technical introduction ends and we are getting to practical and more useful things.

Excel RAND function - generate random real numbers

The RAND function in Excel is one of the two functions specially designed for generating random numbers. It returns a random decimal number (real number) between 0 and 1.

RAND() is a volatile function, meaning that a new random number is generated every time the worksheet is calculated. And this happens every time you perform any action on a worksheet, for example update a formula (not necessarily the RAND formula, just any other formula on a sheet), edit a cell or enter new data.

The RAND function is available in all versions of Excel 365 - 2000.

Since the Excel RAND function has no arguments, you simply enter =RAND() in a cell and then copy the formula into as many cells as you want:

And now, let's take a step further and write a few RAND formulas to generate random numbers according to your conditions.

Excel RANDBETWEEN function - generate random integers in a specified range

RANDBETWEEN is another function provided by Excel for generating random numbers. It returns random integers in the range that you specify:

Obviously, bottom is the lowest number and top is the highest number in the range of random numbers you want to get.

Like RAND, Excel's RANDBETWEEN is a volatile function and it returns a new random integer every time your spreadsheet recalculates.

For instance, to generate random integers between 10 and 50 (including 10 and 50), use the following RANDBETWEEN formula:

=RANDBETWEEN(10, 50)

The RANDBETWEEN function in Excel can create both positive and negative numbers. For example, to get a list of random integers from -10 to 10, enter the following formula in your worksheet:

=RANDBETWEEN(-10, 10)

The RANDBETWEEN function is available in Excel 365 - Excel 2007. In earlier versions, you can use the RAND formula demonstrated in Example 3 above.

Further on in this tutorial, you will find a few more formula examples demonstrating how to use the RANDBETWEEN function to generate random values other than integers.

Create random numbers with specified decimal places

Though the RANDBEETWEEN function in Excel was designed to return random integers, you can force it to return random decimal numbers with as many decimal places as you want.

For instance, to get a list of numbers with one decimal place, you multiply the bottom and top values by 10, and then divide the returned value by 10:

The following RANDBETWEEN formula returns random decimal numbers between 1 and 50:

=RANDBETWEEN(1*10, 50*10)/10

In a similar manner, to generate random numbers between 1 and 50 with 2 decimal places, you multiply the RANDBETWEEN function's arguments by 100, and then divide the result by 100 as well:

=RANDBETWEEN(1*100, 50*100) / 100

How to generate random dates in Excel

To return a list of random dates between the given two dates, use the RANDBETWEEN function in combination with DATEVALUE:

For example, to get the list of dates between 1-Jun-2015 and 30-June-2015 inclusive, enter the following formula in your worksheet:

=RANDBETWEEN(DATEVALUE("1-Jun-2015"),DATEVALUE("30-Jun-2015"))

Alternatively, you can use the DATE function instead of DATEVALUE:

=RANDBETWEEN(DATE(2015,6,1),DATEVALUE(2015,6,30))

Remember to apply the date format to the cell(s) and you will get a list of random dates similar to this:

For a number of advanced options such as generating random weekdays or weekends, check out Advanced Random Generator for dates.

How to insert random times in Excel

Remembering that in the internal Excel system times are stored as decimals, you can use the standard Excel RAND function to insert random real numbers, and then simply apply the time format to the cells:

To return random times according to your criteria, more specific random formulas are required, as demonstrated below.

Generating random letters in Excel

To return a random letter, a combination of three different functions is required:

=CHAR(RANDBETWEEN(CODE("A"),CODE("Z")))

Where A is the first character and the Z is the last character in the range of letters you want to include (in alphabetical order).

In the above formula:

• CODE returns numeric ANSI codes for the specified letters.
• RANDBETWEEN takes the numbers returned by the CODE functions as the bottom and top values of the range.
• CHAR converts random ANSI codes returned by RANDBETWEEN to the corresponding letters.

If someone remembers the ANSI Character Codes Chart by heart, nothing prevents you from supplying the codes directly to the RANDBETWEEN function.

For example, to get random UPPERCASE letters between A (ANSI code 65) and Z (ANSI code 90), you write:

=CHAR(RANDBETWEEN(65, 90))

To generate lowercase letters from a (ANSI code 97) to z (ANSI code 122), you use the following formula:

=CHAR(RANDBETWEEN(97, 122))

To insert a random special character, such as ! " # $ % & ' ( ) * + , - . /, use the RANDBETWEEN function with the bottom parameter set to 33 (ANSI code for "!') and the top parameter set to 47 (ANSI code for "/").

=CHAR(RANDBETWEEN(33,47))

Generating text strings and passwords in Excel

To create a random text string in Excel, you just have to concatenate several CHAR / RANDBEETWEEN functions.

For example, to generate a list of passwords consisting of 4 characters, you can use a formula similar to this:

=RANDBETWEEN(0,9) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(97, 122)) & CHAR(RANDBETWEEN(33,47))

To make the formula more compact, I supplied the ANSI codes directly in the formula. The four functions return the following random values:

• RANDBETWEEN(0,9) - returns random numbers between 0 and 9.
• CHAR(RANDBETWEEN(65,90)) - returns random UPPERCASE letters between A and Z.
• CHAR(RANDBETWEEN(97, 122)) - returns random lowercase letters between a and z.
• CHAR(RANDBETWEEN(33,47)) - returns random special characters.

The text strings generated with the above formula would be something like "4Np#" or "2Yu&".

A word of caution! If you use a similar formula to create random passwords, they won't be strong. Of course, there's nothing saying you can't generate longer text strings by chaining more CHAR / RANDBETWEEN functions. However, it's impossible to randomize the order or characters, i.e. the 1^st function always returns a number, the 2^nd function returns an uppercase letter and so on.

If you are looking for an advanced random password generator in Excel capable of producing text strings of any length and pattern, you may want to check out the capabilities of Advanced Random Generator for test strings.

Also, please keep in mind that the text strings generated with the above formula will change every time your worksheet recalculates. To ensure that your strings or passwords remain the same once they are created, you will have to stop the RANDBETWEEN function from updating the values, which leads us directly to the next section.

How to prevent RAND and RANDBETWEEN from recalculating

If you want to get a permanent set of random numbers, dates or text strings that won't change every time the sheet is recalculated, use one of the following techniques:

1. To stop the RAND or RANDBETWEEN functions from recalculating in one cell, select that cell, switch to the formula bar and press F9 to replace the formula with its value.
2. To prevent an Excel random function from recalculating, use the Paste Special > Values feature. Select all the cells with the random formula, press Ctrl + C to copy them, then right click the selected range and click Paste Special > Values.

To learn more about this technique to "freeze" random numbers, see How to replace formulas with values.

How to generate unique random numbers in Excel

Neither of Excel's random functions can produce unique random values. If you want to create a list of random numbers without duplicates, perform these steps:

1. Use the RAND or RANDBETWEEN function to generate a list of random numbers. Create more values than you actually need because some will be duplicates to be deleted later.
2. Convert formulas to values as explained above.
3. Remove duplicate values by using either Excel's built-in tool or our advanced Duplicate Remover for Excel.

More solutions can be found in this tutorial: How to generate random numbers without duplicates.

Advanced Random Number Generator for Excel

Now that you know how to use random functions in Excel, let me demonstrate you a faster, easier and formula-free way to create a list of random numbers, dates or text strings in your worksheets.

AbleBits Random Generator for Excel was designed as a more powerful and user-friendly alternative to Excel's RAND and RANDBETWEEN functions. It works with all versions of Microsoft Excel 2019, 2016, 2013, 2010, 2007 and 2003 equally well and addresses most of the quality and usability issues of the standard random functions.

Available downloads

Random formula examples (.xlsx file)
Ultimate Suite 14-day fully-functional version (.exe file)