Home > Formulas > Math Trig > Random Excel RAND and RANDBETWEEN FunctionsThese 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 FunctionThere 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
See details and example in the RAND Function Examples section below RANDBETWEEN Function
See details and example in the RANDBETWEEN Function section below 
RAND FunctionUse the RAND function to create random decimal numbers in Microsoft Excel. RAND SyntaxThe RAND function syntax does not require any arguments. Simply type the open and close brackets, with nothing between them.
RAND ResultThe result of a RAND formula will be a number that is:
RAND DetailsFor details on the RAND function algorithm, and the function's volatility, go to the More About RAND Function section, further down this page. 
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.
This short video shows the steps.
Also see the additional RAND and RANDBETWEEN Examples
RANDBETWEEN FunctionAnother 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.
Tip: Because RANDBETWEEN returns numbers, you could also use it to return a list of random dates. Excel stores dates as serial numbers. RANDBETWEEN SyntaxThe RANDBETWEEN function syntax has two arguments, and both arguments are required:
The result will be a whole number (integer) within the specified range. Volatile FunctionThe 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 DataIf 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 DataIn 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:
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 ChangingAfter 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. 
Follow the steps below, to copy the formula results, and paste as values, using a mouse shortcut.
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:
Here are more details on the Excel RAND function, and how it works.
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.
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.
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
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:
Follow these steps, to create the list of people, with each person's name repeated 5 times
Step 2) Create List of Task NumbersNext, follow these steps, to create a list of 25 task numbers.
Step 3) Create List of Random NumbersNext, follow these steps, to create a list of 25 random numbers, beside the task numbers.
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
Note: Some of the results might have a different number of decimal places 
Step 4) Sort Task Numbers in Random OrderNext, follow these steps, to sort the 25 task numbers in random order.
Step 5) Match Task List to NamesNow that the task number list has been sorted in random order, follow these steps to assign the tasks to the list of names
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 NumbersIn 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 NumbersIn 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:
To create the first random number, enter this formula in cell H2:
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 DataThe 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 FormulaTo 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:
To choose a gender numer at random, we can use the RANDBETWEEN function, with

Excel CHOOSE FunctionNext, 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:
In this example:
Here is the completed formula, in cell D2, for the first random gender text selection:
Fill that formula down in column D, to the end of the data rows Step 2) Choose Random Text  Cell References in FormulaIn the Region column, another formula combines CHOOSE and RANDBETWEEN
Here is the completed formula, in cell C2, for the first random gender text selection:
Fill that formula down in column C, to the end of the data rows 
Get the Sample FileRANDBETWEEN: Download the workbook with the Excel RANDBETWEEN Function examples. The zipped file is in xlsx format, with no macros. 
Related Links 
Last updated: February 19, 2024 3:09 PM