Random Numbers in Excel

Updated on December 31, 2023
Article byBabita Sehdev
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

What Are Random Numbers In Excel?

Random numbers in Excel are used when we want to randomize our data for a sample evaluation. These numbers generated randomly. There are two built-in functions in Excel which give us random values in cells, RAND and RANDBETWEEN functions.

RAND() function provides us with any value from the range 0 to 1, whereas =RANDBETWEEN() takes input from the user for an arbitrary number range.

Randomness has many uses in science, art, statistics, cryptography, and other fields.

Generating Random numbers in excel is important because many things in real life are so complicated that they appear random. Therefore, to simulate those processes, we need random numbers.

For instance, we can generate a random number in excel using =RAND().

  • Step 1: Insert the =RAND() in cell A1.
Random Numbers in Excel - 1
  • Step 2: Press Enter.
Random Numbers in Excel - 2

Likewise, we can generate random numbers using random numbers in excel.

Key Takeaways

  • Random Numbers in excel is used to generate numbers in excel.
  • The Random numbers in Excel function help in generating random data for sample evaluation.
  • Remember, the Random Numbers in excel is a worksheet function (WS function)
  • Excel has two built-in Random functions which yield random values. They are =RAND() function and =RANDBETWEEN().
  • The =RAND() provides us with any value from the range 0 to 1, whereas the =RANDBETWEEN() function takes input from the user for an arbitrary number range.
Random Numbers in Excel - 3

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Random Numbers in Excel (wallstreetmojo.com)

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Generate Random Numbers In Excel?

There are several methods to generate a random number in Excel. We will discuss the two of them: RAND() and RANDBETWEEN() functions.

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

#1 – RAND() Function

To generate a random number in Excel between 0 and 1(excluding), we have a RAND() function in Excel.

RAND() functions return a random decimal number that is equal to or greater than 0 but less than 1 (0 ≤ random number <1). In addition, the RAND function recalculates when a worksheet is opened or changed (volatile function).

The RAND function returns the value between 0 and 1 (excluding).

random numbers

We must type =RAND() in the cell and press Enter. The value will change whenever any change is made in the sheet.

How To Generate Random Numbers In Excel For More Than One Cell?

If we want to generate random numbers in Excel for more than one cell, then we need:

  • First, select the required range, type =RAND(), and press Ctrl+Enter to give us the values.
random numbers 1

How to Stop Recalculation of Random Numbers in Excel?

As the RAND function recalculates, if any change in the sheet is made, we must copy and then paste the formulas as values if we do not want values to be changed every time. For this, we need to paste the values of the RAND() function using Paste Special so that it would no longer be a result of the “RAND ()” function. It will not be recalculated.

To do this,

  • We need to make the selection of the values.
random numbers 2
  • Press the Ctrl+C keys to copy the values.
random numbers 3
  • Without changing the selection, press the Alt+Ctrl+V keys to open the Paste Special dialog box.
random numbers 4
  • Choose Values from the options and click on OK.
random numbers 6

We can see that the value in the formula bar is the value itself, not the RAND() function. Now, these are only values.

random numbers 5

There is one more way to get the value, only not the function as a result, but that is only for one cell. If we want the value the very first time, not the function, then the steps are:

  • First, type =RAND() in the formula bar, press F9, and press the Enter key.
random numbers 7

After pressing F9, we get the value only.

random numbers 8

Value From A Different Range Other Than 0 And 1 Using RAND()

As the RAND function returns a random decimal number between 0 and 1 only, if we want the value from a different range, then we can use the following function:

Let ‘a’ be the start point.

And ‘b’ be the end point

The function would be ‘RAND()*(b-a)+a’

For example, if we have 7 as “a” and 10 as “b,” then the formula would be “=RAND()*(10-7)+7.”

random numbers 9

RANDBETWEEN() Function

As the function’s name indicates, this function returns a random integer between given integers. Like the RAND() function, this function also recalculates when a workbook is changed or opened (volatile function).

The formula of the RANDBETWEEN Function is:

RANDBETWEEN Formula
  • Bottom: An integer representing the lower value of the range.
  • Top: An integer representing the lower value of the range.
