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)
431 comments
Hello sir plz solve this problem in excel formula
1
1
1
1
2
2
2
2
3
3
3
3
is there a way to do this in excel? thanks
Hello! To create a sequence of repeating numbers, use the CEILING function along with SEQUENCE and specify the number of repeats in the formula. Please try the following formula:
=CEILING(SEQUENCE(20,1,1,1)/4,1)
00-00-98
00-00-99
00-01-00
can i use sequence in this?
Hi! Create a sequence of numbers as described in the article above. Using the TEXT function, write these numbers as text with six digits with leading zeros. Using the REPLACE function, insert "-" in the text.
=REPLACE(REPLACE(TEXT(SEQUENCE(10,1,98,1),"000000"),3,0,"-"),6,0,"-")
Hello
is there a formula to create numbers in cells for instance put 1 in column A and then in Column B it would put 1-2, then skip a number and put 3-4,6-7,9-10,12-13. trying to set up for custom print pages. and another code to skip 2 pages for instance 1-2,5-6,9-10,13-14,17-18. but would be put in column A 1 and in cell A2 put a 2 there and it would go in sequence like that.
Thank you
Kenny
Hi! Please re-check the article above since it covers your case. Use the SEQUENCE function to create a sequence of numbers. For example:
=SEQUENCE(10,1,1,4)
Hello sir plz solve this problem in excel formula
As a like number one coulum and wants to be A 4789 second number 792
Etc every cells drag the formula but automatic every number combine .
A=4
A=7
A 8
A 9
B 7
B 9
B2
C 6
C3
C9
C2
E 7
E 8
G 1
G 4
To extract each symbol from the text into a separate cell, you can use the MID function. For example:
=MID(A1,ROW(A1:A20),1)
I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
Hi!
Looking for a formula for a sequence (seems basic but somehow have not been able to figure it out):
24-1
24-2
24-3
The "24" as in the year, so for next years it would need to be 25, and numbers always start at 1 and could go anywhere from 80 to 150 to end (used to number closed files).
Any guidance is greatly appreciated.
Hi! To repeat 100 times the number 24 and then 100 times the number 25, use this formula:
=CEILING(SEQUENCE(200,1,1,1)/100,1)+23
Combine sequences of numbers and text using the CONCATENATE function. For example:
=CONCATENATE(CEILING(SEQUENCE(200,1,1,1)/100,1)+23,"-",SEQUENCE(200,1,1,1))
Thank you so much for your guidance. After trying your formulas and getting the #SPILL! message, I realized that it wasn't going to work on an Excel table, and had to convert it to range (it was the only way the formulas would work, I just don't understand why?).
Hi! This article contains useful information about your issue: #SPILL! error in Excel table.
Hi,
I'm after some help. I want to create a project reference number in excel The first will read GBP1000M-24 This will be GBP1000 03/24, the next one would read GBP100103/24 or to could be GBP100104/24 and so on....
Many thanks
Hi! Your 4 examples are not a sequence of numbers. If you see a pattern in them, describe it. I suggest you study the above article and comments. You will find many examples. Or describe your problem in detail.
This would be really usual if you could spill into a different column. So if you had a list of 60 names in column B and you wanted to do those in three sets of 20, yould need the spill area to be Column A (Name in B), Column C (Name in D) and Column E (Name in E).
Sorry, I have no idea exactly what the task is. To understand what you want to do, give an example of the source data and the expected result.
Hi Alexander,
I'm trying to implement a sequence in a set of (same sized) merged cells.
For this i'm getting a #spill! error.
Is there any way of implementing a sequence function in a set of merged cells?
Thanks in advance!
Hi! Your problem is described here: Spill range contains merged cells.
Hi I need to make a number sequence for 500 items.
1000-7300
1001-7300
1002-7300
is there a way to do this in excel?
Hi! There are many solutions to your issue in the comments of this article. Create a sequence of numbers and combine with a text string. For example:
=SEQUENCE(10,1,1000,1)&"-7300"
Hi, how do I create a sequence just like in my sample
50-34386-1582 S. 2000
Hi! I don't see any sequence in your example. If it is there, please describe it.
Bonsoir,
comment créer cette séquence :
LS-001/24
LS-002/24
LS-003/24
...
....
...
LS-500/24
Hi! Write numbers with leading zeros using TEXT function. Combine strings of text by using the & operator or any other method. Read more: Excel CONCATENATE function to combine strings, cells, columns.
The formula might look like this:
="LS-"&TEXT(SEQUENCE(10,1,1,1),"000")&"/24"
Hi! I'd like to create rows that has a pattern like this...
00001-00010
00011-00020
00021-00030
00031-00040
up to 15990-16000
I was wondering which formula to use. Thnak you!
Hi! Use the TEXT function to write numbers with leading zeros. Combine text strings using the & operator or other method.
For example:
=TEXT(SEQUENCE(10,1,1,10),"00000") & "-" & TEXT(SEQUENCE(10,1,10,10),"00000")
Thank you for your help!
Need sequence like below: could you please help me.
1 01-02-2024 S1
2 01-02-2024 S2
3 01-02-2024 S3
4 02-02-2024 S1
5 02-02-2024 S2
6 02-02-2024 S3
7 03-02-2024 S1
8 03-02-2024 S2
9 03-02-2024 S3
Hi! As I wrote earlier in the comments, you can create a sequence of repeating numbers or dates using the CEILING + SEQUENCE function.
To create a repeating sequence of numbers from 1 to 3, use the MOD and TRUNC functions.
Try these formulas:
=DATE(2024,2,1)+CEILING(SEQUENCE(100,1,1,1)/3,1)-1
="S"&TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,3)+1)
I hope this will help.
is there any logic to create a conditional sequence as shown below?
Sl.No. Ref
1.1 aa
1.2 aa
1.3 aa
2.1 ab
2.2 ab
3.1 abc
3.2 abc
3.3 abc
3.4 abc
Hi! If there is some logical sequence in your data, describe it. Then I can understand what you want to do. Also note that you can create a sequence of digits using the SEQUENCE function.
mentioned is the sample of logical sequence.
Sl.No. Band Product
1.1 Dell Laptop
1.2 Dell Monitor
1.3 Dell NUC
2.1 Lenovo Laptop
2.2 Lenovo Tablet
3.1 Apple i-Phone
3.2 Apple i-Pad
3.3 Apple i-Mac
3.4 Apple i-Watch
As I said, I don't see any logical sequence in your data. Describe it if you can.
I'm using the SEQUENCE function to make a dynamic calendar that is auto populated by the year input. My formula is =SEQUENCE(1,7,DATE(C2,1,1)-WEEKDAY(DATE(C2,1,1))+1,1). It works out great however I would like the sequence to skip every other row so I have room to add items to my calendar. How can I do this?
Hi! Based on your description, it is hard to completely understand your task. To understand what you want to do, tell me what is written in cell C2 and give an example of the desired result.
i believe he is looking for
a1 15 jan
a2
a3 22 Jan
a4
a5 29 Jan
there is an empty line after each auto filled seq line, so we can fill in information/ make notes like how we have on paper calendar
Hi! Specify the starting date using the DATE function. Using the formula CEILING(SEQUENCE(50,1,0,1)/2,1)*7, add the numbers 0 7 7 14 14 14 21 21 21 and so on consecutively to this date. This formula will generate a sequence of 50 numbers, starting from 0. The CEILING function rounds each number in the sequence up to the nearest integer that is divisible by 1. This effectively rounds up each number to the nearest whole number. The result is then multiplied by 7.
The formula (IF(MOD(SEQUENCE(50),2)=0,0,1)) will generate a sequence of numbers 0 1 0 1 0 1 0 1 0 1 0 1 and so on in the cells. The SEQUENCE function generates a sequence of numbers from 1 to 50, and the MOD function checks if each number in the sequence is even or odd. If the number is even, the formula returns 0, and if the number is odd, the formula returns 1.
=(DATE(2024,1,15) + CEILING(SEQUENCE(50,1,0,1)/2,1)*7) * (IF(MOD(SEQUENCE(50),2)=0,0,1))
To show dates in the format you want and not show zeros, set a custom date format in the cells: dd mmm;;
Thank you for your help. It is greatly appreciated!
Hi there, I'm attempting to generate a sequence where the number of rows is dependent on Column A, with only one Column, starting with 0, and increment step is 1.
Column B has an identifier that repeats for every like row. For example, in the case of multiple Contact records under one Company, the Company in this case would repeat in Column B, where each Contact Record would be unique in Column A.
The sequence I want to apply would start over from 0 once Column B no longer repeats. The output would be in Column C. Please see the example table below of my desired outcome.
Contact Company Sequence ID
5991 416 0
4565 416 1
1790 416 2
1761 416 3
1023 416 4
6924 417 0
4770 417 1
4640 417 2
1309 418 0
7773 418 1
2682 419 0
7898 419 1
8310 419 2
1209 419 3
1314 419 4
How do I achieve the output in Column C?
Hi! I think you can count the number of duplicates in column B using the COUNTIF function and subtract 1 from the result. Read more: How to count duplicates in Excel.
=COUNTIF($B$1:B1,B1)-1
I hope my advice will help you solve your task.
hello sir
i want to make sequence of digits which are with alphabets like ( PLR-101-1) how can i do this please help me out. and if the article is change like ( Thu-508--Set) then how can i make the center digit sequence ?
Hello! If you read the comments above, you will see many such examples. Combine text strings with a SEQUENCE formula using the concatenation operator (&). For example:
="Thu-"&SEQUENCE(10,1,508,1)&"--Set"
Hi.
I want to create a dynamic numbering for some kind of Database for Samples.
I got an Input Table where i write the basic informations in. Then i write the total Number of Objects.
Object1 4
Object2 3
Now i want to create a Dynamich numbering in a list where i have following collumns
Intern ID Position Date Sample Number Samples Name
after the 4th Sample for Object 1 it should start with the First Sample of Object 2
Hi! Please clarify your specific problem or provide additional information to understand what you need.
I have 3 columns of numbers data . The device count changes, The AMP and channel is always the same. I was trying to see if a =sequence function would work but not figuring it out if so.
AMP Channel Device Count
1 1 20
1 2 1
1 3 10
1 4 5
1 5 2
1 6 3
1 7 7
1 8 9
2 1
2 2
2 3
2 4
I need to create a column that looks like
1-1-1
1-1-2
to
1-1-20
1-2-1
1-3-1
to
1-3-10
ect
Any help would be great thanks.
Hi! There are many such examples in the comments above. Read it. Combine a sequence of values with text using the concatenation operator &. For example.
="1-1-"&SEQUENCE(20,1,1,1)
Thanks for that help, Is there a way to get it then to continue and the second number change after 20 entries, to 1-2-1? I worded it wrong maybe above.
Hi! I advised you to read the comments above. Too bad you didn't. To repeat each number of the sequence 20 times, use the formula:
=CEILING(SEQUENCE(80,1,1,1)/20,1)
To repeat the sequence several times, use the formula
TRUNC(MOD((SEQUENCE(80,1,1,1)-1)/1,20)+1)
Concatenate into a text string using & operator:
="1-"&CEILING(SEQUENCE(80,1,1,1)/20,1)&"-"&TRUNC(MOD((SEQUENCE(80,1,1,1)-1)/1,20)+1)
How to generate consecutive numbers starting from 43 to 60 and each number should repeat 5 times.
For example
column A
43
43
43
43
43
44
44
44
44
44
45
45
45
45
45
and
upto 60 ?
Hi! You can find many such formulas in the comments above:
=CEILING(SEQUENCE(50,1,1,1)/5,1)+42