Use RANDBETWEEN to Create a Random Number Generator in Excel

Neon Numbers

Getty Images/Ivan McClellan

The RANDBETWEEN function can be used to generate random integers (whole numbers only) between a range of values in an Excel worksheet. The range for the random number is specified using the function's arguments.​

Whereas the more commonly used RAND function will return a decimal value between 0 and 1, RANDBETWEEN can generate an integer between any two defined values such as 0 and 10 or 1 and 100.

If you need to generate random numbers, including decimal values, use Excel's RAND function.

01
of 02

RANDBETWEEN Function Syntax and Arguments

Screenshot of Excel showing the RANDBETWEEN function

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 RANDBETWEEN function is:

= RANDBETWEEN ( Bottom, Top )
  • Bottom: (Required) The lowest possible integer the function is to return as a result. The actual integer can be entered for this argument or it can be a cell reference to the location of the data in the worksheet.
  • Top: (Required) The highest possible integer the function is to return as a result. The actual integer can be entered for this argument or it can be a cell reference to the location of the data in the worksheet.

#NUM! error: If the Bottom argument is a larger number than the Top argument, the RANDBETWEEN function will return a #NUM! error value in the cell where the function is located.

Screenshot of Excel showing the RAND and RANDBETWEEN functions

Like the RAND function, RANDBETWEEN is one of Excel's volatile functions — what this means is that:

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

Using Excel's RANDBETWEEN Function

Screenshot of Excel showing the RANDBETWEEN formula option

The steps listed below cover how to get the RANDBETWEEN function to return a random integer between 1 and 100.

=RANDBETWEEN (1,100)

Options for entering the function and its arguments include:

  1. Typing the complete function into a worksheet cell.
  2. Selecting the function and arguments using the Formula Builder.

Although it is possible to just type the complete function in by hand, many people find it easier to use the Formula Builder as it takes care of entering the function's syntax such as brackets and comma separators between arguments.

  1. Click on cell C3 to make it the active cell – the location where the RANDBETWEEN function will be located.
  2. Click on the Formulas tab of the ribbon.
  3. Click on the Math & Trig icon to open the Function drop-down list.
  4. Click on RANDBETWEEN in the list to open the Formula Builder

The data that will be entered into the blank rows in the dialog box will form the function's arguments.

Screenshot of Excel showing the Formula Builder

Entering the RANDBETWEEN Function's Arguments

  1. Click on Bottom line of the dialog box.
  2. Click on cell A3 in the worksheet to enter this cell reference into the dialog box.
  3. Click on Top line of the dialog box.
  4. Click on cell B3 in the worksheet to enter the second cell reference.
  5. Click OK to complete the function and return to the worksheet.
  6. A random number between 1 and 100 should appear in cell C3.
  7. To generate another random number, press the F9 key on the keyboard which causes the worksheet to recalculate.
  8. When you click on cell C3 the complete function =RANDBETWEEN ( A3, A3 ) appears in the formula bar above the worksheet.

Functions dealing with randomness will return a different value upon each recalculation. This means that every time that a function is evaluated in a different cell, the random numbers will be replaced by updated random numbers.