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

The tutorial explains the specificities of the Excel random number generator algorithm and demonstrates how to use RAND and RANDBETWEEN functions to generate random numbers, dates, passwords and other text strings in Excel.

Before we delve into different techniques of generating random numbers in Excel, let's define what they actually are. In plain English, random data is a series of numbers, letters or other symbols that lacks any pattern.

Randomness has a variety of different applications in cryptography, statistics, lottery, gambling, and many other fields. And because it has always been in demand, various methods of creating random numbers have existed since ancient times, such as flipping coins, rolling dice, shuffling playing cards, and so on. Of course, we won't rely on such "exotic" techniques in this tutorial and focus on what Excel random number generator has to offer.

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.
Random Number Generator

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
  • 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 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 2019, Excel 2016, Excel 2013, Excel 2010, 2007, 2003, XP, and 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:
Generate random real numbers using Excel's RAND function

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

Formula 1. Specify the upper bound value of the range

To generate random numbers between zero and any N value, you multiple the RAND function by N:


For example, to create a sequence of random numbers greater than or equal to 0 but less than 50, use the following formula:


Note. The upper bound value is never included in the returned random sequence. For instance, if you want to get random numbers between 0 and 10, including 10, the right formula is =RAND()*11.

Formula 2. Generate random numbers between two numbers

To create a random number between any two numbers that you specify, use the following RAND formula:


Where A is the lower bound value (the smallest number) and B is the upper bound value (the largest number).

For example, to generate random numbers between 10 and 50, you can use the following formula:


Note. This random formula will never return a number equal to the largest number of the specified range (B value).

Formula 3. Generating random integers in Excel

To make the Excel RAND function produce random integers, take either of the above mentioned formulas and wrap it in the INT function.

To create random integers between 0 and 50:


To generate random integers between 10 and 50:


Generating random integers in Excel

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:

RANDBETWEEN(bottom, top)

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:


Using the RANDBETWEEN function to generate random integers in a specified range

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:


The RANDBETWEEN function is available in Excel 2019, Excel 2016, Excel 2013, Excel 2010, and 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:

RANDBETWEEN(bottom value * 10, top value * 10)/10

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

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

Create random numbers with as many decimal places as you want

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:


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


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

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:
Insert random times by using the standard RAND function and applying the time format

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

Formula 1. Generate random times in the specified range

To insert random times between any two times that you specify, use either the TIME or TIMEVALUE function in conjunction with Excel RAND:

TIME(start time)+RAND() * (TIME(start time) - TIME(end time))
TIMEVALUE(start time)+RAND() * (TIMEVALUE(start time) - TIMEVALUE(end time))

For example, to insert a random time between 6:00 AM and 5:30 PM, you can use either of the following formulas:

=TIME(6,0,0) + RAND() * (TIME(17,30,0) - TIME(6,0,0))

=TIMEVALUE("6:00 AM") + RAND() * (TIMEVALUE("5:30 PM") - TIMEVALUE("6:00 AM"))
The TIME / RAND formula to generate random times in Excel

Formula 2. Generating random dates and times

To create a list of random dates and times, use a combinations of RANDBETWEEN and DATEVALUE functions:

RANDBETWEEN(DATEVALUE(start date), DATEVALUE(end date)) + RANDBETWEEN(TIMEVALUE(start time) * 10000, TIMEVALUE(end time) * 10000)/10000

Supposing you want to insert random dates between June 1, 2015 and June 30, 2015 with a time between 7:30 AM and 6:00 PM, the following formula will work a treat:

=RANDBETWEEN(DATEVALUE("1-Jun-2015"), DATEVALUE("30-Jun-2015")) + RANDBETWEEN(TIMEVALUE("7:30 AM") * 10000, TIMEVALUE("6:00 PM") * 10000) / 10000

Generating random dates and times in a specified range

You can also supply dates and times using the DATE and TIME functions, respectively:

=RANDBETWEEN(DATE(2015,6,1), DATE(2015,6,30)) + RANDBETWEEN(TIME(7,30,0) * 10000, TIME(18,0,0) * 10000) / 10000

Generating random letters in Excel

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


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.

Generating random letters in Excel

Note. Since, the ANSI codes are different for UPPERCASE and lowercase characters, this formula is case-sensitive.

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:


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


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 "/").


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:


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&".
Generating text strings in Excel

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 1st function always returns a number, the 2nd 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. Alternatively, you can press Shift + F10 and then V, which is the shortcut for the "Paste special >Values" option.

To learn a few more techniques to "freeze" random numbers, see How to replace formulas with their values in Excel.

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 in How to prevent Excel random function from recalculating.
  3. Remove duplicate values by using either Excel's built-in tool (see How to remove duplicates in Excel) or our advanced Duplicate Remover for Excel.

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.

AbleBits Random Number Generator algorithm

