Random Number Generator in Excel: RAND and RANDBETWEEN
By ExcelReady TeamUpdated 6 min read
The short answer
Excel has three random number generator functions. =RAND() returns a decimal from 0 up to (not including) 1. =RANDBETWEEN(1,100) returns a whole number from 1 to 100. =RANDARRAY(10,1,1,100,TRUE) fills ten cells at once in Microsoft 365 and Excel 2021. All three recalculate whenever the sheet changes, so paste them as values to keep them.
Key takeaways
- =RAND() gives a decimal from 0 up to 1; =RANDBETWEEN(bottom,top) gives a whole number, both ends included.
- For a decimal between two numbers, use =min+(max-min)*RAND(), and ROUND it to the decimals you need.
- RANDARRAY makes a whole list of random numbers in Microsoft 365 and Excel 2021.
- Random functions are volatile: they change on every edit or F9. Paste them as values to freeze them.
- For whole numbers, use INT or RANDBETWEEN, not ROUND, so every number is equally likely.
On this page+
Excel works as a random number generator with three functions: RAND for decimals, RANDBETWEEN for whole numbers in a range, and RANDARRAY for a whole list at once. They are handy for test data, random samples, raffles and practice sheets. This guide shows each one, how to get numbers in any range, how to avoid repeats, and how to stop the numbers from changing.
Which random function should you use?#
Pick the function by the kind of number you need:
| You need | Formula | Example result | Versions |
|---|---|---|---|
| A decimal from 0 up to 1 | =RAND() | 0.7351... | All |
| A whole number from 1 to 100 | =RANDBETWEEN(1,100) | 42 | All current versions |
| A decimal from 50 up to 80 | =50+30*RAND() | 61.2087... | All |
| Ten whole numbers from 1 to 100 | =RANDARRAY(10,1,1,100,TRUE) | A list of 10 | Microsoft 365, Excel 2021 and later |
| A random date in 2025 | =RANDBETWEEN(DATE(2025,1,1),DATE(2025,12,31)) | 14/06/2025 | All current versions |
Your results will differ from these examples, and they change each time Excel recalculates. That is how random functions work.
How to use RAND#
RAND takes no arguments. Type it with empty brackets:
=RAND()It returns a decimal that is at least 0 and less than 1, such as 0.7351. Every value in that range is equally likely. RAND on its own is rarely the final answer; you scale it to the range you need. If formulas are new to you, the lesson on your first formulas covers typing them and how they recalculate.
How to use RANDBETWEEN for whole numbers#
RANDBETWEEN returns a whole number between two limits, and both limits can come up:
=RANDBETWEEN(bottom, top)=RANDBETWEEN(1,6)rolls a die: 1, 2, 3, 4, 5 or 6.=RANDBETWEEN(-10,10)can return negative numbers too.=RANDBETWEEN(DATE(2025,1,1),DATE(2025,12,31))returns a random date, because Excel dates are numbers. Format the cell as a date to see it.
To pick a random item from a list in A2:A11, combine it with INDEX:
=INDEX(A2:A11,RANDBETWEEN(1,ROWS(A2:A11)))ROWS counts the items (10), RANDBETWEEN picks a position, and INDEX returns the name in that position.
RANDBETWEEN can also make random letters for test codes. Capital letters A to Z are character numbers 65 to 90, so =CHAR(RANDBETWEEN(65,90)) returns one random capital letter. Join a few with &, such as =CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,999), to get dummy codes like K482 for practice data.
How to get random numbers in any range#
To get a decimal between a minimum and a maximum, scale RAND by the width of the range and add the minimum:
=min+(max-min)*RAND()For prices from 50 up to 80, that is =50+30*RAND(). With the limits in cells, use =$D$2+($E$2-$D$2)*RAND() so you can change them later.
Decimals with many places look messy, so round them. The ROUND function guide explains the arguments:
=ROUND(50+30*RAND(),2)If RAND returned 0.4127, the result is 50 + 30 × 0.4127 = 62.381, which rounds to 62.38.
Don't use ROUND for random whole numbers#
=ROUND(RAND()*10,0) gives 0 to 10, but 0 and 10 come up only half as often as the other numbers, because each of them gets only half a step of the RAND range. Use INT instead, which always rounds down:
=INT(RAND()*10)+1This gives 1 to 10, each equally likely, and matches =RANDBETWEEN(1,10). See MOD and INT explained and the guide to ROUND, ROUNDUP and ROUNDDOWN for how each one rounds.
How to generate random numbers without duplicates#
RANDBETWEEN can repeat a number. To get every number from 1 to 10 once, in random order, shuffle a sequence. In Microsoft 365 or Excel 2021:
=SORTBY(SEQUENCE(10),RANDARRAY(10))SEQUENCE(10) makes 1 to 10, RANDARRAY(10) makes ten random keys, and SORTBY sorts the numbers by those keys. The result spills into ten cells.
In older Excel, type the numbers 1 to 10 in column A, put =RAND() next to each in column B, and sort both columns by column B. The full method, including random teams, is in how to randomize a list.
How to stop random numbers from changing#
RAND, RANDBETWEEN and RANDARRAY are volatile: they recalculate every time you edit any cell, open the file or press F9. To keep a set of numbers:
- Select the cells with the random formulas.
- Copy them (Ctrl+C; Mac: ⌘C).
- Paste them as values: Ctrl+Alt+V, then V and Enter on Windows. On a Mac, press ⌃⌘V and choose Values.
The formulas are replaced by fixed numbers. The guide to Paste Special values shows more ways to do this. For a single cell, you can also type =RAND() in the formula bar, press F9 to turn it into its value, and press Enter.
Switching the workbook to manual calculation also stops the changes, but it stops every other formula from updating too. That causes the opposite problem described in Excel formula not updating.
Other random number tools in Excel#
Two more options are useful for larger tasks:
- Analysis ToolPak. Turn it on under File > Options > Add-ins > Excel Add-ins > Go (Mac: Tools > Excel Add-ins). Then use Data > Data Analysis > Random Number Generation. It writes fixed values, can follow distributions such as normal or uniform, and accepts a random seed so you can produce the same set again.
- NORM.INV with RAND.
=NORM.INV(RAND(),100,15)gives values that follow a normal (bell-shaped) distribution with a mean of 100 and a standard deviation of 15.
Excel's random numbers are pseudo-random: they come from a formula. They are fine for samples, test data and games, but not for security uses such as passwords.
Practise it#
Random numbers are mostly about scaling and rounding a value correctly. Practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you type each formula and it checks it instantly. If formulas are still new, start with your first formulas, then try the price-scaling exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Column B holds random values that were already frozen as numbers. In C2, turn B2 into a price between the Min (D2) and Max (E2), rounded to 2 decimals: =ROUND($D$2+($E$2-$D$2)*B2,2).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Max | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Tip: Scale the 0-to-1 value by the width of the range (80 - 50 = 30), add the minimum, then round to 2 decimals. 50 + 30 × 0.4127 = 62.381.
Practise this in a lesson
Frequently asked questions
How do I generate a random number between 1 and 100 in Excel?
Type =RANDBETWEEN(1,100) and press Enter. It returns a whole number from 1 to 100, including both ends, and a new one each time the sheet recalculates.
How do I stop random numbers from changing in Excel?
Select the cells, copy them, then paste them as values with Paste Special > Values. The formulas are replaced by their current results, which no longer change.
How do I generate random numbers without duplicates in Excel?
In Microsoft 365 or Excel 2021, use =SORTBY(SEQUENCE(10),RANDARRAY(10)) to get 1 to 10 in a random order with no repeats. In older versions, put =RAND() next to a list and sort by that column.
How do I generate random decimals in a range?
Use =min+(max-min)RAND(), for example =50+30RAND() for a decimal from 50 up to 80. Wrap it in ROUND, such as =ROUND(50+30*RAND(),2), to keep two decimal places.
Are Excel's random numbers truly random?
They are pseudo-random: a formula produces them, so they look random and are fine for samples, test data and games. Don't rely on them for security tasks such as making passwords.