Use RANDBETWEEN to Create a Random Number Generator in Excel

Colored numbers lit up on a board.

Ivan McClellan/Getty Images

You can use the RANDBETWEEN function to generate random integers (whole numbers only) between a range of values in an Excel worksheet. To specify the range, you use 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.

These instructions apply to Excel 2019, 2016, 2013, 2010, Excel Online and Excel for Office 365.

RANDBETWEEN Function Syntax and Arguments

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

Screenshot of Excel showing the RANDBETWEEN function

The syntax for the RANDBETWEEN function is:

  • Bottom (required) is the lowest possible integer the function can return as a result. You can enter the actual integer for this argument or use a cell reference to the location of the data in the worksheet.
  • Top (required) is the highest possible integer the function can return as a result. You can enter the actual integer for this argument or use a cell reference.

If the Bottom argument is bigger than the Top argument, the RANDBETWEEN function will return a #NUM! error value.

Screenshot of Excel showing the RAND and RANDBETWEEN functions

Like the RAND function, RANDBETWEEN is one of Excel's volatile functions, which means:

  • Every time someone makes a change to the worksheet, the function recalculates, producing a new random number.
  • Any formula that depends — either directly or indirectly — on a cell containing a volatile function will also recalculate every time someone makes a change.
  • You should use caution when using volatile functions in worksheets or workbooks containing large amounts of data, as they can slow down the program's response time due to the frequency of recalculations.

Using Excel's RANDBETWEEN Function

The example below shows how to get the RANDBETWEEN function to return a random integer between 1 and 100.

Options for entering the function and its arguments include:

  • Typing the complete function into a worksheet cell
  • Selecting the function and arguments using the Function Dialog Box

Many users find it easier to use the Function Dialog Box as it takes care of entering the function's syntax including brackets and comma separators between arguments.

Excel spreadsheet with the Function Dialog box open to the RANDBETWEEN function.
  1. Input the data in cells A2 and B2 as shown in the above image (1 and 100 respectively).

  2. Click on cell C3 to make it the active cell which is where the RANDBETWEEN function goes.

  3. Click on the Formulas tab of the ribbon.

  4. Click on the Math & Trig icon to open the Function drop-down list.

  5. Click on RANDBETWEEN in the list to open the Function Dialog Box. The data that you input into the blank rows in the dialog box will form the function's arguments.

  6. Click Bottom in the dialog box.

  7. Click cell A2 in the worksheet to enter this cell reference into the dialog box.

  8. Click Top of the dialog box.

  9. Click cell B2 in the worksheet to enter the second cell reference.

  10. Click OK to complete the function and return to the worksheet.

  11. A random number between 1 and 100 should appear in cell C3.

  12. To generate another random number, press the F9 key on the keyboard which causes the worksheet to recalculate.

  13. When you click on cell C3, the complete function, =RANDBETWEEN(A2,B2),  appears in the formula bar above the worksheet.