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.
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:
See details and examples in the RAND Function section below
See details and examples in the RANDBETWEEN Function section below
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
Follow the steps in the Paste As Values section below, to lock in the random numbers.
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
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:
The result will be greater than or equal to zero, and less than 1
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.
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:
Each person will be assigned 5 random task numbers to complete
Follow these steps, to create the list of people, with each person's name repeated 5 times
Next, follow these steps, to create a list of 25 task numbers.
Next, 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
Next, follow these steps, to sort the 25 task numbers in random order.
Now 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 15
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.
Tip: Because RANDBETWEEN returns numbers, you could also use it to return a list of random dates. Excel stores dates as serial numbers.
The RANDBETWEEN function syntax has two arguments, and both arguments are required:
The result will be a whole number (integer) within the specified range.
The RANDBETWEEN function is volatile, and it recalculates its result every time Excel recalculates. This could slow down an Excel workbook, in some situations.
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.
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:
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.
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
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:
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
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
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:
To choose a gender numer at random, we can use the RANDBETWEEN function, with
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:
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
In 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
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.
This very short video shows the steps.
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:
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.
In the sample file, there is a named Excel table, tblNames, with 2 columns: Name, Rand
Both columns have been named, and you can see the name details in the Name Manager, on Excel's Formulas tab
These names are used in formulas on the name chooser sheets
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:
To see a list of random names, type a number in cell B3
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.
The SEQUENCE formula in cell D3 creates a list of numbers for the names, based on the number in cell B3.
If something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.
This formula in cell E3 creates the random list of names.
Here's how the formula works:
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.
Follow these steps to save the current list of randomly-chosen names:
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:
To see a list of random names, type a number in cell B3
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.
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 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.
This formula, entered in cell E3 and copied down to E12, creates the random list of names.
Here's how the formula works:
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.
Follow these steps to save the current list of randomly-chosen names:
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.
Last updated: April 1, 2022 10:42 AM