OmniDocGenerate Random Numbers in Excel With Formulas
Learn which Excel formula gives random test numbers, whole or decimal, and how to stop the values from changing every time the sheet recalculates.
If you need sample data for a spreadsheet, a template, or a quick test, you usually want numbers that look random but stay inside rules you set: a range, whole numbers or decimals, maybe a set of dates. Typing them by hand takes too long, and copying numbers from another file can bring along real data you did not mean to share. An Excel number generator formula solves this by creating the values for you inside the sheet.
This guide covers the three functions you need for random numbers in Excel: RAND, RANDBETWEEN and RANDARRAY. It shows how to set the range, how to fill a column or a block, and how to lock the results so they stop changing. No prior knowledge of Excel's newer array features is required.
Which Excel number generator formula should you use? The short answer
Use RANDBETWEEN for whole numbers in a range, and use RAND (scaled to your range) for decimals. Use the table below to match your situation to a formula.
| Your situation | What to do | Why |
|---|---|---|
| You need whole numbers in a range, such as 1 to 100 | Enter =RANDBETWEEN(1,100) | It returns integers and includes both ends of the range |
| You need decimals in a range, such as 10 to 100 | Enter =ROUND(RAND()*90+10,2) | RAND returns 0 up to just under 1; scaling and shifting moves it into your range |
| You need a whole block filled at once | Enter =RANDARRAY(10,3,1,100,TRUE) in Excel 365 or Excel 2021 and later | One formula fills 10 rows and 3 columns, with TRUE meaning whole numbers |
| You need random dates | Use RANDBETWEEN with two date values | Excel stores dates as serial numbers, so dates work like integers |
| You need numbers that stop changing | Copy the results and paste them as values | Formulas recalculate on every edit; pasted values stay put |
Before you start
- Check your Excel version. RANDARRAY works only in Excel 365 and Excel 2021 or later. In older versions, enter RANDBETWEEN in one cell and fill it down the column instead.
- Decide on the range and the type of number. The arguments depend on whether you need integers or decimals, and on the smallest and largest values you accept.
- Save a copy of the workbook. Once you paste values over a formula, the formula is gone from those cells, and you cannot get it back from the pasted numbers.
- Expect recalculation. RAND and RANDBETWEEN recalculate whenever any cell changes, which can slow a large workbook. Setting calculation to manual in Excel's calculation settings while you build the data avoids that.
- Know the limits. Excel's random numbers are generated by a formula and are not suitable for passwords, keys or any security purpose. Use them for test data and sample layouts only.
Generating random numbers in Excel with formulas, step by step
- Choose the output area. Pick an empty column, and decide the smallest and largest values you need, such as 1 and 100.
- Enter the whole-number formula. In the first cell, type =RANDBETWEEN(1,100) and press Enter, so the cell shows one random integer.
- Fill the formula down. Drag the fill handle at the cell's corner down the column, or copy the cell and paste it into the rest of the range, so every row gets its own value.
- Create decimals if needed. For values from 10 to 100 with two decimal places, enter =ROUND(RAND()*90+10,2) in the first cell, then fill it down. Change 90 and 10 to match your own range: the first number is the spread, and the second is the starting point.
- Fill a block in one step (Excel 365 or 2021+). Enter =RANDARRAY(10,3,1,100,TRUE) in one cell. Excel spills the results across 10 rows and 3 columns, and TRUE limits them to whole numbers. Leave the cells below and to the right empty so the results have room to spill.
- Create random dates. Enter =RANDBETWEEN(DATE(2024,1,1),DATE(2024,12,31)), then format the cell as a date. Change the two dates to your own start and end.
- Lock the results. Select the generated range, copy it, and paste it back as values. The numbers now stay fixed while you edit other cells.
- Confirm the file is saved. Save the workbook under a new name so you keep the formulas in one copy and the fixed values in another.
Check the result
Run these checks once the numbers are in place, before you use the data in a test or a document.
| Check | How to see it | If it's wrong |
|---|---|---|
| The range is correct | Enter =MIN(A1:A100) and =MAX(A1:A100), using your own cell range | Edit the arguments in the formula, then recalculate |
| Whole numbers are whole | Look for decimals in the column | Use RANDBETWEEN or add TRUE as the fifth argument of RANDARRAY |
| Values stopped changing | Edit an unrelated cell and see whether the numbers move | Paste the range as values, as described in step 7 |
| Repeats are acceptable | Scan the column or use COUNTIF on a value | Repeats are normal with random integers; see the FAQ for a no-repeat method |
| The formula is recognized | Look for #NAME? in the cell | Check the spelling, and confirm your Excel version supports the function |
🎯 Which approach fits you?
If you need one column of values, RANDBETWEEN with fill-down is usually the simplest route. If you need a whole table in one step and use Excel 365 or 2021 or later, RANDARRAY is the better fit. If the data will be shared or printed, paste the values before you send the file.
❓ FAQ
1. How do I get random numbers without repeats in Excel?
In Excel 365, you can shuffle a list of unique numbers with =SORTBY(SEQUENCE(100),RANDARRAY(100)), which returns the numbers 1 to 100 in random order. Each number appears once. Change 100 to the size of your list.
2. Are Excel random numbers safe to use for passwords?
No. Excel's random functions are not designed for security. Use them for sample data, classroom exercises and test sheets, and generate passwords or keys with a tool built for that purpose.
3. How do I generate random numbers that follow a normal distribution?
Use =NORM.INV(RAND(),50,10), where 50 is the mean and 10 is the standard deviation. Each cell then returns a value clustered around the mean, with fewer values far from it.
4. Why does my random number formula show #NAME?
Excel shows #NAME? when it does not recognize the function name. Check the spelling, such as RANDBETWEEN with no extra spaces, and confirm that RANDARRAY is supported in your Excel version.
📝 Summary
RANDBETWEEN covers most whole-number test data, RAND scaled to your range handles decimals, and RANDARRAY fills a block in newer versions of Excel. Paste the results as values when the numbers need to stay fixed, and check the minimum and maximum before you rely on them.