Before showing our Random Generator in action, let me provide a few key notes on its algorithm so that you know exactly what we are offering.

  • AbleBits Random Number Generator for Excel is based on the Mersenne Twister algorithm, which is considered an industry standard for high-quality pseudo randomization.
  • We use version MT19937 that produces a normally distributed sequence of 32-bit integers with a very long period of 2^19937 - 1, which is more than sufficient for all imaginable scenarios.
  • Random numbers generated using this method are of a very high quality. The Random Number Generator has successfully passed multiple tests for statistical randomness, including the well-known NIST Statistical Test Suiteand Diehard tests and some of TestU01 Crush randomness tests.

Unlike Excel random functions, our Random Number Generator creates permanent random values that do not change when a spreadsheet recalculates.

As already noted, this advanced Random Number Generator for Excel offers a formula free (and consequently error-free :) way to create various random values such as:

  • Random integers or decimal numbers, including unique numbers
  • Random dates (workdays, weekends, or both, and optionally unique dates)
  • Random text strings, including passwords of a given length and pattern, or by mask
  • Random Boolean values of TRUE and FALSE
  • Random selection from custom lists

And now, let's see the Random Number Generator in action, as promised.

Generate random numbers in Excel

With AbleBits Random Number Generator, creating a list of random numbers is as easy as clicking the Generate button.

Generating unique random integers

All you have to do is select the range to be populated with random integers, set the bottom and top values and, optionally, check the Unique Values box.
Generating unique random integers in Excel

Generating random real numbers (decimals)

In a similar manner, you can generate a series of random decimal numbers in the range that you specify.
Generating random decimal numbers in Excel

Create random dates in Excel

For dates, our Random Number Generator provides the following options:

  • Generate random dates for a specific time period - you enter the bottom date in the From box and the top date in the To box.
  • Include weekdays, weekends, or both.
  • Generate unique dates.

Creating random dates in Excel

Generate random text strings and passwords

Apart from random numbers and dates, with this Random Generator you can easily create random alphanumeric strings with certain character sets. The maximum string length is 99 characters, which allows generating really strong passwords.

Generating random text strings and passwords in Excel

A unique option provided by AbleBits Random Number Generator is creating random text strings by mask. This is a very useful feature for generating globally unique identifiers (GUID), zip codes, SKUs, and so on.

For example, to get a list of random GUIDs, you select the Hexadecimal character set and type ????????-????-????-???????????? in the Mask box, as demonstrated in the screenshot:

Generating random text strings by mask

If you are interested to give our Random Generator a try, you are most welcome to download a fully functional trial version. And if you like the tool, you can purchase it as a separate product or as part of our Ultimate Suite for Excel, which is a collection of 60+ professional tools for Excel. Either way, we will happily offer you the 15% off coupon code that we've created especially for our blog readers: AB14-BlogSpo.

If someone wants to have a closer look at the functions and formulas discussed in this tutorial, feel free to download the Sample workbook with random formula examples. Thank you for reading!

You may also be interested in:

