Summary

To generate a random number between two numbers, you can use the RANDBETWEEN function. In the example shown, the formula in B5 is:

=RANDBETWEEN(1,100)

As the formula is copied down, it returns a random integer between 1 and 100.

Generic formula

=RANDBETWEEN(lower,upper)

Explanation

The Excel RANDBETWEEN function returns a random integer between given numbers. In the example shown, the formula in B5 is:

=RANDBETWEEN(1,100)

This formula is then copied down from B5 to B11. The result is random numbers between 1-100.

RANDBETWEEN is a volatile function that recalculates when a worksheet is opened or changed. This includes any edits to the worksheet, or simply opening the workbook. To prevent random numbers from being recalculated again, you can replace the formulas with the values last calculated:

  1. Select the random numbers
  2. Copy the formulas to the clipboard with Control + C.
  3. Open the Paste Special Window and select "Values"
  4. Click OK to replace the formulas with static values.

For random results that only change when you change a seed value, see Seeded Random Number Generator in Excel (Excel 365), which also shows how to scale random numbers to a range.

Notes

  1. The RAND function can generate random decimal values.
  2. The RANDARRAY function (available in Excel 2021+ and Excel 365) can generate random numeric arrays.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related formulas

Excel Random number from fixed set of options

Random number from fixed set of options

To quickly fill a range of cells with random numbers from a set of fixed options (i.e. 25,50,75,100), you can use a formula based on the CHOOSE and RANDBETWEEN functions. In the example shown, the formula in B4 is: =CHOOSE(RANDBETWEEN(1,4),25,50,75,100) Which returns a random number from the numbers …
Excel Random date between two dates

Random date between two dates

To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. In the example shown, the formula in B5 is: =RANDBETWEEN(DATE(2016,1,1),DATE(2016,12,31)) This formula is then copied down from B5 to B11. The result is random dates between Jan 1, 2016 …
Excel Randomly assign data to groups

Randomly assign data to groups

To randomly assign rows of data to arbitrary groups, you can use the RANDBETWEEN function with the CHOOSE function. In the example shown, the formula in F5 is: =CHOOSE(RANDBETWEEN(1,3),"A","B","C") As the formula is copied down the column, it will return a random group ("A", "B", or "C") at each new …
Excel Random text values

Random text values

To quickly fill a range of cells with random text values, you can use a formula based on the CHOOSE and RANDBETWEEN functions. In the example shown, the formula in C5 is: =CHOOSE(RANDBETWEEN(1,4),"Red","Blue","Green","Pink") Which returns a random color from the values provided.
Excel Random value from list or table

Random value from list or table

To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions. In the example shown, the formula in G7 is: =INDEX(data,RANDBETWEEN(1,ROWS(data)),1)

Related videos

Excel How to pick names out of a hat with Excel

How to pick names out of a hat with Excel

Have you ever had to select the winners in a contest? It's easy when you draw names out of a hat, but how would you do it in Excel? In this video we'll show you a simple way to do it using the RAND function. Here's a list of names that represent entries in a contest. Suppose we'd like to pick five …

Related functions

Excel RANDBETWEEN Function

RANDBETWEEN Function

The Excel RANDBETWEEN function returns a random integer between two given numbers. RANDBETWEEN recalculates each time a worksheet is opened or changed.
Excel RANDARRAY Function

RANDARRAY Function

The Excel RANDARRAY function generates an array of random numbers between two values. The size of the array is specified by rows and columns arguments. The generated values can be either decimals or whole numbers.

Related articles

Seeded Random Number Generator in Excel

Seeded Random Number Generator in Excel

How to keep random numbers from changing in Excel? Excel's random functions like RAND and RANDARRAY recalculate every time you edit your worksheet, making it impossible to maintain stable random results. This article shows you how to build a seeded random number generator using Excel's LAMBDA …