Updated July 5, 2023
Introduction to Random Numbers in Excel
Generating random numbers in Excel is similar to rolling a die in Excel. Like a physical die, Microsoft Excel can generate random numbers in Excel that you can utilize for various purposes.
Excel has special functions called RAND and RANDBETWEEN that let you quickly generate random numbers. The RAND function gives you a decimal between 0 and 1, like 0.24 or 0.89. The RANDBETWEEN function gives you integers (whole numbers) between a specific range, like 24, 26, or 29.
Random numbers generated in Excel can help solve mathematical problems, simulate real-life scenarios, select random samples for analysis, and enhance encryption and security systems.
How to Generate Random Numbers in Excel?
Let us understand how to generate a random number in Excel with the help of some examples.
Method 1: Using RAND Function in Excel
The RAND is a built-in function in Excel that generates a random decimal/ fractional number between 0 and 1.
Syntax::
The function RAND requires no arguments
Example #1: Generate Random Numbers in Excel Between 0 and 1.
To generate random numbers in Excel between 0 and 1, follow these steps:
Solution:
Step 1: In an empty cell (cell A6), enter the formula =RAND().
Step 2: Press Enter to get the random number
Method 2: Using RANDBETWEEN Function in Excel
The RANDBETWEEN is a built-in function in Excel that generates a random integer between two given values.
Syntax:
“bottom” is the minimum value, and “top” is the maximum value of the range you want to generate random numbers between.
Example #2: Generate Random Numbers in Excel Between Specified Values
Suppose we want to create sample sales data with values between 100 and 200 to strategize marketing campaigns using the RANDBETWEEN function.
Solution:
Step 1: Create a data table with the customer ID and months for which you want to create sample sales data.
Step 2: Select the range of cells where you want to display the random numbers.
Step 3: Type the formula =RANDBETWEEN(100,200)
Step 4: Press CTRL + ENTER keys to see the result
Step 5: The formula yields the random numbers between 100 and 200, as shown in the image below
Method 3: Using INDEX and RANDBETWEEN Functions Together
We can use the INDEX and RANDBETWEEN functions to randomly select a value from a list or range of cells. Let’s see an example to understand it:
Example #3
Suppose we have a list of contestants and want to choose a random winner. We shall use the INDEX and RANDBETWEEN functions to achieve this goal.
Solution:
Step 1: In an empty cell (cell C6), enter the formula:
=INDEX(A6:A17,RANDBETWEEN(2,13))
Formula Explanation:
- The RANDBETWEEN will choose a random integer between 2 and 13 and pass the result to the INDEX function.
- The INDEX function treats the integer as the row number and returns the corresponding data (here, the winner’s name).
Step 2: Hit the Enter key to see the winner’s name
How to Activate the Random Number Generation Feature?
In some scenarios, we want to use the same random numbers repeatedly; therefore, we activate the Random Number Generation feature in Excel to save time and effort.
To activate the feature, follow these steps:
Step 1: Open an Excel worksheet and go to File
Step 2: Click More and go to Options
An Excel Options window opens up
Step 3: Click Add-ins > Analysis Toolpak > Go.
An add-Ins window opens up
Step 4: Select Analysis Toolpak and click OK.
It activates the Random Number Generation feature in Excel.
Step 5: Access the Data tab in the Excel toolbar to access the feature and click Data Analysis.
It opens a Data Analysis window
Step 6: Choose Random Number Generation and click OK.
It opens a Random Number Generation window consisting of seven types of distribution, and the kind of distribution you choose depends on your data type.
Step 7: Here, we choose the Uniform distribution.
Step 8: The following parameters must be specified while using this functionality:
- Number of Variables, as this would decide the number of columns used to display the result.
- Several Random Numbers we wish to generate.
- The distribution type we want to have for the numbers. It appears as a drop-down menu:
- For Uniform distribution, we specify upper and lower limits as shown:
We may use Random Seed, which keeps the data centered on entered values. It helps generate the same set of numbers later.
- You must specify the Output Range so Excel knows where to place the result.
Step 9: Click OK.
It displays the result as shown below.
Difference Between RAND AND RANDBETWEEN in Excel
|
Parameters |
RAND Function |
RANDBETWEEN Function |
| Type of value | It generates a decimal number (e.g., 0.54 ) | It generates an integer
(e.g., 54) |
| Range of values | Between 0 and 1 | Between specified values |
| Syntax | =RAND() | =RANDBETWEEN(bottom,top) |
| Application | You can use it to produce random numbers for simulations. | You can use it to create randomized samples of data for analysis. |
| Limitation |
|
|
Things to Remember for Generating Random Numbers in Excel
1. To prevent the random numbers from changing each time Excel recalculates a cell, convert them to static values using the paste special feature in Excel.
Example: Suppose you have a random number generated using the formula “=RAND()” in cell D1. To convert it to a static value, select cell D1, copy it (Ctrl+C), then use “Paste Special” and choose “Values” to paste the static value.
2. The likelihood of getting repeated random numbers using the RAND function is low, while that of RANDBETWEEN is high.
Example: If the RAND function generates five random decimal numbers, it will only give repeated values if you recalculate the worksheet. However, if the RANDBETWEEN function generates eight random whole numbers between 1 and 20, it may provide repeated numbers.
Frequently Asked Questions (FAQs)
Q1. How do I generate random numbers from 1 to 100 in Excel?
Answer: To generate random integer numbers from 1 to 100, follow these steps:
Step 1: Choose the cell range to display the generated numbers.
Step 2: Without pressing any key, type the formula =RANDBETWEEN(1,100).
Step 3: Press “Ctrl” + “Enter” to generate the random numbers.
Q2. What is the formula for random numbers?
Answer: To generate random numbers in Excel (specifically, decimal numbers), you can use the formula =RAND()(b-a)+a. Let’s say you want a random number between 20 and 50. Use the formula =RAND()(50-20)+20, and Excel will give you a random decimal number within that range.
Q3. What are the disadvantages of Randbetween?
Answer: The disadvantages of using the RANDBETWEEN function are:
- Generates only integer values: The RANDBETWEEN function can generate only integer values within the specified range; therefore, it is not helpful for situations requiring decimal values within a specified range.
- No option to exclude specific values: The RANDBETWEEN function does not allow users to indicate values that are not required.
- Potential for Duplicate values: When you use the RANDBETWEEN function to generate many duplicate values, there is a high probability of identical random values, making it unsuitable for situations where you need unique random values.
Q4. Is Randbetween inclusive or exclusive in Excel?
Answer: The RANDBETWEEN function is inclusive, including both the specified range’s lower and upper boundaries. For example, if you use the RANDBETWEEN function to generate random numbers in Excel between 1 and 10, it will consider both (1 and 10) numbers in the range of possible random integers.
Recommended Articles
The above article provides a comprehensive guide on generating random numbers in Excel, including practical examples and a downloadable Excel template. For additional resources, please refer to our other recommended articles-
