Excel RAND and RANDBETWEEN Functions

These examples show how to use the Excel RAND function and RANDBETWEEN function to create random numbers or random text in Excel. See the steps in videos and written instructions.

RAND Function vs RANDBETWEEN Function

There are two Microsoft Excel functions that create random numbers -- RAND and RANDBETWEEN. Each random function is explained in the sections below, and there are examples showing how to use each function.

To help you decide which function to choose, if you need to create random numbers, here is a quick comparison:

RAND Function

• Returns a random decimal number
• Can return number greater than or equal to zero, and less than 1
• Cannot return negative numbers
• Syntax has no arguments
• Volatile - recalculates its result every time Excel recalculates

See details and examples in the RAND Function section below

RANDBETWEEN Function

• Returns a random integer number
• Returns number within a specified range of numbers
• Can return negative numbers or positive numbers, or zero
• Syntax requires bottom number and top number, to set a range of numbers
• Volatile - recalculates its result every time Excel recalculates

See details and examples in the RANDBETWEEN Function section below

Stop Results from Changing

Both functions are volatile, and recalculate every time Excel recalculates.

After you create a set of random numbers, you can change the random formula results to values, to lock in the results

• Numbers will stay the same, instead of changing every time the workbook is calculated
• Workbook calculation time is reduced

Follow the steps in the Paste As Values section below, to lock in the random numbers.

RAND Function

Use the RAND function to create random decimal numbers in Microsoft Excel.

-- 1) This section has details on the RAND function syntax, the algorithm it uses, and its volatility.

-- 2) See the next section for a RAND function example - Random Task Assignment

RAND Syntax

The RAND function syntax does not require any arguments. Simply type the open and close brackets, with nothing between them.

For example, to create a random decimal number in a cell, use the following formula:

• =RAND()

The result will be greater than or equal to zero, and less than 1

RAND Algorithm

In Excel 2010 and later versions, Excel uses the Mersenne Twister algorithm (MT19937) to generate random numbers. This algorithm was developed in 1997, to avoid the flaws from earlier pseudorandom number generators.

Volatile Function

The RAND function is volatile, and it recalculates its result every time Excel recalculates, to return a new random number. This could slow down an Excel workbook, in some situations.

For more information on volatile functions, and why you should avoid them whenever possible, visit the Volatile Excel Functions page on Charles Williams' site. Charles is an Excel calculation expert, and offers excellent advice, to help you avoid calculation problems, and keep your Excel files running smoothly.

RAND Example - Random Task Assignment

To randomly assign numbers or tasks to a group of people, you can use the RAND function.

In the example below, see how to create a list of people, and a list of task numbers. Then, use RAND formulas to randomly sort the task numbers, and assign each task to a person.

In this example, there are:

• 5 people
• 25 numbered tasks to be assigned

Each person will be assigned 5 random task numbers to complete

Create List of People

Follow these steps, to create the list of people, with each person's name repeated 5 times

• Starting in cell A1, type a list, with each of the 5 people's names entered once
• Select all the names, and point to the fill handle, in the bottom right corner of the selected cell range.

• When the pointer changes to a black plus sign, drag down to row 25.
• As you drag the fill handle, the Excel AutoFill feature repeats the values from the selected range, over and over again, until you stop dragging down.
• When you stop dragging in row 25, the list of 5 names has been repeated 5 times

Create List of Task Numbers

Next, follow these steps, to create a list of 25 task numbers.

• Leave column B blank, to act as a buffer between the list of names, and list of task numbers
• Then, to start the list in cell C1, type the number 1
• In cell C2, type the number 2
• Then, select cells C1 and C2
• Point to the fill handle at the bottom right corner of cell C2
• When the pointer changes to a black plus sign, drag down to row 25
• As you drag the fill handle, the Excel AutoFill feature extends the number sequence from the selected cells, until you stop dragging down
• The result is a list of sequential numbers, from 1 to 25

Create List of Random Numbers

Next, follow these steps, to create a list of 25 random numbers, beside the task numbers.

