by In this tutorial, you will learn how to create a number sequence in Excel with formulas. Additionally, we'll show you how to auto generate a series of Roman numbers and random integers - all by using a new dynamic array SEQUENCE function.
The times when you had to put numbers in sequence in Excel manually are long gone. In modern Excel, you can make a simple number series in a flash with the Auto Fill feature. If you have a more specific task in mind, then use the SEQUENCE function, which is specially designed for this purpose.
Excel SEQUENCE function
The SEQUENCE function in Excel is used to generate an array of sequential numbers such as 1, 2, 3, etc.
It is a new dynamic array function introduced in Microsoft Excel 365. The result is a dynamic array that spills into the specified number of rows and columns automatically.
The function has the following syntax:
Where:
Rows (optional) - the number of rows to fill.
Columns (optional) - the number of columns to fill. If omitted, defaults to 1 column.
Start (optional) - the starting number in the sequence. If omitted, defaults to 1.
Step (optional) - the increment for each subsequent value in the sequence. It can be positive or negative.
- If positive, subsequent values increase, creating an ascending sequence.
- If negative, subsequent values decrease, producing a descending sequence.
- If omitted, the step defaults to 1.
The SEQUENCE function is only supported in Excel for Microsoft 365, Excel 2021, and Excel for the web.
Basic formula to create a number sequence in Excel
If you are looking to populate a column of rows with sequential numbers starting at 1, you can use the Excel SEQUENCE function in its simplest form:
To put numbers in a column:
To place numbers in a row:
Where n is the number of elements in the sequence.
For example, to populate a column with 10 incremental numbers, type the below formula in the first cell (A2 in our case) and press the Enter key:
=SEQUENCE(10)
The results will spill in the other rows automatically.
To make a horizontal sequence, set the rows argument to 1 (or omit it) and define the number of columns, 8 in our case:
=SEQUENCE(1,8)
If you'd like to fill a range of cells with sequential numbers, then define both the rows and columns arguments. For instance, to populate 5 rows and 3 columns, you'd use this formula:
=SEQUENCE(5,3)
To start with a specific number, say 100, supply that number in the 3rd argument:
=SEQUENCE(5,3,100)
To generate a list of numbers with a specific increment step, define the step in the 4th argument, 10 in our case:
=SEQUENCE(5,3,100,10)
Translated into plain English, our complete formula reads as follows:
SEQUENCE function - things to remember
To efficiently do a sequence of numbers in Excel, please remember these 4 simple facts:
- The SEQUENCE function is only available with Microsoft 365 subscriptions and Excel 2021. In Excel 2019, Excel 2016 and earlier versions, it does not work since those versions do not support dynamic arrays.
- If the array of sequential numbers is the final result, Excel outputs all the numbers automatically in a so-called spill range. So, be sure you have enough empty cells down and to the right of the cell where you enter the formula, otherwise a #SPILL error will occur.
- The resulting array can be one-dimensional or two-dimensional, depending on how you configure the rows and columns arguments.
- Any optional argument that is not set defaults to 1.
How to create a number sequence in Excel - formula examples
Though the basic SEQUENCE formula does not look very exciting, when combined with other functions, it takes on a whole new level of usefulness.
Make a decreasing (descending) sequence in Excel
To generate a descending sequential series, such that each subsequent value is less than the preceding one, supply a negative number for the step argument.
For example, to create a list of numbers starting at 10 and decreasing by 1, use this formula:
=SEQUENCE(10, 1, 10, -1)
Force a two-dimensional sequence to move vertically top to bottom
When populating a range of cells with sequential numbers, by default, the series always goes horizontally across the first row and then down to the next row, just like reading a book from left to right. To get it to propagate vertically, i.e. top to bottom across the first column and then right to the next column, nest SEQUENCE in the TRANSPOSE function. Please note that TRANSPOSE swaps rows and columns, so you should specify them in the reverse order:
For example, to fill 5 rows and 3 columns with sequential numbers starting at 100 and incremented by 10, the formula takes this form:
=TRANSPOSE(SEQUENCE(3, 5, 100, 10))
To better understand the approach, please have a look at the screenshot below. Here, we input all the parameters in separate cells (E1:E4) and create 2 sequences with the below formulas. Please pay attention rows and columns are supplied in different order!
Sequence that moves vertically top to bottom (row-wise):
=TRANSPOSE(SEQUENCE(E2, E1, E3, E4))
Regular sequence that moves horizontally left to right (column-wise):
=SEQUENCE(E1, E2, E3, E4)
Create a sequence of Roman numbers
Need a Roman number sequence for some task, or just for fun? That's easy! Build a regular SEQUENCE formula and warp it in the ROMAN function. For example:
=ROMAN(SEQUENCE(B1, B2, B3, B4))
Where B1 is the number of rows, B2 is the number of columns, B3 is the start number and B4 is the step.
Generate an increasing or decreasing sequence of random numbers
As you probably know, in new Excel there is a special function for generating random numbers, RANDARRAY, which we discussed a few articles ago. This function can do a lot of useful things, but in our case it cannot help. To generate either an ascending or descending series of random whole numbers, we'll be needing the good old RANDBETWEEN function for the step argument of SEQUENCE.
For example, to create a series of increasing random numbers that spills in as many rows and columns as specified in B1 and B2, respectively, and start at the integer in B3, the formula goes as follows:
=SEQUENCE(B1, B2, B3, RANDBETWEEN(1, 10))
Depending on whether you want a smaller or bigger step, supply a lower or higher number for the second argument of RANDBETWEEN.
To make a sequence of decreasing random numbers, the step should be negative, so you put the minus sign before the RANDBETWEEN function:
=SEQUENCE(B1, B2, B3, -RANDBETWEEN(1, 10))
Note. Because the Excel RANDBETWEEN function is volatile, it will generate new random values with every change in your worksheet. As the result, your sequence of random numbers will be continuously changing. To prevent this from happening, you can use Excel's Paste Special > Values feature to replace formulas with values.
Excel SEQUENCE function missing
Like any other dynamic array function, SEQUENCE is only available in Excel for Microsoft 365 and Excel 2021 that support dynamic arrays. You won't find it in pre-dynamic Excel 2019, Excel 2016, and lower.
That's how to create sequence in Excel with formulas. I hope the examples were both useful and fun. Anyway, thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel SEQUENCE formula examples (.xlsx file)
437 comments
Good Day
We would like to have the following data on a separate line for a raffle draw. They unfortunately did the numbering as :
31100-31127
32656-32697
34729-34750
90784-90828
92159-92198
333-336
851-856
116-119
867-858
859
2070-2079
2709-2713
2569-2573
337-340
341-343
860
861-861
Is there a way to have them in one column in order?
Hi!
I'm really sorry, we cannot help you with this.
Hello,
I suggest a helper column in column B with this formula (and copy down):
=IF(ISNUMBER(A1),A1,VALUE(LEFT(A1,FIND("-",A1)-1)))
Then in cell C1, have a sort formula =SORT(A1:B1000,2)
Hope this solves the problem.
André
Hello sir, how about sequence with text in rows, like:
var 1 var 2 var 3
And so on.
Thank you for your help
Hello!
Please re-check the article above since it covers your task. The SEQUENCE function works on a row as well.
I've tried
="var "&SEQUENCE(1,50,1,1)
But it doesn't work
Hi!
It works for me. What doesn't work for you? SEQUENCE function available in Microsoft Excel 365.
how about
KD1-20
KD21-40
KD41-60
and so on.
tnx
Hi!
Try the following formula:
="KD"&CEILING(SEQUENCE(100,1,1,20)/1,1)&"-"&CEILING(SEQUENCE(100,1,1,20)/1,1)+19
i forgot something formula for
1kd 1 - 20
2kd 21 - 40
3kd 41 - 60
4kd 61 - 80
thank u sir godbless
Hello Sr
how can I do a sequence of rows every 8 repetitive number, every 8 rows same number, then the next one 8 times, etc. Example: the patern is same number 8 rows or 8 times, then next number ther 8 rows or times:
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
thank you
Hello!
Please use the following formula
=CEILING(SEQUENCE(800,1,1,1)/8,1)
thank you
Thanks
I am currently stuck on a problem while creating an MLB pitching model. I would like to distribute the number of batters faced across the 9 positions in a batting order. Lets say a pitcher faces 23 batters during his out outing I would like to distribute that across the 9 batting order spots in sequential order. In doing this manually I know the pitcher would face the first 5 batters 3 times and the 6-9th batter 2 times. How can I create a running tally that distributes those 23 batters across the 9 batting order spots -- more simply put how can I tally the number 23 across 9 cells in sequential order? Thank you in advance if you can solve this riddle!
I want to generate the following pattern in excel cells. Would it be possible
0000000000
1000000000
1100000000
1110000000
1111000000
....
I want to generate this kind of number but I am having difficulty. Anyone can help me?
=VLOOKUP(G2,O2:P23,2,FALSE)
=VLOOKUP(G3,O2:P23,2,FALSE)
=VLOOKUP(G4,O2:P23,2,FALSE)
=VLOOKUP(G5,O2:P23,2,FALSE)
=VLOOKUP(G6,O2:P23,2,FALSE)
=VLOOKUP(G7,O2:P23,2,FALSE)
=VLOOKUP(G8,O2:P23,2,FALSE)
=VLOOKUP(G9,O2:P23,2,FALSE)
=VLOOKUP(G10,O2:P23,2,FALSE)
The G Is the one only rising. Thank you to anyone who can help.
Hi!
When you copy the formula down the column, G2 will change to G3, G4, and so on.
Hi,
1 have 5 digits number, for example 01234, from that number I want to generate 2 digits number for all the possible sequence, so 00, 01, 02, 03, 04, 10, 11, 12, 13, 14, 20, 21,... and so forth.
Any way to do this? TYIA
would it be possible to sequence numbers but end on the number that is in the next column.
example:
1 6
2 6
3 6
4 6
5 6
6 6
1 3
2 3
3 3
1 5
2 5
We are using the info for a data merge for bundles. so the outcome would be "Bundle 1 of 6"
Thank you
Hello!
You have not written what is the source data. I am assuming column B. Perhaps this formula will work -
=COUNTIF($B$1:B1,B1)
After that you can copy this formula down along the column.
If this is not what you wanted, please describe the problem in more detail.
Sorry, Can you tell I don't know what I'm doing?
I was able to plug this in and worked. But I do see when I scroll down and some of the "bundles" are combined together and then go back to correct sequence. what could cause this?
example: Source is column B.
1 8
2 8
3 8
4 8
5 8
6 8
7 8
8 8
8 7
9 7
10 7
11 7
12 7
13 7
14 7
1 9
2 9
3 9...
Hello!
What formula are you using?
The formula used was =COUNTIF($B$1:B1,B1)
After some testing I realized that the repeating bundles would start from the previous similar bundles. see below.
example:
1 5
2 5
3 5
4 5
5 5
1 7
2 7
3 7
4 7
5 7
6 7
7 7
6 5
7 5
8 5
9 5
10 5
What if we want this
1 6 11
2 7 12
3 8 13
4 9 14
5 10 15
Sequence reference for Bookmark pdf
1st row is 1 - 5 (sequence)
2nd row is 2 - 2
3rd row is 2 - 4
4th row is 4 - 5
5th row is 6 - 7 (sequence)
6th row is 8 - 9 (sequence)
7th row is 9 - 11
8th row is 10 - 11 (sequence)
9th row is 14 - 15 (sequence)
How to check missing sequence 12 - 13
For above type of page range.
Hello,
I am trying to have a One Column, with the following parameters:
First 20 Columns (1-20) is 1
Next 30 Column (21-50) is 2
Next 40 Column (51-90) is 3 and so forth.
Also I want to make it dynamic where I can change the columns i.e Instead of having the first 20 columns to 1, I can plug into a cell 30 and the first 30 column is 1 and the rest adjust accordingly.
I am create a training plan and I can decide to have the first 20 go to training in Wave 1 or first 30 to Wave 2 etc.
Please how can I create this formula.
Thanks for your help.
Oluseyi
This is perfect - Thank You!
how can I generate this kind of Sequence
Chapter: 1 - 3
Chapter: 4 - 6
Chapter: 7 - 9
.
.
.
.
Hello,
What would the formula be to create this sequence?
A1001A A1001B A1001C A1001D A1002A A1002B A1002C A1002D A1003A A1003B A1003C A1003D
A1004A A1004B A1004C A1004D A1005A A1005B A1005C A1005D A1006A A1006B A1006C A1006D
Where the last letter in the series ends with "D", but the preceding number will increase as the sequence proceeds.
Thanks
Hello!
If I understand your task correctly, the following formula should work for you:
="A"&(1000+CEILING(SEQUENCE(1000,1,1,1)/4,1)) & CHOOSE(TRUNC(MOD((ROW(A1:A1000)-1)/1,4)+1),"A","B","C","D")
Hope this is what you need.
I really prefer the sequence function. However, my computer, Microsoft 2016, doesn't exist the function. I accessed to the internet and i found that Sequence Function can be only supported by Microsoft 360. How can i install the function and does it affect my computer if i install it? Thank you very much
Hi thank you so much for your informative guidance.
This is pdf index page sequence.
1-4
5-88
(37-38
60-61
61-63
64-65
64-64)
89-101
(91-92
94-95)
103-108
107-108
How to check missing sequence 102...
eg. sequence of numbers 1-1, 2-2, 4-5. Number 3 was skipped..
I have 4 sheets , the number of rows is random for each sheet. How to make the sequential number continue from sheet 1 to sheet 4 meanwhile the number of rows is random .
Hello!
Find the maximum number on the previous sheet using the MAX function and add 1 to it.
=MAX(Sheet2!A:A)+1
Then, on the current sheet, simply add 1 to this number.
Hello,
I hope you can help. I am trying to identify the number of transactions per date and per supplier, and create a sequence from this.
For example, on 20th May 2021, 1 transaction was from British Gas, 3 transactions were put through from Amazon, and 1 from Sainsbury's. I assume a formula can cluster per unique supplier whilst incrementing sequentially, I just haven't cracked it yet:
20210520-01
20210520-02
20210520-02
20210520-02
20210520-03
Essentially, I want to match 3 transactions to one from a Cash Book to a Bank Statement. The CB would list 3 individual items from Amazon and the Bank would show one transaction. I would like to be able to cross-reference automatically/dynamically. That way I can SUMIF and MATCH to reconcile (hopefully).
If there is a better way, please say! Currently, I am working on historic 'chunks' of formula and concatenating. I thought it was time to get up to date.
Also, I can only get the date sequence as 44336 when appearing in a formula. That's fine if it has to be this way, I'd just prefer it to be Gregorian if possible. Do you know if I can use a format for this?
Much appreciated in anticipation.
Hello!
I’m sorry but your task is not entirely clear to me.
You can apply a standard or custom date format to the 44336 as described in this guide.
I want sequence something like this:
1
2
1
2
1
2
1
2
.
.
.
.
.
etc.
how to achieve this?
Hi!
A sequence is a series of consecutive numbers: 1,2,3 ... or 3,5,7 ... Each next number is not less than the previous one.
Your example is not a sequence. Write 1 and 2 in the cells and copy them down the column.