RAND Excel Function

What Is RAND Function In Excel?

The RAND function in Excel creates evenly distributed random real numbers. These generated random values are between 0 and 1, so the values range is greater than or equal to 0 and less than 1.  

The RAND Excel Function is a Volatile function. So, the values are recalculated every time the worksheet is opened or modified, or the calculation is prompted. As a result, the random numbers generated in the selected cells are unique and are not repeated in the same selection.

For example, we apply the RAND Function to calculate random numbers in cell A1.

Enter the formula =RAND(), and press the “Enter” key.

RAND Excel Function Intro

The output is “0.7658”, as shown above. A random number is generated between 0 and 1 without getting any input in the formula.

Key Takeaways
  • The RAND Excel function generates random numbers from 0 to 1. It can also generate random percentages from 0% to 100%.
  • Since it is a Volatile function, it generates a new value each time the worksheet is opened, or the formula is triggered.
  • To calculate the random numbers between two numbers, we must use the following RAND formula,=a+ (b-a)*RAND ()”. [a and b can be any integers].
  • We can use the RAND Function and change the output cell format to “Time” to generate random time, just like random numbers.

RAND() Excel Formula

The Syntax of the RAND Excel formula is,

RAND syntax

The formula does not have any arguments.

How To Use RAND Excel Function?

The RAND Excel function can be used in 2 methods, namely,

  1. Access from the Excel ribbon.
  2. Enter the worksheet manually.

#1 – Access from the Excel ribbon

Choose an empty cell for the result > select the “Formulas” tab > go to the “Function Library” group > click the “Math & Trig” drop-down > select the “Rand” function option, as shown below.

How to Use

The RAND Excel formula is inserted in the cell, and the “Function Arguments” window instantly appears, as shown below. Since this function has no arguments, click “OK.

RAND Excel Function How to Use.1

#2 – Enter the worksheet manually

  1. Select an empty cell for the output.
  2. Type =RAND() in the selected cell. [Alternatively, type “=R” and double-click the RAND function from the list of suggestions shown by Excel].
  3. Press the “Enter” key.

# Basic Example

We will generate random numbers using the RAND Excel formula.

In the table, the data is,

  • Column A contains the Output to be calculated.
  • Column B contains the Formula for our reference.
RAND Excel Function Basic Example

The steps to generate the random numbers using the RAND Function are as follows:

Step 1: Select cell A2, enter the formula =RAND() and press the “Enter” key. The output is ‘0.274’, as shown below.

RAND Excel Function Basic Example.1

Step 2: Drag the formula from cell A2 to cell A5 using the fill handle.

Basic Example.2

The output is shown above. The formulas displayed in Column B are for our reference.

[Note: The RAND function generates random numbers between 0 and 1, so the above example returns random numbers between 0 and 1].

Examples

We will understand the RAND in Excel with some advanced scenarios.

#1 Example

We will generate random numbers between 0 and 100 using RAND Excel Function.

In the table, the data is,

  • Column A contains the Output to be calculated.
  • Column B contains the Formula for our reference.
RAND Excel Function Example 1

The steps to generate the random numbers using the RAND in Excel are as follows:

Step 1: Select cell A2, enter the formula =100*RAND() and press the “Enter” key. The output is ‘52.150’, as shown below.

Example 1.1

Step 2: Drag the formula from cell A2 to cell A10 using the fill handle.

Example 1.2

The output is shown above. The formula =100*RAND() first generates the random numbers between 0 and 1, multiplies the result by 100, and returns the output between 0 and 100.

[Note: The RAND Excel function generates random numbers only between 0 and 1. However, we can build formulas like the above example and generate random numbers between different numbers].

#2 Example

We will generate the random number between 10 and 20 using RAND Function. In this example, the formula used is “=a+(b-a)*RAND()”. When we enter the two values, 10 and 20, the formula will then be “=10+(20-10)*RAND()”.

In the table, the data is,

  • Column A contains the Output to be calculated.
  • Column B contains the Formula for our reference.
RAND Excel Function Example 1

The steps to generate the random numbers using the RAND Excel function are as follows:

Step 1: Select cell A2, enter the formula =10+(20-10)*RAND() and press the “Enter” key. The output is ‘14.366’, as shown below.

RAND Excel Function Example 2.1

Step 2: Drag the formula from cell A2 to cell A10 using the fill handle.

Example 2.2

The output is shown above.

[Note: Using the formula =a+ (b-a)*RAND(), the RAND Excel Function generates the random number between two integers, 10 and 20, as seen in the above example]

#3 Example

We will generate random time using RAND Excel Function.

In the table, the data is,

  • Column A contains the Output to be calculated.
  • Column B contains the Formula for our reference.
RAND Excel Function Example 3

The steps to generate the random time using the RAND Excel function are as follows:

Step 1: First, we will set column A to “Time” format. Choose column A > select the “Home” tab > go to the “Number” group > click the “Number Format” drop-down > select the “Time” option, as shown below.

Example 3.1

Step 2: Enter the formula =RAND() in cell A2 and press the “Enter” key. The output is ‘06:34:05’, as shown below.

Example 3.2

Step 3: Drag the formula in cell A2 to cell A10 using the fill handle.

RAND Excel Function Example 3.3

The output is shown above in the “Time” format.

Important Things to Note

  • The RAND Excel function reduces the speed of the workbook due to consistent recalculation of the randomly generated values.
  • To generate a set of random numbers in multiple cells, instead of dragging the formula, first, select the cell range for the output, enter the RAND() formula, and then press the “CTRL+ENTER” keys in place of the “Enter” key.
  • To stop the auto-change in the RAND Excel function, click on the result that we do not want to change. Then, in the Formula Bar, press the F9 key [in some cases, Fn+F9] to convert the formula into its value.

Frequently Asked Questions

What is the purpose of the RAND function in Excel?

The purpose of the RAND function in Excel is as follows;

1. The RAND or RANDBETWEEN function in Excel generates a list of random numbers. It creates more values than we need, and we can delete some duplicates later.
2. We can convert formulas to values to prevent Excel random functions from recalculating.
3. The function removes duplicate values using Excel’s built-in tool or the advanced Duplicate Remover for Excel.

How does the RAND function work in Excel?


We can use the RAND function works in Excel as follows:

1. Select an empty cell to enter the Formula.
2. Type =RAND() in the selected cell. [Alternatively, type “=R” and double-click the RAND function from the list of suggestions shown by Excel].
3. Press the “Enter” key.

Let us generate random numbers using the RAND Excel Function for the following example, where

Column A contains the Output to be generated.

The procedure to use RAND Excel Function is,

Select cell A1, enter the formula =RAND(), and press the “Enter” key. The result in cell A1 is ‘0.4072’. Now, drag the formula to cell A2 using the fill handle. The output is shown below.

FAQ 1

Where is the RAND function in Excel?


We can find the RAND function using the following steps:

Choose an empty cell for the result > select the “Formulas” tab > go to the “Function Library” group > click the “Math & Trig” drop-down > select the “Rand” function option, as shown below.

How to Use

The RAND Excel formula is inserted in the cell, and the “Function Arguments” window instantly appears, as shown below. Since this function has no arguments, click “OK.

RAND Excel Function How to Use.1

Download Template

This article must help understand the RAND Excel Function’s formula and examples. You can download the template here to use it instantly.

This has been a guide to RAND Excel Function. Here we get random numbers from 0 to 1, other values by built formulas, examples & downloadable excel template. You can learn more from the following articles – 

Reader Interactions

Leave a Reply

Your email address will not be published.