*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.

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:

SEQUENCE(rows, [columns], [start], [step])

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.

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**:

SEQUENCE(*n*)

To place numbers in a **row**:

SEQUENCE(1, *n*)

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 3^{rd} argument:

`=SEQUENCE(5,3,100)`

To generate a list of numbers with a **specific increment step**, define the step in the 4^{th} argument, 10 in our case:

`=SEQUENCE(5,3,100,10)`

Translated into plain English, our complete formula reads as follows:

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. 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.

Though the basic SEQUENCE formula does not look very exciting, when combined with other functions, it takes on a whole new level of usefulness.

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)`

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:

TRANSPOSE(SEQUENCE(*columns*, *rows*, start, step))

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)`

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.

As you probably know, in Excel 365 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))`

Like any other dynamic array function, SEQUENCE is only available in the latest builds of Excel 365 that support dynamic arrays. Therefore, you won't find any of those functions in pre-dynamic Excel such as Excel 2019, Excel 2016, and lower.

That's how to create sequence in Excel 365 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!

Excel SEQUENCE formula examples (.xlsx file)

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips and How-to

## 44 responses to "Excel SEQUENCE function - create a number series automatically"

hello,

help solve this, how many times 1 &2 appear in a column of 2,3,4 upto 20?

sample1

1 1 2 2

1 2 1 2

sample2

1 1 1 1 2 2 2 2

1 1 2 2 1 1 2 2

1 2 1 2 1 2 1 2

sample3

1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2

1 1 1 1 2 2 2 2 1 1 1 1 2 2 2 2

1 1 2 2 1 1 2 2 1 1 2 2 1 1 2 2

1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2

how can i get formula to know how many times a number can appear in a column before being exhausted or repeated.

thanks

Hello!

If I understand your task correctly, here is the article that may be helpful to you - COUNTIF in Excel.

If this is not what you wanted, please describe your task in more detail. Please specify what you were trying to find. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you

Hello

Sir I want generate the series of mobile phone numbers like +92301230000 to +92301239999 I don't know how I can.

Hi, use below:

=SEQUENCE(9999,1,92301230000,1)

IT NOT WORK

Interesting piece! I am creating a spreadsheet for my wife who is a potter. All pieces, let's say 'started', are listed in the spreadsheet starting 1 and obviously going down the sheet 2,3,4 etc. However, owing to the nature of her activity, not all pieces get to the point of being saleable items - thus, the final product codes for the saleable items are also consecutive, but there might be gaps between rows, where certain pieces are rejected i.e. product code 0001,0002, gap, gap, gap, 0003 etc. Is it possible, perhaps using a second column next to the Product Code column to use something like an IFISBLANK to work with SEQUENCE, so that, for example a Y (for yes) can be added to the first column, which then lets SEQUENCE add in the next column, the next sequential product code number - if that makes sense ?

Hello!

If you want to create automatic numbering in a column, write 1 in cell C1, and the formula in cell C2:

=MAX($C$1:$C1)+1

After that you can copy this formula down along the column.

You can make some of the cells in column C blank. The numbering will continue from the next nonblank cell.

I hope I answered your question. If something is still unclear, please feel free to ask.

Fantastic - much appreciated - exactly what I needed!

I want a generate a sequence of numbers in order from 1 to 25. But have them starting again from 1 after 25. How would I modify this formula to satisfy this.

I have the same , I need to have a sequence number generated automatically and continuance from page one to the next page ... I need to use this sequence as an order number so every time I place an order I have to get an automatic number ... is it possible ?

thank you & appreciate

Hello!

For automatic line numbering from 1 to 25, use the formula

=TRUNC(MOD((ROW(A1)-1),25)+1)

After that you can copy this formula down along the column.

so if I need to make my number start from 10001 :

how it should look like

Hi thank you so much for your informative guidance.

I have a question. How would the sequence function work in case I wanted to repeat the same number twice?

e.g.

2175

2175

2176

2176

2177

2177

2178

2178

etc.

Thanks appreciate your help!

Hello!

Use the following formula/the formula below to solve your task:

=CEILING(ROW(A4349)/2,1)

After that you can copy this formula down along the column.

Hello

Sir I want generate the series of mobile phone numbers like 2301230000 to 2301239999 I don't know how I can?

Hello Farhan,

You can use a formula like this:

=SEQUENCE(9999,1,2301230000)

Where 9999 is how many numbers you want to generate.

I am wondering if you can make a sequence where the number changing is not the last character? I want it to be like this, with the stars included? I need it to go from 1-99. Thanks for the help!!

*R1-01-01-01*

*R1-01-01-02*

*R1-01-01-03*

*R1-01-01-04*

*R1-01-01-05*

Hi!

If I got you right, the formula below will help you with your task:

="*R1-01-01-"&TEXT(SEQUENCE(99,1,1,1),"00")&"*"

Hope this is what you need.

I'm wondering if anyone could help me.

I am trying to generate some 1-column sequences like this:

1001-01

1001-02

1001-03

1002-01

1002-02

1002-03

1003-01

1003-02

1003-03

1004-01

(etc.)

Hello!

If I understand your task correctly, the following formula should work for you:

=CEILING(ROW(A3001)/3,1)&" - 0"&TRUNC(MOD((ROW(A1)-1)/1,3)+1)

I hope my advice will help you solve your task.

this works great but how do I use a dynamic function with the solution?

I have 2 numbers set by the user: a1 = 15 and b1 = 3

from these two inputs I would like to dynamically create a sequence that goes:

1.1

1.2

1.3

2.1

2.2

.... until 15.3

is this possible?

after playing around with John's "puzzle" below I came up with this to solve my own issue:

=INT(MOD(SEQUENCE($A$1*$B$1,1,0,1)/$B$1,$A$1)) + 1 & "." & MOD(SEQUENCE($A$1*$B$1,1,0,1),$B$1) + 1

somehow doing it with letters from the AAA0001 loop helped me figure out my own loop better.

Hi!

Replace A3001 with A1 in the formula above.

Hi,

I'm trying to generate a sequence of numbers in the below order. Could you please help?

40000 / 01

40002 / 03

40004 / 05

40006 / 07

40008 / 09

...

...

...

etc

Hello!

The formula below to solve your task:

=40000+(ROW(A1)-1)*2&" / "&TEXT(ROW(A1)*2-1,"00")

Hi Alex,

Highly appreciate your quick response. Excel shows that 'there's a problem with this formula'.

Thanks again!!!

Thomas

i want to make a series of 3 digit 'n' numbers

please help

Hi,

You can get 3-digit numbers with this formula:

=SEQUENCE(900,1,100,1)

If this is not what you wanted, please describe the problem in more detail.

Hi, I am looking to take data from one column and create a sequential number in another column. However the data in my reference column (location codes) are duplicated in more than one row but I need my sequential number column to be the same value for each location code. Example: My first entry is Location code K051 that has three rows of data so it's sequential number should be 1 for each of the three rows. Next location code is K052 that has five rows of data so it's sequential number column should be 2 for each of it's 5 rows.

Each location code could have a varying amount of rows associated.

Is there a formula that can create these sequential numbers? Thank you!

Hello!

If the Location code is written in column A, the order number of each code can be obtained using the formula

=MATCH(A2,UNIQUE($A$2:$A$20),1)

After that you can copy this formula down along the column.

How can i make sequence of number series with one column or row blank.

1 2 3 4 5 6

- - - - - -

7 8 9 10 11

- - - - - -

Hi,

You can use the formula

=SEQUENCE(1,10,1,1)

Hi, thank you, Sir Alexander, I'm working on making a sequence of Admission number like

1910404001

19104040002

1910404003

,,,,

,,,,,

,,,

Please help me, thank you?

Hi,

The constant 1910404 can be combined with a changing value using the & operator. To always have 3 digits in a number, use the TEXT function.

I’ll try to guess and offer you the following formula:

=1910404&TEXT(SEQUENCE(100,1,1,1),"000")

Hello,

I have a sequence of numbers, let's say:

1

3

4

20

22

and would like Excel to return on a cell(another sheet) the next available number in the given column, starting in 1 to n, if this makes sense.

Is this possible?

Thanks

i have a sequence of number

AAA0001 --- AAA9999 , after the 9999 the next number i need is AAB0001 and continue increasement .

please help , and the alphabet increase is without "I" and "o" .

thanks !

Hi,

I'm really sorry, we cannot help you with this task. Your task cannot be accomplished with Excel formulas.

I'm spending way too much time on this but who doesn't like a good excel challenge.

after playing with the start value I was able to make it a bit more elegant

this solves the no i and o in the letter sequence and skips from 9999 to 0001

also you can change the number series from 9999 to 999 in P2

you can drag the top cell across to continue the series in the next column as this series exceeds the max rows allowed in a column.

=CHAR(65 + MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24^2*$P$2),24) + IFS(MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24^2*$P$2),24)>12,2,MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24^2*$P$2),24)>7,1,TRUE,0)) & CHAR(65 + MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24*$P$2),24) + IFS(MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24*$P$2),24)>12,2,MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24*$P$2),24)>7,1,TRUE,0)) & CHAR(65 + MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/($P$2),24) + IFS(MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/($P$2),24)>12,2,MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/($P$2),24)>7,1,TRUE,0)) & RIGHT("00000" & MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1),($P$2))+1,LEN($P$2))

ok I have refined my previous answer a little

set $C$2 to either 999 or 9999 depending on the number series you want

then use this formula to get the series you asked for.

No letters i or o and skip from 9999 to 0001

This was a fun puzzle!!!

=IF(SEQUENCE(1048576)>$C$2*24^2,"B","A") & CHAR(65 + INT(MOD(SEQUENCE(1048576)/(24*$C$2)- 0.000001,24)) + IFS(INT(MOD(SEQUENCE(1048576)/(24*$C$2)- 0.000001,24))>12,2,INT(MOD(SEQUENCE(1048576)/(24*$C$2)- 0.000001,24))>7,1,TRUE,0)) & CHAR(65 + INT(MOD(SEQUENCE(1048576)/($C$2)- 0.000001,24)) + IFS(INT(MOD(SEQUENCE(1048576)/($C$2)- 0.000001,24))>12,2,INT(MOD(SEQUENCE(1048576)/($C$2)-0.000001,24))>7,1,TRUE,0)) & RIGHT("00000" & ROUNDUP(MOD(SEQUENCE(1048576),($C$2)+0.000001),0),LEN(C2))

for even more fun ;o)

you can use this formula and drag from a1 to k1 or more

This will continue the series since column limit in excel doesn't go far enough for your series

in this case the number series 999 or 9999 is in $M$2

=CHAR(65 + INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24^2*$M$2)- 0.0000001,24)) + IFS(INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24^2*$M$2)- 0.0000001,24))>12,2,INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24^2*$M$2)- 0.0000001,24))>7,1,TRUE,0)) & CHAR(65 + INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24*$M$2)- 0.0000001,24)) + IFS(INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24*$M$2)- 0.000001,24))>12,2,INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24*$M$2)- 0.000001,24))>7,1,TRUE,0)) & CHAR(65 + INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/($M$2)- 0.000001,24)) + IFS(INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/($M$2)- 0.000001,24))>12,2,INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/($M$2)-0.000001,24))>7,1,TRUE,0)) & RIGHT("00000" & ROUNDUP(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576)),($M$2)+0.000001),0),LEN($M$2))

I have a sequence of numbers, let's say:

1

3

4

20

22

and would like Excel to return on a cell(another sheet) the next available number in the given column, starting in 1 to n, if this makes sense.

Is this possible?

Thanks

Hello!

Write down your numbers starting at cell A2. Write this formula in cell B2

=MIN(FILTER(SEQUENCE(100,1,1,1),(SEQUENCE(100,1,1,1)>A2)*(SEQUENCE(100,1,1,1)<>A3)*(SEQUENCE(100,1,1,1)<>B1)))

and then copy it down along the column.

You can learn more about FILTER function in Excel in this article on our blog.

Hope this is what you need.

DEAR SIR

I want to make sequence in different cells i mean selected cells .

make select cells and put sequence in selected cell .

not upcoming cell.

Hi!

An Excel formula can only work over a continuous range. It is not possible to select individual cells.