Contextures

Excel RAND and RANDBETWEEN Functions

These examples show how to use the RAND and RANDBETWEEN function to create random numbers or random text in Excel

RAND Function

Use the RAND function to create random numbers in Excel. Then, use a column of random numbers for sorting or extracting records in a table.

To create a random number in a cell, use this formula:

=RAND()

Random Assignment

To randomly assign numbers or tasks to a group of people, use the RAND function. In this example, there are 5 people, and the numbers 1 to 25.

  • Starting in cell A1, type the list of people's names.
  • Select all the names, and point to the fill handle, in the bottom right corner.

type list of names

  • When the pointer changes to a black plus sign, drag down to row 25. This will repeat the name list down the column.

create a list of numbers

  • Leave column B blank
  • Starting in cell C1, create the list of numbers or tasks to be randomly assigned.
  • NOTE: For a sequential list of numbers, type the first two numbers, then select those tw0 numbers, and drag down to row 25.

fill down to row 25

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

create RAND formula

  • Fill the RAND formula down to row 25
  • Then, right-click on any number in D1:D25, and in the popup menu click Sort, then click Sort Smallest to Largest

sort random numbers

  • This sorts the list of 1 to 25 in random order
  • Finally, delete column D (the RAND formulas), and delete column B (the blank column)
  • Each name has a randomly assigned number, from the list of 1 to 25

names with randomly assigned numbres

Create Random Numbers

See how to quickly create test data with month headings, customer numbers, and random numbers, then change the formulas to static values.

Written steps are on the Data Entry Tips page.

Create Random Text and Numbers

Use the RANDBETWEEN function to create random numbers 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.

RANDBETWEEN for Random Numbers

If you give the RANDBETWEEN function a bottom number, and a top number, it will return a random number within that range.

For example, in the screen shot below, we are creating a set of scores for records in a sample data file.The lowest score should be 30, and the maximum is 100, so this is the formula in cell H2:

=RANDBETWEEN(30,100)

randbetween function 02

Copy the formula down, in column H, to fill in the remaining scores. go to top

RANDBETWEEN for Random Text - List

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, we are trying to fill the Gender column, in a sample data file, that will be used for testing. For Gender, there are 2 values two values -- Male and Female -- so the RANDBETWEEN function uses 1 as the bottom value and 2 as the top.

For the Value arguments, each gender is entered, with double quote marks around each value.

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

randbetween function 04

Use Cell References as CHOOSE Values

In this example, the RANDBETWEEN function will be combined with CHOOSE, to return a random Region name in each row of a sample data workbook.

There are 3 regions, so the RANDBETWEEN function uses 1 as the bottom value and 3 as the top.

Instead of typing the region names, you can use absolute references to the cells which contained the region names. Cell references are a better choice if the values are long, or if they'll change frequently.

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

randbetween function 05

Paste As Values

The final step in setting up the random data is to copy the columns, and paste them as values. That will lock in the values, so they don't keep changing, as you work in the file.

  • Use the mouse to select the columns, then 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

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 list for random selection

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

enter a number within the limits

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.

formula for number sequence

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.

random list of names

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)

copy names and paste as values

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

enter a number within the limits

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.

formula for number sequence

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.

random list of names

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)

copy names and paste as values

Get the Sample Files

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

RANDOM NAMES: Get 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

Last updated: October 27, 2021 2:38 PM