EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Random Numbers in Excel

Random Numbers in Excel

Madhuri Thakur
Article byMadhuri Thakur

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Generate Random Numbers in Excel

How to Generate Random Numbers in Excel?

Let us understand how to generate a random number in Excel with the help of some examples.

You can download this Generate Random Number Excel Template here – Generate Random Number Excel Template

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::
RAND Function in Excel 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().
Generate Random Numbers in Excel RAND 1.1

Step 2: Press Enter to get the random number
Generate Random Numbers in Excel RAND 1.2

Note: When we apply the formula repeatedly to the same cell, Excel generates a new random number each time.

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:
RANDBETWEEN Function in Excel 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.
RANDBETWEEN Function in Excel step 1

Step 2: Select the range of cells where you want to display the random numbers.
Step 3: Type the formula =RANDBETWEEN(100,200)
RANDBETWEEN Function in Excel step 3

Step 4: Press CTRL + ENTER keys to see the result
generate random numbers in excel step 4

Step 5: The formula yields the random numbers between 100 and 200, as shown in the image below
generate random numbers in excel- step 5

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.
Example #3

Solution:
Step 1: In an empty cell (cell C6), enter the formula:
=INDEX(A6:A17,RANDBETWEEN(2,13))
generate random numbers in excel- Example #3.1

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).
Note: If you add or delete a name from the list, you must update the range in the formula accordingly.

Step 2: Hit the Enter key to see the winner’s name
generate random numbers in excel-Example #3.2

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
generate random numbers in excel 111

Step 2: Click More and go to Options
Random Number Generation 2
An Excel Options window opens up
Step 3: Click Add-ins > Analysis Toolpak > Go.
Random Number Generation 3
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.
Data Analysis
It opens a Data Analysis window
Step 6: Choose Random Number Generation and click OK.
Random Number Generation 6
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.
Random Number Generation 6.
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:

generate random numbers in excel-8

  • For Uniform distribution, we specify upper and lower limits as shown:

Random Number Generation 8.
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.
Random Number Generation 9

It displays the result as shown below.
Random Number Generation 9.

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
  1. Can generate random numbers in Excel between 0 and 1 only.
  2. It produces pseudo-random numbers, which are not indeed random.
  1. It generates discrete integers, therefore, unsuitable for applications requiring a continuous range of values.
  2. This function generates pseudo-random numbers.

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.
generate random numbers from 1 to 100 in Excel

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.
generate random numbers in excel-formula for random numbers

Note: The random number will change each time Excel recalculates the cell or if there is an activity in the worksheet.

Q3. What are the disadvantages of Randbetween?
Answer: The disadvantages of using the RANDBETWEEN function are:

  1. 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.
  2. No option to exclude specific values: The RANDBETWEEN function does not allow users to indicate values that are not required.
  3. 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-

  1. VLOOKUP Function in EXCEL
  2. Excel Match Function
  3. Divide in Excel Formula
  4. Text to Columns in Excel
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Generate Random Number Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Generate Random Number Excel Template

EDUCBA

डाउनलोड Generate Random Number Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more