Written and maintained by CASRAI Editorial Board
Last updated
To generate a random number in Excel, use =RAND() for a decimal from 0 up to (but not including) 1, =RANDBETWEEN(bottom, top) for a whole number, or =RANDARRAY() to fill a whole block of cells with one formula. All three recalculate, so the numbers change whenever the worksheet calculates unless you freeze them. For research work that is the part that matters: a list you plan to cite or reuse has to be fixed as values before you rely on it.
What it is used for in research
Random numbers in a spreadsheet are used to draw a random subset of records, assign participants or samples to groups, create test data, and feed simple simulations. Each use depends on the same property: every value is drawn independently from a stated range. RAND and RANDBETWEEN do not give you control over the draw itself, so what you control is the range, the type of number (decimal or whole), and whether the result is frozen afterwards.
If your goal is picking rows from a dataset without repeats, read how to do a random sample in Excel, which covers the helper-column and sort method. For the statistical idea behind it, see simple random sampling and the dictionary entry on sampling methods.
Which function to use
| You need | Function | What Microsoft documents | Excel versions listed by Microsoft |
|---|---|---|---|
| A decimal between 0 and 1 | RAND() |
No arguments; returns an evenly distributed random real number greater than or equal to 0 and less than 1 | Microsoft 365, 2024, 2021, 2019, 2016 |
| A whole number in a range | RANDBETWEEN(bottom, top) |
Returns a random integer between the smallest (bottom) and largest (top) integer you give | Microsoft 365, 2024, 2021, 2019, 2016 |
| A block of numbers from one formula | RANDARRAY([rows],[columns],[min],[max],[whole_number]) |
All arguments optional; defaults are 0 to 1 and decimals; returns an array that spills | Microsoft 365, 2024, 2021 (2019 and 2016 are not in the list) |
Install or access (official source only)
These are built-in worksheet functions, so nothing needs installing. Microsoft documents each one on its own Support page; the function reference pages are listed under Sources. The one version constraint to check is RANDARRAY: Microsoft’s page lists Microsoft 365, Excel 2024, Excel 2021 and the mobile apps, and does not list Excel 2019 or 2016. In those two versions use RAND or RANDBETWEEN copied down a range instead.
Step-by-step task
Generate a random decimal (RAND)
- Click an empty cell.
- Type
=RAND()and press Enter. The function takes no arguments. - For a decimal in a range from a to b, use
=RAND()*(b-a)+a. For example,=RAND()*(10-5)+5returns a number from 5 up to (not including) 10.
Generate a random whole number in a range (RANDBETWEEN)
- Click an empty cell.
- Type
=RANDBETWEEN(1,100)and press Enter. The first argument is the smallest integer the function can return and the second is the largest. - Negative bounds are allowed:
=RANDBETWEEN(-1,1)returns -1, 0 or 1. - To fill a column, copy the cell down. Each cell draws its own number.
Microsoft’s RAND page also lists a whole-number alternative, =INT(RAND()*100), which returns whole numbers from 0 to 99.
Fill a block of random numbers in one formula (RANDARRAY)
- Click the top-left cell of an empty area.
- Type
=RANDARRAY(10,3,1,100,TRUE)and press Enter. This asks for 10 rows and 3 columns of whole numbers between 1 and 100. - The results spill into the cells below and to the right, so those cells must be empty.
- Leave
whole_numberout, or set it to FALSE, for decimals. With no arguments at all,=RANDARRAY()returns a single value between 0 and 1.
Stop the numbers from changing
- For a single cell: select the cell, click in the formula bar, and press F9. Microsoft documents this for RAND: it replaces the formula with its value.
- For a range: copy the cells, then use Paste Special and choose Values to replace the formulas with the numbers they currently show. Save the result before you use it.
Output and how to read it
Every result is a fresh draw. Microsoft states that a new random number is returned every time the worksheet is calculated, and that includes entering a value or formula in any other cell, so a column of random numbers changes whenever you edit something nearby. RAND can return 0 but never 1. RANDBETWEEN can return either bound, so with =RANDBETWEEN(1,6) a 1 and a 6 are both possible outcomes.
Excel’s documentation for these functions lists no seed argument (RAND has no arguments, RANDBETWEEN takes only bottom and top, and RANDARRAY takes rows, columns, min, max and whole_number). That means you cannot ask Excel to reproduce the same sequence later. If someone else needs to see the numbers you used, share the frozen values, not the formulas.
Because each call is independent, the same whole number can appear more than once in a RANDBETWEEN or RANDARRAY column. Repeats are therefore normal, and if you need every value to appear once, use the sort-based method in the random sample guide.
The random-number draw itself is also what a simple spreadsheet simulation repeats; see what is a Monte Carlo simulation, and the uniform distribution formula for the distribution RAND follows.
Troubleshooting
The numbers keep changing
That is expected: the functions return a new value at every calculation. Freeze them with F9 in the formula bar (one cell) or Paste Special, Values (a range), as above.
RANDARRAY returns #VALUE!
Microsoft states that the minimum must be less than the maximum, otherwise RANDARRAY returns a #VALUE! error. Check that min is smaller than max.
RANDARRAY shows an error or is not recognised
The function is not listed for Excel 2019 or 2016. Use RAND or RANDBETWEEN and copy the formula down instead.
The spilled results will not appear
RANDARRAY returns an array that spills into neighbouring cells. Clear any cells in the way.
A RANDBETWEEN column contains duplicates
Each cell is an independent draw, so repeats are normal. Use the no-repeat method linked in the Output section.
Free alternatives: LibreOffice Calc and Google Sheets
| Tool | Decimal 0 to 1 | Whole number in a range | Notes |
|---|---|---|---|
| LibreOffice Calc | RAND(), no arguments |
RANDBETWEEN(Bottom; Top), both bounds inclusive |
New value on each recalculation; F9 forces one. Non-volatile RAND.NV and RANDBETWEEN.NV are also listed |
| Google Sheets | RAND(), 0 inclusive to 1 exclusive |
RANDBETWEEN, uniformly random integer between two values, inclusive |
RANDBETWEEN is listed on the RAND page under See Also |
Note that LibreOffice separates arguments with semicolons in its help-page syntax.
FAQ
How do I use a random number generator in Excel?
Excel has no separate generator tool for this task: type =RAND(), =RANDBETWEEN(1,100) or =RANDARRAY(10,1,1,100,TRUE) into a cell and press Enter.
What is the Excel number randomizer function?
There is no function by that name. The functions that produce random numbers are RAND, RANDBETWEEN and RANDARRAY. To reorder an existing list at random, put RAND next to each row and sort by that column; the random sample guide walks through it.
How do I make random numbers in Excel stop changing?
Select the cell, click the formula bar and press F9 to replace the formula with its value, or copy a range and use Paste Special, Values.
How do I generate random decimals between two numbers?
Use =RAND()*(b-a)+a, where a is the lower limit and b the upper. The result is at least a and below b.
Can RANDBETWEEN return the bounds?
Yes. The bottom and top arguments are the smallest and largest integers the function will return.
Does Excel let me set a seed?
The documented arguments of RAND, RANDBETWEEN and RANDARRAY include no seed, so the same sequence cannot be requested again.
Sources
- Microsoft Support, RAND function, checked 2026-10-29.
- Microsoft Support, RANDBETWEEN function, checked 2026-10-29.
- Microsoft Support, RANDARRAY function, checked 2026-10-29.
- LibreOffice Help, Mathematical functions: RAND and RANDBETWEEN, checked 2026-10-29.
- Google Docs Editors Help, RAND, checked 2026-10-29.