• In cell D1, type the following RAND formula, to create a random number
  • =RAND()

In the screen shot below, you can see the RAND function from cell D1 in the formula bar

And cell D1 shows the result that the RAND function returned - a single random number with 5 decimal places

• Next, point to the fill handle at the bottom right corner of cell D1 - the active cell in the above image
• Double-click on the fill handle, to fill the RAND formula down to row 25
  • The AutoFill will automatically stop when it reaches the end of the data in column C
• The RAND formulas in cells D1 to D25 create a list of 25 random decimal numbers

Note: Some of the results might have a different number of decimal places

Sort Task Numbers in Random Order

Next, follow these steps, to sort the 25 task numbers in random order.

• To start the sort, right-click on any random number in cells D1:D25
• Then, in the popup menu that appears, click Sort
• In the sub-menu that appears, click on either one of the Sort commands
  • Sort Smallest to Largest
• OR, Sort Largest to Smallest

Match Task List to Names

Now that the task number list has been sorted in random order, follow these steps to assign the tasks to the list of names

• The RAND formulas are no longer needed, so delete column D, where those formulas were entered
• Next, the buffer column is no longer needed, so delete column B (the blank column)
• Now, each name in column A has a randomly assigned task number in column B
• Each person has 5 randomly assigned task numbers to complete

Note: There are no duplicate task numbers assigned, because the task number list had only one instance of each task number, from 1 to 15

RANDBETWEEN Function

Another way to create random numbers is with the Excel RANDBETWEEN function. Specify a bottom number, and a top number, and the formula will return a random integer number within that range of numbers.

Use the RANDBETWEEN function to create random integer numbers in Microsoft Excel, within a specified range of numbers.

1. This section has details on the RANDBETWEEN function syntax, and its volatility.
2. See the next sections for RANDBETWEEN function examples:
   • Create test data for monthly sales amounts
   • Create random text entries or numbers for test score results

Tip: Because RANDBETWEEN returns numbers, you could also use it to return a list of random dates. Excel stores dates as serial numbers.

RANDBETWEEN Syntax

The RANDBETWEEN function syntax has two arguments, and both arguments are required:

• bottom: lowest number to include in the results, the minimum number
• top: highest number to include in the results, the maximum number

The result will be a whole number (integer) within the specified range.

Volatile Function

The RANDBETWEEN function is volatile, and it recalculates its result every time Excel recalculates. This could slow down an Excel workbook, in some situations.

RANDBETWEEN Example - Create Test Data

If you need to create sample test data in Excel, the RANDBETWEEN function can help you get the job done quickly. This example needs monthly sales amount for a list of customers

This video shows how to use the RANDBETWEEN function to quickly create test data with month headings, customer numbers, and random numbers. You'll also see how change the formulas to static values.

There are written steps below the video.

Create Random Numbers in Test Data

In this example, the goal is to create data for a quick test, showing sales to each customer, over a six month period.

To create realistic test data, the sales amounts should be between 10 and 100 each month.

To create the sample sales data, follow these steps:

1. In the screen shot below, cells B2:G7 are selected.
2. Type the formula: =RANDBETWEEN(10,100)
3. Press the Ctrl key, then press Enter, to enter the formula in all the selected cells.

If you want to change the formulas to static values, follow the instructions on the Data Entry Tips page.

You can also get an Excel file with sample data to use for your tests.

Create Random Text and Numbers

In this example, RANDBETWEEN creates a set of random test score data, for records in a sample data file. Use the RANDBETWEEN function to create random whole numbers (integers) in Excel. When combined with CHOOSE, it can even create random text.

See the steps in this video, and the written instructions are below the video.

----Create Random Numbers for Test Score Data

----Create Random Text for Test Score Data

Create Random Score Numbers

In this example, we need to create a set of random test score numbers, for records in a sample data file.

To create realistic data, the scores should be within this range of numbers:

• Lowest (minimum) score should be 30
• Highest (maximum) score should be 100

To create the first random number, enter this formula in cell H2:

• =RANDBETWEEN(30,100)

