You need thirty rows of test data for a demo, or to draw a prize among an event’s attendees. Inventing the values by hand is slow and it shows they are not random.
How to use Random Numbers Formula step by step
1. Choose what to draw
A whole number or a decimal between two limits, a date, a value from your list, a sequence with no repeats, a table of integers, a letter or a yes or no with a probability.
2. Set the limits
Give the minimum and maximum, the range holding your list or the cells with the dates. For decimals you choose how many to show.
3. Copy the formula
Paste it and drag it down. Every time Excel recalculates the values change; to keep one, copy it and paste it as a value.
The Step Count: Traditional Friction vs. KorexTools
Inventing test data by hand (4 steps)
- Think of a value for each cell.
- Type it into the sheet.
- Avoid letting a pattern show.
- Repeat until the rows are full.
With the KorexTools “Random Numbers Formula” builder (3 steps)
- You choose what to draw.
- You set the limits or the list range.
- You copy the formula and drag it down.
A random number in Excel changes every time the sheet recalculates
RANDBETWEEN returns a whole number between the minimum and the maximum you give, both included, and RAND returns a decimal between zero and one. With a small sum, that decimal scales to any range, and ROUND fixes how many decimals remain. Dates work the same way, because a date is a number.
To pick an item from a list you combine RANDBETWEEN with INDEX and ROWS, so the draw adjusts if you add rows. RANDBETWEEN can repeat results; for a sequence with no repeats you sort a run of numbers by random values, which needs Microsoft 365. These functions are volatile: they change on every recalculation.
Which option to pick
- •Test data or examples: whole number or decimal.
- •A draw among participants: pick from a list.
- •A random order with no repeats: the 365 sequence.
- •Samples or simulations: table of integers.
Limits from 1 to 100 and a list in A2:A20
| What you draw | Formula | What it returns |
|---|---|---|
| Whole number | =RANDBETWEEN(1,100) | A number from 1 to 100 |
| A name from the list | =INDEX($A$2:$A$20,RANDBETWEEN(1,ROWS($A$2:$A$20))) | A random name |
| Ten numbers, no repeats | =SORTBY(SEQUENCE(10),RANDARRAY(10)) | 1 to 10 in random order |
Common Mistakes and How to Avoid Them
- ⚠️Leaving the formula live after the draw so the winner changes on recalculation.
- ⚠️Expecting RANDBETWEEN not to repeat numbers.
Frequently Asked Questions
Why do my random numbers change by themselves?
Because RAND and RANDBETWEEN are volatile and recalculate with every change in the workbook. To freeze them, copy the cells and paste as values.
How do I generate random numbers with no repeats?
With the no-repeat list option, which sorts a sequence by random values. It needs Microsoft 365; in earlier versions you have to rely on a helper column with RAND and sort it.
How do I pick a random name from a list?
With the pick-from-a-list option: you give the range and the formula returns a different item each time it recalculates. It is handy for draws and for choosing who opens a meeting.
What I would do if I were in your shoes
«A draw that changes when you edit another cell is not a draw: freeze the result as soon as it comes out.» — DataKorex Engineering Team.