How to use Excel RAND and RANDBETWEEN Function in Excel?

This Tutorial Covers:

1. RAND Function vs RANDBETWEEN Function
   • RAND Function
   • RANDBETWEEN Function
2. Create random real numbers using the RAND function in Excel
   • Formula 1- Indicate the range’s top limit value
   • Formula 2- Create random numbers in the range of two numbers
   • Formula 3- Generating random integers in Excel
3. Excel RANDBETWEEN function – produce random integers within a certain range
   • Create random numbers with specified decimal places
   • How to generate random dates in Excel
4. How to insert random times in Excel
   • Formula 1- Create random times between the given ranges
   • Formula 2- Generating random dates and times
5. Generating random letters in Excel
6. Generating text strings and passwords in Excel
7. How to stop RAND and RANDBETWEEN from performing calculations again
8. How to generate unique random numbers in Excel

1. RAND Function vs RANDBETWEEN Function

RAND and RANDBETWEEN are two Excel functions that generate random numbers. The sections below include explanations for each random function as well as usage examples.

If you need to generate random numbers, here is a brief comparison to aid in your decision.

• RAND Function:
• A random decimal number is returned.
• Can yield a value that is less than 1 and larger than or equal to 0.
• Returns no negative numbers.
• There are no arguments for syntax.
• Every time Excel recalculates, a volatile formula will recalculate its result.
• RANDBETWEEN Function:
• A random integer number is returned.
• Returns a number that falls within a given range of numbers.
• Can yield a value that is negative, positive, zero, or both.
• To set a range of integers, the syntax requires the bottom number and the top number.
• Every time Excel recalculates, a volatile formula will recalculate its result.

2. Create random real numbers using the RAND function in Excel

One of Excel’s two functions specifically made for generating random numbers is the RAND function. It provides a random real integer in the decimal range of 0 to 1.

Every time the worksheet is calculated, a new random number is produced because RAND() is a volatile function. And this occurs each time you update a formula (not necessarily the RAND formula, simply any other formula on the sheet), edit a cell, or insert new data on a spreadsheet.

The Excel RAND function is accessible in Excel 365 through 2000.

The =RAND() formula can be copied into as many cells as you like because the Excel RAND function doesn’t require any arguments:

Let’s go one step further and create a couple RAND formulas that will generate random numbers based on your specifications.

• Formula 1- Indicate the range’s top limit value:

You multiply the RAND Excel function by N to produce random numbers between 0 and any N value:

=RAND()*N

For instance, use the method below to generate a series of random numbers higher than or equal to 0 but less than 20:

=RAND()*20

Note: The returned random sequence never contains the upper bound value. For instance, the correct formula is =RAND()*21 if you want random numbers between 0 and 20, including 20.

• Formula 2- Create random numbers in the range of two numbers:

Use the RAND algorithm below to generate a random number between any two numbers you specify:

=RAND()*(B-A)+A

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

Use the formula below, for instance, to get an excel random number between 10 and 50:

=RAND()*(20-10)+10

Note that the biggest number inside the given range will never be returned by this random formula (B value).

• Formula 3- Generating random integers in Excel:

Either of the aforementioned formulas can be wrapped in the INT function to produce random integers using the RAND Formula Excel.

To generate random numbers from 0 to 20:

=INT(RAND()*20)

To produce random integers in the range of 20 and 60:

=INT(RAND()*(60-20)+20)

3. Excel RANDBETWEEN function – produce random integers within a certain range

Another function offered by Excel for creating random numbers is RANDBETWEEN. It gives you back random integers in the range you provide:

RANDBETWEEN(bottom, top)

In the range of random numbers you desire, bottom is obviously the lowest number and top is the highest.

The volatile function RANDBETWEEN in Excel, like RAND, generates a new random integer each time your spreadsheet is recalculated.

Use the RANDBETWEEN formula, for example, to produce random integers between 20 and 60 (including 20 and 60):

=RANDBETWEEN(20, 60)

Both positive and negative values can be generated with Excel’s RANDBETWEEN function. For instance, insert the following formula in your worksheet to obtain a list of random integers ranging from -20 to 20:

=RANDBETWEEN(-20, 20)