Next, copy the formula down, to the last row of data in column H, to fill in the remaining scores.

In each row, the above formula returns a whole number, between 30 and 100

Create Random Text Data

The RANDBETWEEN function can only return numbers, so by itself, it can't create a set of random text data. However, you can use RANDBETWEEN inside a CHOOSE formula. to return a random item from a list of values

In this example, random text will be created for two columns in the score results table:

--- 1) Gender - Value options typed in formula

--- 2) Region - Cell references in formula

See the steps in the following sections

Choose Random Text - Options Typed in Formula

To create the sample test score data, we need to fill the Gender column with random data, but we need text in this column, instead of numbers.

For Gender, in this sample data, there are two options:

• 1) Male
• 2) Female

To choose a gender numer at random, we can use the RANDBETWEEN function, with

• 1 as the bottom value
• 2 as the top value

Excel CHOOSE Function

Next, combine that RANDBETWEEN function with the CHOOSE function, to show the text value that matches the random number.

The CHOOSE function syntax has the following arguments:

• CHOOSE(index_num,value1,value2,...)

In this example:

• RANDBETWEEN function provides the Index_num value
• Text values for gender options are typed in formula, with double quote marks around each value.

Here is the completed formula, in cell D2, for the first random gender text selection:

• =CHOOSE(RANDBETWEEN(1,2),"Male","Female")

Fill that formula down in column D, to the end of the data rows

Choose Random Text - Cell References in Formula

In the Region column, another formula combines CHOOSE and RANDBETWEEN

• There are 3 regions, so RANDBETWEEN uses 1 as bottom value and 3 as top value.
• Instead of typing region names in the formula, absolute references to cells with region names are used
• Cell references are a better choice if the text values are lengthy, or if the text values will change frequently.

Here is the completed formula, in cell C2, for the first random gender text selection:

• =CHOOSE(RANDBETWEEN(1,3),$K$4,$K$5,$K$6)

Fill that formula down in column C, to the end of the data rows

Lock Results with Paste As Values

As a final step in setting up random data, for RAND or RANDBETWEEN, you can lock in the results -- copy the formula cells, and paste them as values.

By doing this step, you will lock in the random values, so the volatile RAND and RANDBETWEEN functions don't keep changing their results, every time Excel calculates.

These are the steps to copy the formula results, and paste as values, using a mouse shortcut.

• Use the mouse to select the cells that you want to change to values
• Point to the border of the selected range of cells
• Drag to the right, while pressing the RIGHT mouse button.
• Drag the column back to its original location, and let go of the mouse button.
• Then, in the popup menu, click on Copy here as Values Only

This very short video shows the steps.

• Note: If you prefer, you could use keyboard shortcuts to copy, the Paste Special, Values.

Choose Random Names from List

Choose random names from a list in Excel, using the RAND function, along with other Excel functions.

The sample file has 2 Name Chooser examples:

1. Chooser365 - For Excel 365 - Spill functions in cells D3 and E3 list numbers and names
2. ChooserALL - For all Excel versions - INDEX/MATCH functions, limit of 10 names

IMPORTANT: Every time the worksheet calculates, the list of randomly-chosen names will update automatically. If you want to save the list of names that were selected, be sure to copy the current names, and paste them as values, somewhere else.

Name List

In the sample file, there is a named Excel table, tblNames, with 2 columns: Name, Rand

• Names - Type names in this column, in order. You can sort the names, or leave them as is
• Rand - Has a formula to create a random number: =RAND()

Both columns have been named, and you can see the name details in the Name Manager, on Excel's Formulas tab

• NameList - Names column in tblNames
• RandList - Rand column in tblNames

These names are used in formulas on the name chooser sheets

Name Chooser 1 - Chooser365

This name chooser can be used in Excel 365, or other versions that support Excel's new spill functions.

There are 3 formula cells on the worksheet:

• B4: Number Limits - Shows minimum and maximum numbers that can be entered
• D3: Name Count - lists numbers for the chosen names
• E3: Names - list of chosen names

Enter a Number

To see a list of random names, type a number in cell B3

• In cell B4, a formula shows the minimum and maximum numbers that can be entered.
  • ="1 to " & COUNTA(NameList)
• The maximum is a count of the names in the NameList range.
• There is a data validation rule in cell B3, with the same minimum and maximum settings

Randomly-Chosen Names List

After a number is entered in cell B3, the randomly-chosen names are listed in column E, with a number beside each name, in column D.

Number List

The SEQUENCE formula in cell D3 creates a list of numbers for the names, based on the number in cell B3.

• =IFERROR(SEQUENCE(B3),"--")

If something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

Name List

This formula in cell E3 creates the random list of names.

• =IFERROR(FILTER(NameList,RandList>=LARGE(RandList,B3)),"--")

Here's how the formula works:

• First, the LARGE function finds the nth largest number in the RandList range, based on the number in cell B3.
  • In this example, that number is 3, so it will return the 3rd largest number in the RandList range.
• Next, the FILTER function returns names from the NameList range, where the Rand number is greater than or equal to the nth largest Rand number.
• Finally, if something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

Save the Names

IMPORTANT: Every time the worksheet calculates, the list of randomly-chosen names will update automatically.

If you want to save the list of names that were selected, copy the names, and paste them as values, somewhere else.

Copy and Paste as Values

Follow these steps to save the current list of randomly-chosen names:

• Select the cells with numbers and names, and press Ctrl+C, to copy them
• Go to another sheet, and right-click a cell where you want to paste the list
• Then, in the popup menu, under Paste Options, click on Values (V)

Name Chooser 2 - ChooserALL

This name chooser can be used in any version of Excel - it does NOT use the new spill functions.

There are 2 formula ranges on the worksheet:

• D3:D12: Name Count - lists numbers for the chosen names
• E3:E12: Names - list of chosen names

Enter a Number

To see a list of random names, type a number in cell B3

• The worksheet is set up to show a maximum of 10 selected names
• There is a data validation rule in cell B3, for whole numbers between 1 and 10
• That rule is shown in cell B4, for information only

Randomly-Chosen Names List

After a number is entered in cell B3, the randomly-chosen names are listed in column E, with a number beside each name, in column D.

Number List

This formula is entered in cell D3, and copied down to cell D12. These formulas create a list of numbers for the names, based on the number in cell B3.

• =IF(MAX(D$2:D2)=$B$3,"",SUM(D2,1))

If any number in the preceding rows is equal to the number in cell B3, the remaining cells show an empty string ("").

If something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

Name List

This formula, entered in cell E3 and copied down to E12, creates the random list of names.

• =IF(D3="","",INDEX(NameList, MATCH(LARGE(RandList,D3), RandList,0)))

Here's how the formula works:

• First, the LARGE function finds the nth largest number in the RandList range, based on the number in column D, in each row.
  • In cell E3, that would be the largest Rand number, and in E4, it's the 2nd largest, and so on
• Next, the MATCH function returns a row number, within the RandList range, where that Rand number is found.
• Then, the INDEX function returns the name from that row number within the NameList range
• Finally, if something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

Save the Names

IMPORTANT: Every time the worksheet calculates, the list of randomly-chosen names will update automatically.

If you want to save the list of names that were selected, copy the names, and paste them as values, somewhere else.

Copy and Paste as Values

Follow these steps to save the current list of randomly-chosen names:

• Select the cells with numbers and names, and press Ctrl+C, to copy them
• Go to another sheet, and right-click a cell where you want to paste the list
• Then, in the popup menu, under Paste Options, click on Values (V)

Download the Sample Files

RANDBETWEEN: Download the workbook with the Excel RANDBETWEEN Function examples. The zipped file is in xlsx format, with no macros.

Random Name Selection: Download the workbook with the Excel Random Name Chooser examples. The formulas on the Chooser365 sheet work in Excel 365, or other versions with the new spill functions. For other versions of Excel, use the ChooserALL sheet. The zipped file is in xlsx format, with no macros.

Related Links

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Functions

INDIRECT Function