Home > Formulas > Math Trig > Random
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 vs RANDBETWEEN
RAND Function
-- Choose Winning Ticket
RANDBETWEEN Function
-- Create Test Sales Data
Stop Random Results from Changing
More About RAND Function
More RAND and RANDBETWEEN Examples
Get the Sample File
More Function Tutorials
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.
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.
- This section has details on the RANDBETWEEN function syntax, and its volatility.
- 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 - 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:
- In the screen shot below, cells B2:G7 are selected.
- Type the formula: =RANDBETWEEN(10,100)
- 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.
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:
- Numbers will stay the same, instead of changing every time the workbook is calculated
- 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 theMersenne 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.
- 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
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
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
- 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
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
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
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
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
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
