SEQUENCE and RANDBETWEEN in Excel: how to use it
SEQUENCE makes a list of consecutive numbers in one go, such as 1 to 10. RANDBETWEEN gives a random whole number between two limits.
Try the first exercises for freeIn the lesson SEQUENCE, RANDBETWEEN and RANDARRAY
What do SEQUENCE and RANDBETWEEN do?
SEQUENCE makes a whole series of consecutive numbers in one go. You say how many rows, and if you like how many columns, which number to start at and what step to use. The numbers spill over into the cells below or next to it automatically.
RANDBETWEEN gives a random whole number between two limits, like a dice between 1 and 6. RAND gives a random number from 0 to 1. Both pick again every time Excel recalculates.
When do you use SEQUENCE and RANDBETWEEN?
- A table of test data with 10 rows and 3 columns of scores from 1 to 20: =RANDARRAY(10,3,1,20,TRUE).
- Numbering from 1 to 50 in one formula: =SEQUENCE(50).
- Weeks 1 to 52 next to each other in a row: =SEQUENCE(1,52).
- Making test data, such as random scores between 1 and 20: =RANDBETWEEN(1,20).
- Drawing a random winner from 30 participants: =RANDBETWEEN(1,30).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
A series of consecutive numbers that spills over automatically.
Filled in: =SEQUENCE(5)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SEQUENCE | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SEQUENCE |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
rows | How many numbers underneath each other. | 5 |
[columns] | Optional: how many numbers next to each other. 1 by default. | left out |
[start] | Optional: the first number. 1 by default. | left out |
[step] | Optional: how much is added each time. 1 by default; with -1 you count down. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
SEQUENCE and RANDBETWEEN step by step
A2 contains =SEQUENCE(5). The numbers 1 to 5 spill into the cells below automatically. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click A2, the first cell where the numbering should go, and type SEQUENCE with a bracket.
=SEQUENCE( - Type how many numbers you want underneath each other: 5.
=SEQUENCE(5 - Close the bracket and press Enter: the numbers 1 to 5 spill over into the cells below automatically.
=SEQUENCE(5)
More examples
In steps of 10
A2 contains =SEQUENCE(4,1,10,10). Four numbers in one column, starting at 10, adding 10 each time: 10, 20, 30 and 40.
Next to each other
B1 contains =SEQUENCE(1,4). One row and four columns: weeks 1 to 4 run to the right.
Counting down
A2 contains =SEQUENCE(5,1,5,-1). With -1 as the step, you count down: 5, 4, 3, 2 and 1.
A sequence in steps
=SEQUENCE(4,1,10,10) gives 10, 20, 30 and 40: four numbers in one column, starting at 10, adding 10 each time.
Random numbers
=RANDBETWEEN(1,6) rolls a dice, as it were. RAND() gives a random number between 0 and 1. Both pick again every time the worksheet recalculates, for instance when you type something or press F9.
Where it often goes wrong
- SEQUENCE only works in Excel 2021 and 365. In an older version you type 1 and 2 and drag the fill handle down.
- If you want random numbers to stop changing, copy them and paste them as values.
Practise this in the lesson SEQUENCE, RANDBETWEEN and RANDARRAY. The first exercises are free.
Frequently asked questions
Why do I get #SPILL!?
There's already something in the cells where the sequence has to go. Clear those cells.
Read next
ROUNDCEILING and FLOORMOD and INTLARGE and SMALLMEDIAN and MODE