Excel RAND and RANDBETWEEN Functions
last modified October 5, 2026
The RAND and RANDBETWEEN functions are essential for
generating random numbers in Excel. RAND creates random decimals
that are greater than or equal to 0 and less than 1, while
RANDBETWEEN generates random integers within a specified range.
This tutorial provides a comprehensive guide to using these functions with
detailed examples. You'll learn basic syntax, practical applications, and
advanced techniques to master these powerful Excel functions.
RAND and RANDBETWEEN Function Basics
The RAND function generates a random decimal number that is
greater than or equal to 0 and less than 1. RANDBETWEEN generates
random integers between a specified bottom and top value, including both
endpoints. Both functions are volatile, so a new value appears each time the
worksheet recalculates.
| Component | Description |
|---|---|
| Function Name | RAND, RANDBETWEEN |
| Syntax | =RAND() and =RANDBETWEEN(bottom, top) |
| Required Arguments | None for RAND; bottom and top for RANDBETWEEN |
| Return Value | Decimal from 0 to 1 (1 excluded) for RAND; integer between bottom and top for RANDBETWEEN |
This table breaks down the essential components of the two random number functions in Excel. It shows the syntax format, argument requirements, and return value characteristics of each function.
| Argument | Meaning |
|---|---|
| bottom | The smallest integer RANDBETWEEN can return. It can be a
literal, a cell reference, or an expression that returns a number, such as
A1+10. |
| top | The largest integer RANDBETWEEN can return. Both endpoints
are included, so RANDBETWEEN(1,1) always returns 1. |
RANDBETWEEN requires exactly two arguments. RAND
takes none: it must be written as RAND() with empty parentheses,
and passing an argument to it produces a "too many arguments" error. The
arguments of RANDBETWEEN must be numeric; text that cannot be read
as a number produces a #VALUE! error, and a bottom
larger than top produces a #NUM! error.
Neither function accepts a seed value, so Excel cannot be asked to repeat a particular sequence of numbers. The only way to keep one draw is to store it, which is shown later in the Freezing Random Results section.
Basic RAND Example
This example demonstrates the simplest use of the RAND function to generate a random decimal number in the range from 0 to 1.
=RAND()
This formula generates a random decimal number like 0.623481. Each time the worksheet recalculates, a new random number appears. The value is always greater than or equal to 0 and strictly less than 1; it can never be exactly 1. The values are uniformly distributed across that range, so no interval is preferred over another.
Basic RANDBETWEEN Example
This example shows how to use RANDBETWEEN to generate random integers between specified minimum and maximum values.
=RANDBETWEEN(1, 100)
This formula generates a random integer between 1 and 100, such as 42. The result is always a whole number, both endpoints are included, and every integer in the range is equally likely. Values such as 42.7 can never appear.
Generating Random Decimals in a Range
Combine RAND with arithmetic to create random decimals in any range. This example generates numbers from 5.0 up to, but not including, 10.0.
=RAND()*(10-5)+5
This formula multiplies RAND's result by the range width (5) and then adds the
minimum value (5). For RAND()=0.5, the result is 7.5. The general form is
=RAND()*(b-a)+a, which produces uniformly distributed decimals
from a up to, but never reaching, b. In this example the result always stays
below 10.
Generating Random Whole Numbers in a Range
To build random integers from RAND alone, scale the result and wrap it in
INT, which discards the decimal part. The width of the range must
be increased by one so that the upper bound is reachable.
=INT(RAND()*(100-1+1))+1
Here RAND()*100 returns a value from 0 up to, but never reaching,
100. INT turns it into a whole number from 0 to 99, and the final
+1 shifts the result to 1 through 100. The general form is
=INT(RAND()*(b-a+1))+a. For a six-sided die,
=INT(RAND()*6)+1 returns 1, 2, 3, 4, 5, or 6. In most worksheets
you can use RANDBETWEEN instead of this construction.
Creating Random Dates
Use RANDBETWEEN with Excel's date system to generate random dates. This example creates dates in 2025.
=RANDBETWEEN(DATE(2025,1,1), DATE(2025,12,31))
This formula generates random dates throughout 2025. Excel stores dates as serial numbers, so RANDBETWEEN accepts DATE values directly and returns the serial number of a randomly chosen day. Format the cell as a date to display it properly.
Generating Random Data in Multiple Cells
Both functions can fill a whole range at once. Select the target cells, type the formula, and confirm with Ctrl+Enter so that every selected cell receives its own independent value.
=RANDBETWEEN(1,100) ' select the cells, type the formula, press Ctrl+Enter
Copy a single formula down a column with Ctrl+D to extend the series. In Excel
for Microsoft 365 one dynamic array formula can produce the whole set:
=RANDARRAY(10,1,1,100,TRUE) spills ten random whole numbers
between 1 and 100 down the column.
Random Sampling Without Duplicates
Create a unique random sample by combining RAND with ranking functions. This example draws 5 rows at random from a set of 50, without repeating any row.
| A | B |
|---|---|
| =RAND() | =RANK(A1,$A$1:$A$50) |
| =RAND() | =RANK(A2,$A$1:$A$50) |
| ... | ... |
| =RAND() | =RANK(A50,$A$1:$A$50) |
=RANK(A1,$A$1:$A$50)
Column A generates random numbers and column B ranks them from 1 to 50. The rows whose ranks are 1 through 5 form a random sample of 5 out of 50. Because each rank is used exactly once, no row can appear twice in the selection. The draw changes on every recalculation, so freeze the values as soon as you are happy with the sample.
Random Yes/No Values
Combine RANDBETWEEN with CHOOSE to create random categorical data like Yes/No responses.
=CHOOSE(RANDBETWEEN(1,2), "Yes", "No")
This formula randomly returns "Yes" or "No" with equal probability. Increase
the RANDBETWEEN range and add matching CHOOSE options to draw from more
categories, for example =CHOOSE(RANDBETWEEN(1,4), "Red", "Blue",
"Green", "Pink"). Every category then has the same chance; unequal
probabilities require a weighted technique such as nested IF
tests.
Random Team Assignment
Use RANDBETWEEN with conditional formatting to randomly assign items to teams. This example divides 20 items into 4 teams.
| A | B |
|---|---|
| Item1 | =RANDBETWEEN(1,4) |
| Item2 | =RANDBETWEEN(1,4) |
| ... | ... |
| Item20 | =RANDBETWEEN(1,4) |
=RANDBETWEEN(1,4)
Column B assigns each item to team 1-4 at random. Use conditional formatting to color-code the teams. Press F9 to draw a new set of assignments, and freeze the results once the split looks acceptable. Because each item is drawn independently, the four teams will rarely come out exactly the same size.
Understanding Volatility
RAND and RANDBETWEEN are volatile functions. They
return a new value every time the worksheet recalculates, which happens when
you edit any cell, open the workbook, or press F9. The recalculation is not
limited to the cells that feed the formula, so even an edit in a distant corner
of the sheet redraws every random number in the workbook.
F9 ' recalculate the open workbooks Ctrl+Alt+F9 ' force a complete recalculation
Volatility is exactly what makes these functions useful for simulation and sampling, but thousands of volatile cells can slow a large workbook down. Neither function takes a seed argument, so Excel cannot be asked to reproduce the same sequence twice; the only way to keep a particular draw is to store it.
Freezing Random Results
Convert volatile random numbers to static values using Paste Special. This example shows how to freeze random values.
1. Generate random numbers with RAND/RANDBETWEEN 2. Select the cells and copy (Ctrl+C) 3. Right-click and choose Paste Special > Values 4. Click OK to replace formulas with static values
This process converts formulas to their current values, preventing further recalculation. Use this when you need fixed random numbers for reproducibility.
To freeze a single value, edit the formula in the formula bar instead. With the cursor in the formula bar, F9 evaluates the formula and replaces it with its result; press Enter to keep that value.
=RAND() ' press F9 to convert the formula into its value, then Enter
Both methods leave numbers that never change again, which is the only way to make a random draw reproducible in Excel.
RAND vs RANDBETWEEN vs RANDARRAY
Three functions produce random numbers in Excel. RAND and
RANDBETWEEN work in every version, while
RANDARRAY requires Excel for Microsoft 365 or Excel 2021 and
later.
| Function | Returns | Syntax | Availability |
|---|---|---|---|
| RAND | One decimal, greater than or equal to 0 and less than 1 | =RAND() | All versions |
| RANDBETWEEN | One integer between bottom and top, inclusive | =RANDBETWEEN(bottom, top) | All versions |
| RANDARRAY | An array of decimals or whole numbers | =RANDARRAY([rows],[columns],[min],[max],[whole]) | Microsoft 365 and Excel 2021 or later |
All three are volatile. RANDARRAY combines the roles of the other
two: rows and columns set the size of the result, min and max set the range,
and whole returns whole numbers when it is TRUE. Every argument is optional,
so =RANDARRAY() behaves like =RAND() and returns a
single decimal between 0 and 1. =RANDARRAY(5,1,1,6,TRUE) spills
five whole numbers between 1 and 6 down a column. Use
RANDBETWEEN for one integer at a time and
=RAND()*(b-a)+a when you need decimals in a custom range in a
version without RANDARRAY.
RAND and RANDBETWEEN Errors and Troubleshooting
Most problems come from reversed bounds, non-numeric arguments, or results that keep changing.
| Cause | Symptom | Fix |
|---|---|---|
| bottom is larger than top | #NUM! error | Swap the values so that bottom is less than or equal to top |
| bottom or top is text that cannot be read as a number | #VALUE! error | Use numbers or reference cells that contain numbers |
| An argument is missing from RANDBETWEEN | Too few arguments error | Supply both bottom and top |
| An argument is passed to RAND | Too many arguments error | RAND takes no arguments; write it as =RAND() |
| Result changes after every edit | Numbers never stay the same | Copy the cells and use Paste Special > Values |
| RANDARRAY is not available in this version | #NAME? error | Use RAND or RANDBETWEEN instead, or upgrade to Microsoft 365 |
A number that looks wrong is usually a matter of reversed bounds:
=RANDBETWEEN(100,1) cannot return anything because no integer is
at the same time at least 100 and at most 1, so Excel reports #NUM!.
Check the two arguments whenever an error appears, and remember that empty
cells passed as arguments count as 0.
The RAND and RANDBETWEEN functions are the standard
tools for randomization in Excel. RAND scales cleanly to any
decimal range, RANDBETWEEN handles integers and dates directly,
and together with ranking and CHOOSE they support sampling, shuffling, and
random categories. Remember that both functions are volatile and accept no
seed, so every recalculation redraws the numbers; convert a draw you want to
keep with Paste Special > Values. Mastering these functions, and knowing when
to reach for RANDARRAY, makes random data a reliable part of your
spreadsheets.
Author
List all Excel Formulas.