How to Generate Random Numbers in Excel (RAND, RANDBETWEEN & RANDARRAY)

To generate a random number in Excel, type =RAND() in a cell and press Enter. You get a random decimal number between 0 and 1.

For a random whole number between two numbers, use RANDBETWEEN instead. For example, =RANDBETWEEN(1,100) returns a random integer from 1 to 100.

Here’s the quick version:

1. Select a cell.
2. Type =RANDBETWEEN(1,100) (or =RAND() for a decimal).
3. Press Enter.
4. Drag the fill handle down to get more random numbers.

In Excel for Microsoft 365, =RANDARRAY(10,1,1,100,TRUE) fills 10 cells with random whole numbers from 1 to 100 in one go.

Random numbers are great for picking audit samples, making test data or running simulations 👩‍🏫 Below, I’ll show you all three functions, how to get random decimals between two numbers and how to stop the numbers from changing.

Random numbers with RAND

RAND is the most basic function to generate random numbers in Excel. The easiest and the simplest.

It doesn’t take any arguments at all 💁‍♀️ Funny, no?

The RAND function returns a random decimal number that is greater than or equal to 0 and less than 1.

To get a random number between 0 and 1, write the RAND function as follows:

=RAND()

Hit “Enter” and that’s it.

You get a random number. That’s how easy it is to create random numbers in Excel 🤩

If you want to generate a series of random numbers, copy this formula and paste it into multiple cells.

Or drag the fill handle down (or to the right) across as many cells as you need random numbers 📍

Here, we have copied this formula to 3 more cells below.

And there we have 4 random numbers generated in Excel.

Random decimal numbers between two numbers

RAND only gives you numbers between 0 and 1. To get a random decimal number between any two numbers a and b, use:

=RAND()*(b-a)+a

For example, =RAND()*(50-10)+10 returns a random decimal number between 10 and 50.

Want a fixed number of decimals? Wrap it in ROUND. =ROUND(RAND()*(50-10)+10,2) gives you a random number between 10 and 50 with two decimals.

Random numbers with RANDBETWEEN

RANDBETWEEN is a very cool function. It generates a random number between two numbers you choose 😎

The syntax is =RANDBETWEEN(bottom,top), where bottom is the smallest and top is the largest whole number it can return. Note that RANDBETWEEN only returns whole numbers (integers), not decimals.

To generate a random number in Excel between 0 and 100:

1. Select the cell where you want the random number populated.
2. Write the RANDBETWEEN function as follows:

=RANDBETWEEN(0,100)

In the above function, 0 is the bottom and 100 is the top.

This tells Excel to generate a random whole number from 0 to 100. In other words, an integer that is equal to or greater than 0 and smaller than or equal to 100 ✌

3. Hit “Enter” and there you go.

That’s how you can create random whole numbers in Excel.

Need more of them?

4. Drag the fill handle down across as many cells as you need values.

For example, we want 3 more random numbers, so we drag it 3 cells down⬇

Similarly, you can drag the formula to the right or left to generate as many random numbers as you need.

For example, here we have dragged it 3 columns to the right ➡

Pro Tip! How to stop random numbers from changing

The random number functions of Excel are volatile 🔥

They recalculate every time the worksheet is calculated. That happens when you enter or edit anything on the sheet (it doesn’t have to be the random numbers), and when you press F9.

For example, say RANDBETWEEN gave you the number 81. The next time the sheet is recalculated, the formula returns another random number. Say 97.

Here’s how you can stop these functions from recalculating over and over again 🚴‍♀️

1. One cell: select the cell, click in the formula bar and press F9, then press Enter. This replaces the formula with its result, so 81 stays 81 and won’t change with new recalculations.
2. Many cells: select the random numbers and copy them (Ctrl + C). Then go to Home > Paste > Values, or press Ctrl + Alt + V and choose Values in the Paste Special dialog box.

Many random numbers at once with RANDARRAY

In Excel for Microsoft 365 and Excel 2021 or later, you don’t have to drag a formula down. RANDARRAY returns a whole range of random numbers from a single formula.

The syntax is:

=RANDARRAY(rows, columns, min, max, whole_number)

All the arguments are optional:

• rows and columns: how many random numbers to return. Leave them out and you get a single number.
• min and max: the smallest and largest number. They default to 0 and 1.
• whole_number: TRUE for whole numbers, FALSE (the default) for decimals.

For example, this formula fills 10 rows and 1 column with random whole numbers from 1 to 100:

=RANDARRAY(10,1,1,100,TRUE)

And this one returns a block of 5 rows by 3 columns of random decimals between 0 and 1:

=RANDARRAY(5,3)

Type the formula in one cell and press Enter. The results “spill” into the cells below and to the right, so make sure those cells are empty. If they aren’t, you’ll get a #SPILL! error.

RANDARRAY recalculates just like RAND and RANDBETWEEN. Use the Pro Tip above to turn the results into fixed values.

If min is bigger than max, RANDARRAY returns a #VALUE! error.

That’s it! Now what?

Now you know how to generate random numbers in Excel. RAND gives you random decimals between 0 and 1 (or between any two numbers with a small formula) 🔢

RANDBETWEEN gives you a random whole number between a minimum and a maximum, and RANDARRAY fills a whole range in one go. How cool is that?

If you enjoyed learning about these functions, there’s so much more to learn in Excel.

To begin, I recommend the VLOOKUP, SUMIF and IF functions.

How? Enroll in my 30-minute free email course here that teaches you all about these functions.

Other resources

In addition to generating random numbers in Excel, you might simply want to randomize an already created list in Excel.

Or you may want to sort a randomized list in Excel. Well, for both of the said cases, we have got you covered.

Read our other blogs to learn how to do that in Excel.

Frequently asked questions

How do I generate a random number between 1 and 100 in Excel?

Use =RANDBETWEEN(1,100). Excel returns a random whole number from 1 to 100, and both 1 and 100 can come up.

How do I generate multiple random numbers in Excel?

Enter =RAND() or =RANDBETWEEN(1,100) in one cell, then copy it or drag the fill handle across as many cells as you need. In Excel for Microsoft 365, =RANDARRAY(10,1,1,100,TRUE) returns 10 random whole numbers from one formula.

How do I generate random decimal numbers between two numbers?

Use =RAND()*(b-a)+a, where a is the lowest and b the highest number. For two decimals between 10 and 50, use =ROUND(RAND()*(50-10)+10,2). RANDBETWEEN can’t do this, because it only returns whole numbers. =RANDARRAY(1,1,10,50) works too.

How do I stop random numbers from changing in Excel?

Copy the cells and paste them as values with Home > Paste > Values (or Ctrl + Alt + V, then Values). For one cell, click in the formula bar, press F9 and then Enter. See the Pro Tip above.

How do I pick a random value from a list in Excel?

Combine INDEX with RANDBETWEEN. If your list is in A2:A20, =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))) returns a random item from it. To shuffle the whole list, see how to randomize a list in Excel.

How do I generate random numbers with VBA?

In VBA, the Rnd function returns a random number that is at least 0 and less than 1. Call Randomize first so you don’t get the same sequence every time. For a random whole number between two numbers, use Int((upper-lower+1)*Rnd+lower) (where upper and lower are the highest and lowest numbers), or call WorksheetFunction.RandBetween(1, 100). New to VBA? Start with my VBA guide.