random numbers 10

To generate random numbers in Excel for the students between 0 and 100, we will use the RANDBETWEEN function.

First, we must select the data, type the formula, =RANDBETWEEN(0,100), and press the Ctrl + Enter keys. You can prefer the below-given screenshot.

random numbers 11

As values will recalculate, we can only use Alt+Ctrl+V to open the Paste Special dialog box to paste as values.

Follow the steps given below in the screenshot.

random numbers 12

Like the RAND() function, we can also type the RANDBETWEEN function in the formula bar, pressing F9 to convert the result into value, and then press the Enter key.

Important Things To Remember

  • If the bottom is greater than the top, the RANDBETWEEN function will return #NUM! Error.
  • If either of the supplied arguments is non-numeric, the function will return #VALUE! Error.
  • Both RAND() and RANDBETWEEN() functions are volatile functions (recalculates). Hence, it increases the processing time and can slow down the workbook.

Random Numbers In Excel – Project 1

We can use the RANDBETWEEN() function for getting random dates between two dates.

We will be using two dates as the bottom and top arguments.

random numbers 13

After making the selection, we need to copy down the formula using the shortcut (Ctrl+D).

random numbers 14

We can change the start date (D1) and end date (E1) to change the top and bottom values for the function.

Random Numbers In Excel – Project 2 –  Head And Tail

To randomly choose head and tail, we can use the CHOOSE function in excel with the RANDBETWEEN function.

random numbers 16

We need to copy the formula into the next and next cell every time in the game, and Head and Tail will come randomly.

random numbers 15

Random Numbers In Excel – Project 3 – Region Allocation

Many times, we have to imagine and create data for various examples. For example, suppose we have data for sales, and we need to allocate three different regions to every sale transaction.

Then, we can use the RANDBETWEEN function with the CHOOSE function.

random numbers 17

We can drag the same for the remaining cells.

random numbers 18

Random Numbers In Excel – Project 4 – Creating Ludo Dice

Using the RANDBETWEEN function, we can also create dice for Ludo. We need to use the RANDBETWEEN function in Excel VBA for the same. Please follow the below steps:

  • Merge the four cells (B2:C3) using Home Tab → Alignment Group → Merge & Center.
random numbers 19
  • Apply the border to the merged cell using the shortcut key (ALT+H+B+T) by pressing one key after another.
random numbers 20
  • Center and middle align the value using Home Tab → Alignment Group → Center and Middle Align commands.
random numbers 21
  • To create the button, use Developer tab → Controls Group → Insert Command Button
random numbers 22
  • Create the button and choose View Code from the Controls group on Developer.
random numbers 24
  • After choosing CommandButton1 from the dropdown, paste the following code:

RN = (“=RANDBETWEEN(1,6)”)

Cells(2, 2) = RN

random numbers 23

Please save the file using the .xlsm extension as we have used the VBA code in the workbook. After coming to the Excel window, deactivate the Design Mode.

random numbers 25

Now, whenever we click the button, we get a random value between 1 and 6.

random numbers 26

Frequently Asked Questions (FAQs)

1. What is Random Numbers in excel?

Random Numbers in excel is a built-in function used to generate random values. It is used when we have to calculate sample evaluation for random values.

2. What are the two functions used to generate random numbers in excel?

In excel, we can generate numbers using two functions. They are: =RAND() and =RANDBETWEEN().

The =RAND() provides us with any value from the range 0 to 1, whereas the =RANDBETWEEN() function takes input from the user for an arbitrary number range.

3. How to use RAND function in excel?

RAND() generates random numbers between 0 to 1.

For example, consider the below image where we have inserted the RAND() in a blank cell.

Random Numbers in Excel - FAQ 3

As soon as we press Enter¸ we can see that the function has generated a random number below 1 as shown in the below image.

FAQ 3 - 1

Similarly, we can generate random numbers between 0 to 1, using random numbers in excel function.

This article is a guide to Random Numbers in Excel. Here, we discuss generating random numbers in Excel using the RAND() and RANDBETWEEN functions along with multiple projects and downloadable Excel templates. You may also look at these useful functions in Excel: –