39 Responses to "How to generate random numbers in Excel - RAND and RANDBETWEEN functions"

  1. shekhar says:

    I have a date field and a time field in my excel table
    For each day there are multiple times
    20-1-2009 6:00
    20-1-2009 7:00
    20-1-2009 8:00

    so on till
    20-1-2009 24:00
    from each day I need to generate one randon time from all times I have.

    Can you help

  2. shekhar says:

    I have several days extending for over 4 years
    I need to generate one randon time for each date for the next 4 years

  3. TG says:

    Is the specific method of generating RAND() in Excel known, or is it a black box? In other words, how random is RAND()?

  4. Yasser Nanjari says:


    Do you know how to change the root of the generator? has excell a simple method to do that? thanks.

  5. says:

    I want to generate a lotto chart of 1 - 90 in 5 unrepeated rows involving all numbers such that every winning result of 5 numbers must comform with at least one possible and correct row.

  6. says:

    When I say '5 rows', I mean as many times as posible for each number in the ninety numbers.

    Urgently pls.

  7. Asif says:

    Hi Svetlana, I have a requirement in excel when I simulate random variables in excel particular cell, if the cell value is within a specified range, I want to copy that value to a new cell each time it simulates, I want to retain the previous value to calculate the mean, Kindly help with the requirement of copying the simulated value to new cell I am running the simulation 10k times

  8. Hunter says:


    i am working on a norm.inv(rand(),Mean,Std dev) using these mean and deviation values to generate my random number. however, the deviation may bring the value across 0, negative, which is not possible for my needs. is it possible to put a bounds (randbetween) while still asking it to use the mean and dev. to create the number?

  9. sharath says:

    how to generate the 100 random numbers equally
    distributed between 23 and 87 using excel

  10. prashant gk says:

    I need random incremental decimal values in a range.

    22.1234 to 79.1234

    and from row 1 to row 300.

    how to do it........?

  11. Luke says:

    3 cells wide 100 down that have my own numbers that don't change.

    3 cells wide beside it and 100 down as well but random numbers between 1 and 20 "no decimals"

    3 Cells wide 100 down as a match column that highlights any number of single pairs or tripples

    Can you paste or send to my email please

    I need for my work so please help me :(

  12. Colin Banfield says:

    Very nice article. Note that from Excel 2010 onwards, Excel RAND() function uses the Mersenne Twister algorithm.

  13. Chris says:

    Trying to randomize the 55- 2 digit combinations for 0-9. For example: 0,0 0,1 and so on through 9,9. 0,1 and 1,0 are considered the same combo. Gotta be easier than 55 slips of paper?? Can you help?

  14. Carlos Castro says:

    Hi, is there a possible way to combine the randbetween function with current day. In other words use a random number with current day to create an unique number using only the year and the day only. Thanks!

  15. maddogbad says:

    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.

    Use Rnd(-N) to generate a reproducible number.
    Feed the previous results as a negative number to the succeeding Rnd calls, x1=RND(-Seed); x2=RND(-x1) etc.. to produce a repeatable sequence from a seed number.

    • Hello!

      Thank you for your comment.

      Right, in Excel VBA you can initialize a random sequence using Rnd(-N), but as noted in the tutorial, it cannot be done with the regular worksheet functions RAND or RANDBETWEEN. Or course, you can create your own user-defined function that will use RND to generate reproducible numbers.

  16. martin says:

    Hi, Would you please help me find the correct formula to generate a random number between 1500 and 4000 which ends in a zero (either round up or down, doesnt matter).
    Kind regards

  17. Sam says:

    I have a small VBA code setup to generate 4 random integers (using INT (RAND()*(10-1)+1) ) between 1 and 10 every time I push a button on the worksheet. What I noticed is that every time I open the spreadsheet it cycles through the same order of numbers. Meaning the first time I click the button I'll get the same 4 random numbers, the second time I push the button I'll get the same set of numbers I got the last time I opened the sheet and presssed it twice.

  18. Brent says:

    I have a list of Latitude and Longitude values in excel, is there a way to build a script that will take the column of data and randomize the last 2 digits past the decimal? This would in effect jitter the points by a few meters, and keep points from all falling on top of each other on a map. Anyone know how to accomplish this?

  19. Khairil says:

    Hi there, i want to generate random number between 2 number in decimal point form (between 0.1 and 0.6), but always got value of 1. Is there any possible way to make the random number generate between these 2 values?

  20. Happiness says:

    Hello can you please help me to find the next number in Random Number Generator when the last numbers was 351 and 610 then what is the next number? please help me.

  21. TAH AJAGA says:

    wow i like this app

  22. UseMyTools says:

    Nice information about randam password generator.

  23. Lars Fussing says:

    What is the best formula when rolling (many) dice:
    a) The TRUNC(RAND) function
    b) The INT(RAND) function
    c) are they both suitable?

  24. nincals says:

    how to generate random number (real) in a specified range that will have a Ppk value of at least 1.3


    • Doug says:

      Is something like this what you want?
      The lowest value is 1.3, the highest value is 5 and the numbers will be decimal numbers. This idea is discussed in the above article.
      You can format the cells as numbers to show one decimal place.

  25. JAYESH PALAN says:

    1 180
    2 180
    3 360
    4 180
    5 180
    6 360
    7 180
    8 180
    9 360
    10 540
    11 180
    12 180
    13 180
    14 720
    15 540
    16 360
    17 180
    18 180
    19 360
    20 540
    21 360
    22 180
    23 360
    24 180
    25 720
    26 900
    27 1080
    28 3600
    29 9000
    30 5400
    31 2700
    32 1800
    33 1260
    34 540
    35 900
    36 720
    37 540
    38 180
    39 540
    40 360
    41 180
    42 360
    43 540
    44 180
    45 360
    46 540
    47 720
    48 180
    49 180
    50 180

    want in cell two to select rendomly no so that there sum may be 18000 and 16200 like wise is this possible by any formula

  26. kishor says:

    Is it possible if
    then B=random value less than 12
    and C=random value less than 12

  27. Mel says:

    Is there a way to use the RAND function with exceptions? I want to generate random numbers between 93 and 118 but they can not include 103 or 114. (Also each number can only appear a maximum of twice per row). Is there a better function to use?

  28. chandru says:

    could you please any one help how to random using below string

    Example :


  29. Taras says:

    What is the max capacity of RAND() in Excel - i.e. what number of i.i.d. random numbers one can safely simulate without running into autocorrelation of them (breaking statistical tests on i.i.d.)? Thanks

  30. Johan says:

    Hola, quisiera saber cuál es la diferencia entre aleatorio y pseudoaleatorio?

    • Hello Johan!

      Random numbers are a sequence of numbers selected in a random way. Pseudo-random numbers are used instead of random ones. They are generated by a computer program using a predefined complex algorithm.
      For an ordinary user they look like random, but still they can be predicted if you know that complex algorithm. Pseudo-random numbers are used in all computer programs.

  31. Modesta Nnedinso Abugu says:

    I want to generate a series of A’s and B’s but never more than 2 of A or B is it possible?

    • Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard