How to Generate Random Number in Excel?

Excel provides us with two formulas to generate random number those are, RAND() & RANDBETWEEN(). We can modify these functions to get random decimal numbers, random date, random time, random character. We can also calculate random text string to get a password. Well, there is a lot to do with this random function.

In this section, we will discuss the steps to:

1. RAND() Function
2. RANDBETWEEN() Function
3. Random Number with Decimal Places
4. Random Date
5. Random Time
6. Random Letter

Steps to Generate Random Number in Excel 2016:

Using RAND() Function

Step 1: Select a cell in an Excel sheet where you want to generate a random number and type the formula to generate the random number i.e.

= RAND()   // After typing the formula press enter.

Step 2: The RAND() function returns the real number which is randomly picked from the range ‘0 to 1‘. The random number generated is ‘≥ 0’ & ‘< 1’.

Whenever you conduct any action on the excel worksheet which have the RAND() function, it will generate a random real number on each action. Like, when you calculate any formula, or update a formula, or update data of a cell in a sheet, enter new data, etc. the worksheet recalculates itself which causes the RAND() function to recalculate itself again to generate a new random number each time.

In the figure below, you can observe the random number generated by the RAND() function.

Using RANDBETWEEN() Function

Step 1: The RANDBETWEEN() function generate a random integer number from the range we specify. Select a cell to write the formula and type:

= RANDBETWEEN(bottom-range, top-range)  // After typing the formula press enter.

Here, the bottom range is the smallest integer that the RANDBETWEEN() function can return & top range is the largest integer that the RANDBETWEEN() function can return.

In the image below you can see that I have selected the bottom range as 1 and the top range as 1000.

Step 2: The RANDBETWEEN() returns an integer number which is randomly picked from the range you provided to the function. Like, RAND() the RANDBETWEEN() function also recalculates itself every time the worksheet is recalculated. So, each time when some action is performed on the worksheet RANDBETWEEN(), returns a new random integer number.

The image below shows the random number generated by RANDBETWEEN(1, 1000) function. We can observe that the integer generated by this function lies between the range 1-1000.

Generate Random Number with Decimal Places

Step 1: The RANDBETWEEN() formula generates the random integer number between the provided range. But, we can modify it to provide a decimal number between the provided range. We can generate a random number with 2 decimal places using the RANDBETWEEN() formula :

=RANDBETWEEN(1*100, 1000*100)/100  // After typing the formula press enter.

Step 2: In the formula above we have multiplied the range by 100 & the whole result is divided by 100. So, whatever the random integer number will be generated will get divided by 100. So, we will obtain the number with 2 decimal places.

As in the image below, you can see that the random number generated is with 2 decimal places. To generate a random number with one decimal place multiply & divide the range by 10, for 3 decimal place multiply and divide the range by 1000.

Generate Random Date

Step 1: To generate a random date we can manipulate the RANDBETWEEN() formulas as

=RANDBETWEEN(DATEVALUE(“30-Jun-2019”), DATEVALUE(“30-Jul-2020”))
// After typing the formula press enter.

Now, here the DATEVALUE() function in takes the date in the text form and returns a number that Excel recognizes as the date. Now, both the DATEVALUE() functions in the RANDBETWEEN(), return the numbers corresponding to the date inside DATEVALUE() functions.

RANDBETWEEN() takes the returned numbers by DATEVALUE() functions as bottom and top range respectively. And generates a random integer between the bottom and top range.

Step 2: When the corresponding cell is in Date format Excel recognizes the number returned by RANDBETWEEN() as the ‘Date‘.

In the image below you can observe that we have obtained a random date 23 July 2019 because the cell is in Date format. If the cell is in General cell format, Excel recognizes date 23 July 2019 as the number 43669.

Generate Random Time

Step 1: To generate the random time we can build up the RANDBETWEEN() formula as:

=RANDBETWEEN(TIMEVALUE(“3:45 AM”)*10000, TIMEVALUE(“4:32 PM”)*10000)/10000
// After typing the formula press enter.

The TIMEVALUE() function returns a decimal number for the corresponding text time it has taken as an argument. It returns ‘0‘ for 12:00:00 AM to ‘0.9998‘ for 11:59:59 PM.

Now, we know that the RANDBETWEEN() takes two integer arguments as bottom range and top range respectively. So, we have multiplied the range (in TIMEVALUE() context) by 10000 & consecutively divided the result with 10000 to nullify the multiplication effect.

So, the both TIMEVALUE() function returns two decimal numbers which are multiplied by 10000 to get them converted into integers. Further, the RANDBETWEEN() takes these integers as bottom and top range and returns a random integer value which is further divided by 10000 to again convert it into a decimal number.

Step 2: Now, the cell with this decimal number when seen in Time format exhibits the time for the corresponding decimal number.

Generate Random Letter

Step 1: To generate the random letter we can modify the RANDBETWEEN() as follow:

=CHAR(RANDBETWEEN(CODE(“A”), CODE(“Z”)))
// After typing the formula press enter.

Now, here the for a text string CODE() returns the numeric code for the first character in the text string.

So, both the CODE() functions in the RANDBETWEEN() returns a numeric code which is an integer and serves as bottom and top range to RANDBETWEEN() function.

In turn, RANDBETWEEN() generates a random integer which is between the range provided by CODE() functions.

This random integer is served as an argument to CHAR() function which returns the character corresponding to the integer number returned by RANDBETWEEN() function.

So, this was all about how we can generate a random number in Excel. You can explore it more to get more formulas to generate random numbers in Excel.