Generate Random Numbers, Dates and Values in Excel

by

Excel has three random functions: RAND for decimals between 0 and 1, RANDBETWEEN for whole numbers in a range, and RANDARRAY for a whole grid at once. With them you can make random decimals, dates, picks from a list and numbers that never repeat.

Decimal 0 to 1=RAND()
Whole number=RANDBETWEEN(1,100)
A grid (365)=RANDARRAY(5,3)

RAND: a decimal between 0 and 1

=RAND()
RAND returning random decimals

For a decimal between two values, such as 10 and 50: =RAND()*(50-10)+10. See the RAND function.

RANDBETWEEN: a whole number in a range

=RANDBETWEEN(10,75)
RANDBETWEEN returning whole numbers between 10 and 75

Both limits can come up. See the RANDBETWEEN function.

RANDARRAY: many random numbers at once (Excel 365)

=RANDARRAY(9,5,-15,50,TRUE)
RANDARRAY returning 9 rows and 5 columns of whole numbers between -15 and 50

The arguments are rows, columns, minimum, maximum and whole numbers (TRUE) or decimals (FALSE). This returns 45 whole numbers between -15 and 50. See the RANDARRAY function.

More random values

You want Formula
A random date in 2026 =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)), formatted as a date
A random time of day =RAND(), formatted as time
A random item from A2:A20 =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20)))
Yes or No at random =CHOOSE(RANDBETWEEN(1,2),"Yes","No")
10 numbers from 1 to 50, no repeats (365) =TAKE(SORTBY(SEQUENCE(50),RANDARRAY(50)),10)
A list in random order (365) =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20)))

More on picking from a list in pick a random value from a list, and on shuffling rows in shuffle records in Excel. To split people into groups, try the random team generator.

Stop the numbers from changing

Random functions recalculate every time anything on the sheet changes, or when you press F9.

RANDBETWEEN values changing on every recalculation
  • To keep the current values: copy the cells, then Paste Special → Values (Ctrl + Alt + V, V, Enter).
  • For a single cell: click in the formula bar, press F9, then Enter. The formula becomes its result.

With a macro

Sub RandomNumber()
    Randomize
    Range("C3").Value = Int((999 - 50 + 1) * Rnd + 50)
End Sub

Puts a whole number between 50 and 999 in C3. Randomize makes VBA start from a different point each time Excel opens; without it, the same sequence repeats. The value doesn't change until you run the macro again.

Related