RANDBETWEEN is a feature of Excel 2007 and Excel 365. The RAND formula shown in Formula 3 above can be used in previous versions.

More formula examples showing how to employ the RANDBETWEEN function to produce random numbers other than integers may be found later in this lesson.

Tip: The dynamic array RANDARRAY function in Excel 365 and Excel 2021 can be used to return an array of random numbers between any two numbers that you provide.

• Create random numbers with specified decimal places:

Excel’s RANDBEETWEEN function may be made to return random decimal numbers with as many decimal places as you like even though it was intended to return random integers.

For instance, you would multiply the bottom and top values by 10, divide the result by 10, and then receive a list of numbers with one decimal place:

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

The RANDBETWEEN algorithm below generates random decimal integers in the range of 20 and 60:

=RANDBETWEEN(20*10, 60*10)/10

Similar to this, to get random numbers between 20 and 60 with two decimal places, you increase the arguments of the RANDBETWEEN function by 100, divide the result by 100, and repeat:

=RANDBETWEEN(20*100, 60*100)/100

• How to generate random dates in Excel?

Use the RANDBETWEEN function along with DATEVALUE to return a list of random dates between the provided two dates:

=RANDBETWEEN(DATEVALUE(start date), DATEVALUE(end date))

For instance, insert the formula below in your spreadsheet to produce a list of dates from 1 January 2022 to 31 December 2022 inclusive:

=RANDBETWEEN(DATEVALUE(“1-Jan-2022”),DATEVALUE(“31-Dec-2022”))

In place of DATEVALUE, you can also use the DATE function:

=RANDBETWEEN(DATE(2022,1,1),DATE(2022,12,31))

You will receive a list of arbitrary dates that looks somewhat like this if you remember to apply the date format to the cell(s):

4. How to insert random times in Excel?

Keeping in mind that times are saved as decimals in the internal Excel system, you can insert random real numbers using the RAND function in Excel and then just apply the time format to the cells:

More precise random formulas are needed, as shown next section, to return random timings that meet your criteria.

• Formula 1- Create random times between the given ranges:

Use Excel RAND together with either the TIME or TIMEVALUE function to insert random times in between any two times that you specify:

=TIME(start time)+RAND() * (TIME(start time) – TIME(end time))

=TIMEVALUE(start time)+RAND() * (TIMEVALUE(start time) – TIMEVALUE(end time))

You can use one of the following formulas, for instance, to insert a random time between 8:00 AM and 6:30 PM:

=TIME(8,0,0) + RAND() * (TIME(18,30,0) – TIME(8,0,0))

=TIMEVALUE(“8:00 AM”) + RAND() * (TIMEVALUE(“6:30 PM”) – TIMEVALUE(“8:00 AM”))

• Formula 2- Generating random dates and times:

Use the RANDBETWEEN and DATEVALUE functions in combination to generate a list of random times and dates:

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

The following formula will work perfectly if you wish to insert random days between January 1, 2022, and December 31, 2022, with a time between 8:00 AM and 6:30 PM:

=RANDBETWEEN(DATEVALUE(“1-Jan-2022”), DATEVALUE(“31-Dec-2022”)) + RANDBETWEEN(TIMEVALUE(“8:00 AM”) * 10000, TIMEVALUE(“6:30 PM”) * 10000) / 10000

Additionally, you can also provide times and dates by using the DATE and TIME functions, respectively:

=RANDBETWEEN(DATE(2022,1,1), DATE(2022,12,31)) + RANDBETWEEN(TIME(8,0,0) * 10000, TIME(18,30,0) * 10000) / 10000

5. Generating random letters in Excel

Three distinct functions must be combined in order to return a random letter:

=CHAR(RANDBETWEEN(CODE(“A”),CODE(“Z”)))

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

In the formula above:

• For the given letters, CODE returns ANSI numeric codes.
• The bottom and top values of the range are determined by RANDBETWEEN using the integers returned by the CODE routines.
• Random ANSI codes from RANDBETWEEN are translated into their respective letters by CHAR.

Note: This formula is case-sensitive since the ANSI codes for uppercase and lowercase letters differ.

Nothing prevents you from giving the codes directly to the RANDBETWEEN function if someone knows the ANSI Character Codes Chart off by heart.

As an illustration, write the following to generate UPPERCASE letters at random between A (ANSI code 65) and Z (ANSI code 90):

=CHAR(RANDBETWEEN(65, 90))

You can use the following formula to produce lowercase letters from a (ANSI code 97) to z (ANSI code 122):

=CHAR(RANDBETWEEN(97, 122))

We’re using the RANDBETWEEN function with both the bottom input parameter to 33 (ANSI code for “!’) and the upper parameter set to 47 (ANSI code for “/”). This will input a randomized special character such as! ” # $% &'() * +, -. /.

=CHAR(RANDBETWEEN(33,47))

6. Generating text strings and passwords in Excel

Simply concatenate many CHAR / RANDBETWEEN functions in Excel to generate a random text string.

For instance, you can use a formula like this to create a list of passwords of 4 characters:

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

I included the ANSI codes right in the calculation to make it more succinct. The following arbitrary values are returned by the four functions:

(0, 9) RANDBETWEEN – A random number between 0 and 9 is returned.

CHAR(RANDBETWEEN(65,90)) – Returns a set of UPPERCASE letters, ranging from A to Z.

RANDBETWEEN(97, 122) CHAR – Delivers a range of random lowercase letters from a to z.

CHAR(RANDBETWEEN(33,47)) – Special character output is random.

The text strings produced by the aforementioned algorithm would resemble “9Oh-” or “4Zo$”

Just a word of warning! They won’t be secure if you generate random passwords using a similar algorithm. Of course, there’s nothing preventing you from chaining additional CHAR / RANDBETWEEN functions together to create longer text strings. The first function always returns a number, the second function always returns an uppercase letter, and so on. It is, however, impossible to randomize the order of the characters.

You might want to look into the capabilities of Advanced Random Generator for test strings if you’re seeking for an advanced random password generator in Excel that can create text strings of any length and pattern.

Please keep in mind that each time your worksheet recalculates, the text strings produced by the aforementioned formula will vary. You must prevent the RANDBETWEEN function from changing the values once your strings or passwords have been formed in order to guarantee that they stay the same. This brings us immediately to the following part.

7. How to stop RAND and RANDBETWEEN from performing calculations again?

Use one of the following methods to obtain a set of random numbers, dates, or text strings that will remain constant no matter how often the sheet is recalculated:

1. When a cell is selected, the formula bar should be displayed. Press F9 to change the formula with the cell’s value and stop the RAND or RANDBETWEEN functions from recalculating in that cell.

Use the Paste Special > Values function to stop an Excel random function from recalculating. Press Ctrl + C to copy all of the cells containing the random formula, then right-click the range you have selected and choose Paste Special > Values from the menu that appears.

8. How to generate unique random numbers in Excel?

Both RANDOM Excel Functions are unable to generate distinct random values. Follow these instructions to generate a list of random integers that doesn’t contain any duplicates:

1. Create a list of random numbers by using the RAND or RANDBETWEEN function. Because some values will be duplicates that must be removed later, create more values than you actually need.
2. Calculate values from formulas as previously described.

Use our sophisticated Duplicate Remover for Excel or the built-in duplicate removal feature in Excel to remove duplicate data.

Application of Excel RAND and RANDBETWEEN Function

• Simulating Data: Use RAND and RANDBETWEEN to generate random numbers for simulating data sets, ideal for statistical analysis, modeling scenarios, or financial forecasting.
• Creating Random Samples: Employ RANDBETWEEN to select random samples from larger data sets, useful in statistical sampling, surveys, or experiments to ensure unbiased results.
• Password Generation: Utilize RANDBETWEEN to create secure, random passwords or codes by generating random numbers and converting them into alphanumeric characters.
• Game Development: Implement RAND and RANDBETWEEN for game mechanics that require random outcomes, such as dice rolls, loot drops, or random events.
• Decision Making: Use RAND to make random choices or decisions when options are equivalent, adding an element of unpredictability to selections or allocations.
• Dynamic Chart Data: Generate dynamic, changing datasets with RAND and RANDBETWEEN to demonstrate chart functionalities or to create mockup visualizations for presentations and reports.

For ready-to-use Dashboard Templates:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts