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)
433 comments
what would be the best formula to get gaps on this sequence
220-0007
220-0009
221-0001
223-0001
223-0002
223-0004
to return the exact missing numbers
i.e 220-0008 & 223-0003 are missing.
what would be the best formula to get gaps on this sequence
220-0007
220-0009
221-0001
223-0001
223-0002
223-0004
to return the exact missing numbers
i.e 220-0008 & 223-0003 are missing.
Hello!
Write in another table the sequence that you think is correct. Then compare these columns using the Compare Tables tool.
There is no pattern in your data, so I cannot suggest a formula.
Hello Sir,
Thanks for your help in advance. Every time a record is added to excel I have to generate a sequence number automatically. Sequence=Stage.Phase.Next Number - Can you please help how to do this in excel.
Stage Phase Sequence
1 1 1.1.1
1 1 1.1.2
1 2 1.2.1
2 2 2.2.1
2 3 2.3.1
2 3 2.3.2
Hi!
I don't see any pattern in your data. If it exists, please describe it.
Pattern is Stage.Phase.Next Incremental number in that Stage.Phase
Stage.......Phase.......Sequence
1000...........1..............1000.1.1
1000...........1..............1000.1.2
1000...........1..............1000.1.3
2000...........2..............2000.2.1
2000...........2..............2000.2.2
1000...........2..............1000.2.1
1000...........2..............1000.2.2
Hope it makes sense...thank you!
I did it successfully using COUNTIF. Thank you for your time!
Correction to previous post -
The format for both methods in my first post got off-alignment when I submitted.
Here is my attempt to correct the formatting of the 2 methods so that it's readable (I hope it doesn't get messed up again).
Method 1
========
Tab Cell E1
==== ========
01 01-Jan-2022
02 =('01'!E1+1)
03 =('02'!E1+1)
04 =('03'!E1+1)
05 =('04'!E1+1)
Method 2
========
Tab Cell E1
==== ========
01 01-Jan-2022
02 =('01'!E1+1)
03 =('01'!E1+2)
04 =('01'!E1+3)
05 =('01'!E1+4)
I was searching Google for a better way to do the following and your website popped up. I hope you can help and that it’s not something that can’t be done.
I have a workbook for each month with either 28,30 or 31 tabs/sheets – one for each day depending on the month.
I have a date field in the same cell (E1) for each tab - date format is dd-mmm-yyyy.
I would like the same formula for cell E1 in each tab that increments the date without me having to edit the formula in cell E1 in each tab to change the tab name (Method 1) or increment number (Method 2) to point to the previous tab and add the increment number.
I have the following 2 formulae that works but I have to change each tab (from tab ‘02’ onwards) to either point to the previous tab name and add 1 or add an increasing increment number to tab name ‘01’. Is there a way to specify the previous tab without having to actually name it – ie. is there a variable name that represents the previous tab ? Here is what currently works, but to me is a bit clumsy.
Thanks, in advance, for any suggestions.
Method 1
Tab Name Cell E1
======== ==========
01 01-Jan-2022
02 =('01'!E1+1)
03 =('02'!E1+1)
04 =('03'!E1+1)
05 =('04'!E1+1)
etc etc
Method 2 - it also works if I do this :
Tab Name Cell E1
======== ===========
01 01-Jan-2022
02 =('01'!E1+1)
03 =('01'!E1+2)
04 =('01'!E1+3)
05 =('01'!E1+4)
etc etc
Hello!
You can only get the name of the previous worksheet using a VBA macro. Regular Excel formulas can only get the name of the sheet where the cell is located:
=MID(CELL("filename",Sheet1!A2),SEARCH("]",CELL("filename",Sheet1!A2))+1,31)
Method 1
Tab Name . . . . Cell E1
======== . . . . ===========
01 . . . . . . . . 01-Jan-2022
02 . . . . . . . . =('01'!E1+1)
03 . . . . . . . . =('02'!E1+1)
04 . . . . . . . . =('03'!E1+1)
05 . . . . . . . . =('04'!E1+1)
etc . . . . . . . . etc
Method 2 - it also works if I do this :
Tab Name . . . . Cell E1
======== . . . . ===========
01 . . . . . . . . 01-Jan-2022
02 . . . . . . . . =('01'!E1+1)
03 . . . . . . . . =('01'!E1+2)
04 . . . . . . . . =('01'!E1+3)
05 . . . . . . . . =('01'!E1+4)
etc . . . . . . . . etc
Hi, is it possible to arrange the sequence formula to add number until the end of the table and automatically add a number if the table row range is extended?
Hello!
In the first argument of the SEQUENCE function, you can use the COUNTA function, which will count the number of rows with data in the table. When adding. data in column A, the SEQUENCE function will add one more value.
=SEQUENCE(COUNTA(A:A),1,1,1)
I hope my advice will help you solve your task.
I am attempting to create a series of mobile numbers. The first four digits and final two digits should not change.
Example:
567-000-042
567-000-142
567-000-242
567-000-343
The series should create a list of all possible combinations from 567-000-042 to 567-999-942 (only the 4th-7th digits will change. The first 3 digits and final two digits should not change.) Thank you.
Hello!
Use the TEXT function to write the number in the desired format.
="567-"&TEXT(CEILING(SEQUENCE(1000,1,0,1),1),"000-0")&"42"
I hope my advice will help you solve your task.
Hi there,
I need to create a sequential list of barcode numbes - 20 digits, the first two digits being "00"
What do i need to do to the formula to make that come out, without getting rid of the two "00" at the front of the number
Hello!
To have a number start with "00", save number as text. You can use the & operator to do this:
"00" & 123456
I hope it’ll be helpful.
HELLO SIR
EMP ID Generate :- NAME WITH NUMBER
PRAKASH P0001
PRATIK P0002
POOJA P0003
I don't know how I can.
Hi!
Pay attention to this comment.
I hope it’ll be helpful.
How do I write a formula to have 0 to 9 followed by A to Z in a sequence and then to be copied to let's say copied to 180 rows?
0
1
2
3
4
5
6
7
8
9
0
A
B
C
etc..
I want to create following sequence
01-01-21
01-01-21
01-01-21
02-01-21
02-01-21
02-01-21
03-01-21
03-01-21
01-01-21
and so on
i need to create chart,
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
leave a blank and then generate
21 22 23 24 25
26 27 28 29 30
31 32 33 34 35
36 37 38 39 40
blank cell
41 42 43 44 45
46 47 48 49 50
51 52 53 54 55
56 57 58 59 60
suggest pls
Hello,
I have a question regarding the "n" in the SEQUENCE Function.
Can it be unknown?
From a table, I would like the formula to list all the data that are above 13%. I can do this using the INDEX, SORT and FILTER functions. But I do not know how many people will be above 13%. Is there a solution to this?
My formula is as per the below:
=INDEX(SORT(FILTER(Master!F4:AC248,Master!AC4:AC248>=13%),24,-1),SEQUENCE(20),{1,24})
Thanks,
Matt
Hello!
In the SEQUENCE function, specify the maximum number of values (for example, 245). To ignore errors, use the IFERROR function.
=IFERROR(INDEX(SORT(FILTER(Master!F4:AC248,Master!AC4:AC248>=13%),24,-1),SEQUENCE(245),{1,24}),"")
Hope this is what you need.
Hi Alexander,
Thank you for taking the time to reply and apologies for the delay of my answer.
Unfortunately this does not work for me so had to change the formula to the below:
=INDEX(SORT(FILTER(Master!B4:AC243,(Master!S4:S243="GBP")*(Master!M4:M243=""),"No results"),28,-1),SEQUENCE(20),{5,28})
So what this does is giving me the 20 top percentages. But only 8 of them are above 13%.
How can I make sure that with this formula, it removes the percentages that are under 13%?
(if this is possible).
Thanks,
Matt
Hi!
Without seeing your data it is difficult to give you any advice.
If you are fetching data from column AC, add a filter condition to the formula:
FILTER(Master!B4:AC243,(Master!S4:S243=”GBP”)*(Master!M4:M243=””)*(Master!AC4:AC243>13%),”No results”)
I hope I answered your question.
Hi Alexander,
Works a treat! Thank you so much!
Matt
I want to enter the following in the column , so kindly help me out:
A1 : 0-1
A2 : 1-2
A3 : 2-3
and so on.
whereas A1, A2, A3 are the address of the Column and the numerics are the value to be entered.
Hello
I would like to have this numbering sequence:
01-G
02-G
03-G
Thanks in advance for the help
Hello!
You can use this formula:
=TEXT(SEQUENCE(10,1,1,1),"00")&"-G"
Add text to the sequence of numbers using the & operator.
Hi Sir,
I would like to make following sequence, which would be changing based on names in other column. The number of rows with the same name is random - sometimes 5 rows, sometimes 1 row:
2021-1000 John
2021-1000 John
2021-1000 John
2021-1001 Tim
2021-1001 Tim
2021-1001 Tim
2021-1002 James
2021-1002 James
2021-1003 Michael
I would appreciate your help.
Hi!
What you want to do is not sequence. This is a random number of duplicates. I'm really sorry, looks like this is not possible with the standard Excel options.
Hello, I am trying to make a SEQUENCE based on the text in the cell next to it.
Column B can be either IC, IG, IB, or any other 2 letter
Column C is a number list 21001 21002 21003 and so on.
However the number can repeat based on the letters in Column B. So I need the sequence to be based on the next available number that relates to the letters in column B.
I hope that make sense
Hello!
To make a sequence of numbers with a condition, use the formula
=21000+COUNTIF($B$2:B2,B2)
Hope I understood the problem correctly.
It did work thank you. Now if I can get my co workers to stop skipping number
Hello I had a follow up on this. I changed the formula a little to exclude texts from the count so it looks like this now.
=21000+COUNTIFS('2022'!$D$3:D3,D3,'2022'!$K$3:K3,"CO",'2022'!$K$3:K3,"NCCO")+1
My problem is when I go to the next cell the is jumps numbers equal to how many cells are used
EX.
=21000+COUNTIFS('2022'!$D$3:D5,D4,'2022'!$K$3:K5,"CO",'2022'!$K$3:K5,"NCCO")+1
=21000+COUNTIFS('2022'!$D$3:D7,D5,'2022'!$K$3:K7,"CO",'2022'!$K$3:K7,"NCCO")+1
=21000+COUNTIFS('2022'!$D$3:D9,D6,'2022'!$K$3:K9,"CO",'2022'!$K$3:K9,"NCCO")+1
^ ^ ^
I put arrows where the numbers jump. Why would it do that and not follow the order? I can not seem to stop this.
I am constantly having to create numbering sequences that look like this:
100 000001
100 000002
three numbers, space and then 6 numbers with only the last 6 numbers increasing. What would be the formula to achieve that?
Thanks
Hi!
You can use a custom number format to repeat zeros.
Use the TEXT function to display a number in this custom format.
="100 " & TEXT(SEQUENCE(100,,1,1),"*00000##")
Hope this is what you need.
I am in need of a way to have a number increase by one each day for a production report. Example
4 departments in separate columns. Above each heading is the number of days worked. Is there a way increase those days by 1 automatically each day?
Thanks in advance!
Is there a way to sequence numbers if the numbers look like this------
PI-2021-07-0480
PI-2021-07-0481
Please advise -
Dawn
Hi,
what if I have number series like
[1,2,3,4,5], [11,12,13,14,15], [21,22,23,24,25] and so on in one column until some hundred,
is there any way to do this?
thank you!