Home > Formulas > Math Trig > Random

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.

randbetween function 04

RAND Function vs RANDBETWEEN Function

There are two Microsoft Excel functions that create random numbers -- RAND and RANDBETWEEN. Both random functions are 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 example in the RAND Function Examples 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 example in the RANDBETWEEN Function section below

RAND Function

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

randbetween function 02

RAND Syntax

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

  • =RAND()

RAND Result

The result of a RAND formula will be a number that is:

  • greater than or equal to zero
    • AND
  • less than 1

RAND Details

For details on the RAND function algorithm, and the function's volatility, go to the More About RAND Function section, further down this page.

RAND - Choose Winning Ticket Number

A while ago, someone asked me how to pick a winner in a spring raffle, using Excel. I suggested that they use the RAND function to pick a random winner.

  • First, create a list of names or ticket numbers
  • Next, put a RAND formula in the next column.
  • Finally, sort by the RAND column, and the winner is the name/number at the top.

This short video shows the steps.

Also see the additional RAND and RANDBETWEEN Examples

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:

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.

randbetween function 02

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 - 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.

Also see the additional RAND and RANDBETWEEN Examples

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 steps in the Stop Random Results from Changing section further down this page.

custom list dialog box

Stop Random Results from Changing

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

This very short video shows the steps, and there are written steps below the video.

Change Formulas to Values

Follow the steps below, 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
  • Note: If you prefer, you could use keyboard shortcuts to copy (Ctrl+C), then Paste Special, Values (Alt+H, V, V).

Benefits to Pasting as Values

The RAND and RANDBETWEEN functions are volatile, so they recalculate every time Excel recalculates. This could slow down an Excel workbook, in some situations.

Here are two benefits of changing the formuls to values, after you create a set of random numbers:

  1. Numbers will stay the same, instead of changing every time the workbook is calculated
  2. Workbook calculation time could be reduced

More About RAND Function

Here are more details on the Excel RAND function, and how it works.

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.

More RAND and RANDBETWEEN Examples

Here are more RAND and RANDBETWEEN function examples, that go beyond the simple examples shown above.

-- A) Random Task Assignment

-- B) Create Random Text and Numbers

Also see this RAND example: Choose Random Names

A) 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 to do the tasks, and 25 numbered tasks to be assigned
  • Each person will be assigned 5 random task numbers to complete

Step 1) 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.

type list of names

  • 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 a list of numbers

Step 2) 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

fill down to row 25

Step 3) 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

create RAND formula

  • 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

list or random decimal numbers

Step 4) 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

sort random numbers

Step 5) 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 25

names with randomly assigned numbers

B) 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.

----B1) Create Random Numbers for Test Score Data

----B2) Create Random Text for Test Score Data

B1) 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

randbetween function 02

B2) 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 sections below.

Step 1) 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

randbetween function 04

Step 2) 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

randbetween function 05

Get the Sample File

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

Related Links

Random Name Chooser

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Functions

INDIRECT Function

 

 

 

Last updated: February 19, 2024 3:09 PM