How to Generate Random Numbers With Excel's RAND Function

When you need random numbers, RAND's the way to go

One way to generate random numbers in Excel is with the RAND function.

By itself, the function generates a limited range of random numbers, but by using RAND in formulas with other functions, the range of values can easily be expanded so that:

  • By specifying the high and low values of a  range, RAND can be made to return random numbers within a specified range, such as 1 and 10 or 1 and 100.​
  • The function's output can be reduced to integers by combining the function with the TRUNC function, which truncates or removes all decimal places from a number.

The RAND function returns an evenly distributed number greater than or equal to 0 and less than 1. While it is normal to describe the range of values generated by the function as being from 0 to 1, in reality, it is more exact to say the range is between 0 and 0.99999999...

The formula that returns a random number between 1 and 10 actually returns a value between 0 and 9.999999...

The RAND Function's Syntax

Generate Random Numbers with the RAND Function

Ted French

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the RAND function is:

= RAND ( )

Unlike the RANDBETWEEN function, which requires high-end and low-end arguments to be specified, the RAND function accepts no arguments.

RAND Function Usage

Several RAND function examples were used to produce the results shown in the image accompanying this article. 

  • The first example enters the RAND function by itself.
  • The second example creates a formula that generates a random number between 1 and 10 or 1 and 100.
  • The third example generates a random integer between 1 and 10 using the TRUNC function.
  • The last example uses the ROUND function to reduce the number of decimal places for random numbers.

Example 1: Entering the RAND Function

Since the RAND function takes no arguments, it can easily be entered into any worksheet cell simply by clicking on a cell and typing:

= RAND ( )

and pressing the Enter key on the keyboard. The result is a random number between 0 and 1 in the cell.

Example 2: Generating Random Numbers Between 1 and 10 or 1 and 100

The general form of the equation used to generate a random number within a specified range is:

=RAND() * (High - Low) + Low

where High and Low signify the upper and lower limits of the desired range of numbers.

To generate a random number between 1 and 10 enter the following formula into a worksheet cell:

=RAND() * (10 - 1) + 1

To generate a random number between 1 and 100 enter the following formula into a worksheet cell:

=RAND() * (100 - 1) + 1

Example 3: Generating Random Integers Between 1 and 10

To return an integer — a whole number with no decimal portion — the general form of the equation is:

=TRUNC ( RAND() *(High - Low) + Low)

To generate a random integer between 1 and 10 enter the following formula into a worksheet cell:

=TRUNC (RAND() * (10 - 1) + 1)

Example 4: RAND and ROUND: Reduce Decimals Places

Rather than remove all decimal places with the TRUNC function, the last example uses the following ROUND function in conjunction with RAND to reduce the number of decimal places in the random number to two.


The RAND Function and Volatility

The RAND function is one of Excel's volatile functions. This means that:

  • The function recalculates and produces a new random number every time the worksheet changes, including actions such as adding new data.
  • Any formula that depends either directly or indirectly on a cell containing a volatile function also recalculates every time a change in the worksheet occurs.
  • In worksheets or workbooks containing large amounts of data, volatile functions should be used with caution because they can slow down the program's response time due to the frequency of recalculations.

Start and Stop Random Number Generation With F9

Forcing the RAND function to produce new random numbers without making other changes to a worksheet can be accomplished by pressing the F9 key on the keyboard. This forces the entire worksheet to recalculate including any cells containing the RAND function.

The F9 key can also be used to prevent a random number from changing every time a change is made to the worksheet:

  1. Click on a worksheet cell where the random number is to reside.
  2. Type the function =RAND() into the formula bar above the worksheet.
  3. Press the F9 key to change the RAND function into a static random number.
  4. Press the Enter key on the keyboard to enter the random number into the selected cell.

Pressing F9 again will have no effect on the random number.

The RAND Function Dialog Box

Almost all functions in Excel can be entered using a dialog box rather than entering them manually.  To do so for the RAND function:

  1. Click on a cell in a worksheet where the function's results are to be displayed.
  2. Click on the Formulas tab of the ribbon.
  3. Choose Math & Trig from the ribbon to open the function drop-down list.
  4. Click on RAND in the list. The function's dialog box contains information that the function takes no arguments.
  5. Click OK to close the dialog box and return to the worksheet. A random number between 0 and 1 should appear in the current cell.
  6. To generate another random number, press the F9 key on the keyboard.

The RAND Function in Microsoft Word and PowerPoint

The RAND function can also be used in other Microsoft Office programs, such as Word and PowerPoint, to add random paragraphs of data to a document or presentation. One possible use for this feature is as filler content in templates. To use this feature, enter the function the same way in these other programs as in Excel:

  1. Click with the mouse in the location where the text is to be added.
  2. Type =RAND().
  3. Press the Enter key on the keyboard.

The number of paragraphs of random text varies depending on the version of the program used. For example, Word 2013 generates five paragraphs of text by default, while Word 2010 generates only three.

To control the amount of text produced, enter the number of desired paragraphs as an argument between the empty brackets. For example,


generates seven paragraphs of text in the chosen location.