This article walks through Excel's RAND and RANDBETWEEN functions, showing how to generate random decimals and whole numbers, why they recalculate automatically, and practical ways to use them. It is for anyone who needs random values in spreadsheets�from beginners exploring Excel functions to analysts building simulations or test data.
What You'll Learn / Key Take-Aways
- RAND() returns a random decimal number between 0 and 1.
- RANDBETWEEN(bottom, top) returns a random whole number between two integers you supply.
- Both functions are volatile: they recalculate whenever the workbook recalculates or any change is made.
- To keep values stable, copy and paste values or use nonvolatile alternatives after generation.
- Common uses: sampling, test data, simple simulations, and randomized sorting.
## Quick overview: what these functions do
RAND and RANDBETWEEN are Excel functions that generate random numbers. They are simple to type and handy for a range of tasks.
- RAND()
- Syntax: RAND()
- Returns: a decimal value >= 0 and < 1, with many decimal places (Excel shows up to about 15 digits of precision).
- Use when you need a fractional random between zero and one or want to transform that into another distribution.
- RANDBETWEEN(bottom, top)
- Syntax: RANDBETWEEN(bottom, top)
- Returns: a whole integer between bottom and top inclusive.
- Use when you need whole-number randoms such as 0 to 100, 1 to 10, or any integer range.
## How to enter and fill these formulas
1. RAND example
- Type RAND() in a cell and press Enter. You will get a decimal value like 0.374652.
- Drag the fill handle to copy RAND() across cells or down a column to get multiple random decimals.
2. RANDBETWEEN example
- Type =RANDBETWEEN(0,100) in a cell and press Enter. You will get an integer between 0 and 100.
- Copy or fill that cell to generate a grid of whole-number randoms.
Practical tip: put the formula in the first cell, then double-click the fill handle to auto-fill a list if adjacent data exists, or drag manually to fill a specified range.
## Volatile functions: what that means and why it matters
A volatile function recalculates every time Excel recalculates the workbook. RAND and RANDBETWEEN are volatile.
- Triggers for recalculation include:
- Editing any cell in the workbook.
- Pressing F9 or choosing Calculate Now from the Formulas tab.
- Opening the workbook in some cases.
- Consequences:
- The numbers will change continuously during workbook edits, which is expected behavior but can be confusing.
- If you need the numbers to stay the same, convert the formulas to values after generation.
How to freeze results
- Select the cells with RAND/RANDBETWEEN, copy (Ctrl+C), then use Paste Special > Values (Ctrl+Alt+V, then V) to replace formulas with fixed numbers.
- Alternatively, generate randoms and then use formulas that reference them in ways that remove volatility, or write the values to a static sheet.
## Common use cases and quick examples
- Random decimal for probabilistic thresholds
- =RAND()
- Use in an IF to assign outcomes: =IF(RAND()<0.3,"A","B") to give roughly 30 percent A.
- Random whole numbers for test data
- =RANDBETWEEN(1,100)
- Useful for generating fake sales numbers, IDs for mockups, or randomized sampling groups.
- Random sort order
- Add a column with =RAND() next to your data and sort by that column to randomize row order. Then convert values to break the link.
- Adjusting range from RAND()
- To get a random decimal between 0 and 10: =RAND()*10
- Between 5 and 15: =5 + RAND()*10
- To emulate RANDBETWEEN uses but keep decimals: =5 + RAND()*(15-5)
## When to prefer RAND vs RANDBETWEEN
- Use RAND when you need fractional or continuous random values, or you want to scale and shift decimals into another numeric range.
- Use RANDBETWEEN when you need integers and you want the function to return whole numbers directly.
## Additional Tips, Pitfalls to Avoid & Pro Advice
- Avoid leaving RAND or RANDBETWEEN live in a final report unless you want them to change. Freeze values before sharing or finalizing.
- If you need many random numbers and performance matters, be mindful that many volatile formulas can slow workbook calculation.
- For Excel versions with RANDARRAY (Office 365 and newer), RANDARRAY can generate multiple random numbers with one formula and nonvolatile options. Consider RANDARRAY for array-friendly workflows.
- If you need reproducible randoms for repeatable testing, Excel functions do not accept seeds. Instead, generate once and store values, or use external tools that support seeding.
## Example workflow: create a randomized sample of 100 rows
1. Add a helper column and enter =RAND() in row 2 of that column.
2. Fill that formula down the 100 rows.
3. Sort the table by the helper column to randomize row order.
4. If you need the new order fixed, copy the randomized rows to a new sheet and paste values.
## FAQ
Q: What is the difference between RAND and RANDBETWEEN?
A: RAND returns a decimal between 0 and 1. RANDBETWEEN returns an integer between the bottom and top you specify.
Q: Why do my random numbers keep changing?
A: RAND and RANDBETWEEN are volatile functions. They recalculate on any workbook change or when you force a calculation. Copy and paste values to freeze them.
Q: Can I set a seed for RAND in Excel so results are reproducible?
A: Not with RAND or RANDBETWEEN. Excel's built-in functions do not accept a seed. Generate values once and save them, or use external tools or VBA to create seeded randoms.
Q: How do I generate multiple random numbers at once?
A: Enter RAND or RANDBETWEEN and drag to fill multiple cells. In modern Excel you can use RANDARRAY to return an array of random values from a single formula.
Q: Are RAND and RANDBETWEEN the same across Excel versions?
A: The basic behavior is consistent, but newer Excel versions include RANDARRAY which offers expanded array behavior and options. RANDBETWEEN is available in most versions.
Q: Will many RAND formulas slow down my workbook?
A: Yes, because they are volatile. Hundreds or thousands of volatile formulas can increase calculation time. Consider generating values once and storing them if performance becomes an issue.
## Summary of Key Points / Take-Home Messages
- RAND() gives random decimals between 0 and 1; RANDBETWEEN(bottom, top) gives random whole numbers in a specified inclusive range.
- Both functions are volatile and will recalculate automatically. Freeze results with Paste Special > Values if you need stability.
- Use RAND for fractional ranges and RANDBETWEEN for integers. For array generation and more control, consider RANDARRAY in newer Excel versions.
- Common uses include sampling, random sort, mock data, and simple simulations. Mind performance when creating many volatile formulas.
