Excel SEQUENCE function - create a number series automatically

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:

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.

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:

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.
Creating a 1 column sequence with a formula

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)
Formula to generate a horizontal sequence

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)
Using the SEQUENCE function to fill a range of cells

To start with a specific number, say 100, supply that number in the 3rd argument:

=SEQUENCE(5,3,100)
A sequence starting at a specific number

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)
A sequence incrementing by a specific step

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

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)
Formula to Make a decreasing sequence in Excel

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:

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)
A formula to generate a two-dimensional sequence that moves vertically

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.
Creating a sequence of Roman numbers

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.
Formula to generate a series of increasing random integers

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))
Formula to create a series of decreasing random integers

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)

Table of contents

437 comments

  1. Hi,
    Thanks a lot for this.
    I would like to go in the sequence 001,002,003 etc down a column
    using the sequence function I am getting 001,011,021,031 etc.
    Would appreciate it if you could let me know how to correct this.

    Thanks.

  2. Hi!
    I would like to make a pattern going down a column:

    1
    4
    2
    4
    2
    4
    2
    4
    1

    The first and last value must always be 1, and in-between needs to be a pattern of 4,2,4,2,4... The pattern needs to be autofilled in when I add a value in the column next to it on the same row (it needs to be applied to blank cells so if I add or delete values the pattern remains with 1 at the start and end). I can't figure out what to use so that the first and value stay at 1. Much appreciated if possible to help!

  3. Hello,

    I have a very large table consisting of 5-digit ID codes (approximately 8,000 rows). For each ID code, I need to concatenate or append a separate table consisting of 4-digit product codes (3,000 rows). The final list would be in the neighbourhood of 24m rows (8k x 3k). Is this something I can do in Excel or perhaps Power Query and how?

    tbl_IDCode tbl_ProdCode
    20456 1111
    20457 2333
    20468 2654
    20471

    End Result
    204561111
    204562333
    204562654
    204571111
    204572333
    204572654
    204681111
    204682333
    204682654
    204711111
    204712333
    204712654

    Thank you,
    Dan

  4. Hi I have a question where I need to create a column of index numbers
    beginning at 20 and incrementing by 1 so that the last number in the column is 44 how do I cap it at 44.

  5. hi

    is it possible to sequence
    2222/22
    2223/22
    2224/22 etc

    thanks

  6. 001/22-23
    002/22-23

    HOW TO CONTINUE THIS SEQUENCE ?

  7. Hi Alexander, I am hoping you can help with a formula I have been struggling to find. I need to list all values between two original values. For example, I have the following 2 values: ABC2100701 & ABC2101800. I need a formula to list the entire range between these 2. Is that possible at all?

  8. Hello,

    I want to type something in sequence, like below.

    A0101
    A0104
    A0201
    A0204
    A0301
    A0304
    etc.

    Up to
    A7001
    A7004

    Is there a formula doing that typing quickly?

    • Hello!
      The formula below will do the trick for you:

      ="A"&TEXT(CEILING(ROW(A1)/2,1),"00") &"0"& (TRUNC(MOD((ROW(L1)-1)/1,2)+1))^2

      Copy this formula down along the column.

  9. Hi - I have a slightly random one. For livestock animals such as cattle, they have passport numbers that go up in a specific sequence, for example:

    UK121629 101653
    UK121629 201654
    UK121629 301655
    UK121629 401656
    UK121629 501657
    UK121629 601658
    UK121629 701659
    UK121629 101660
    UK121629 201661
    UK121629 301662
    UK121629 401663
    UK121629 501664
    UK121629 601665
    UK121629 701666

    There's UK for the country, then the herd number of the farmer (which usually stays the same) and then the last 6 digits are the individual animal's tag number. This goes up by one each time for the last 4 digits, but the first two numbers go up by 10 each time, until they reach 70, then it goes back to 10,20,30 and so on.

    Is there any way I can make this into a sequence?
    Thank you!

  10. Hi, what is the formula for this kind of sequence?
    20220327.01.01
    20220327.02.01
    20220327.03.01
    20220327.04.01
    20220327.05.01

    I already tried the formula you shared in this article, but I couldn't figure it out. Any help would be appreciated. Thank you!

  11. Hello,

    I need to create the following sequence

    5-00001-01
    5-00002-01
    5-00003-01
    5-00004-01

    etc

    Any advice?

    • Hello!
      Use the TEXT function to apply a custom number format. All the information you need about the SEQUENCE function is in the article above.

      ="5-"&TEXT(SEQUENCE(100,1,1,1),"00000")&"-01"

      There are many examples in the comments.

  12. I am looking to create the ongoing sequence below and could use help with the formula. As I have to go very high with the numbers, I really don't want to be entering them all manually. Thanks!

    2022.1.1
    2022.2.1
    2022.3.1
    2022.4.1
    2022.5.1
    2022.6.1
    2022.7.1

  13. Hi, i want a column A to generate a sequential number of p/o's to an unlimited numer of rows. can you post an expample?

  14. Hello
    I hope you can help me out.
    I am new to excel and am probably over my head.
    I have a sheet with 8 columns in this order (last name, first name, hire date, shift, position, hours, switch and rank) I have it set up so that the data auto sorts by position first, then shift, hours, hire date and last name and when a new person is added to a master sheet they are automatically added to this sheet and placed according to the sort listed above. What i am desperately needing help on is how to auto number my rank column so that they are ranked dynamically as per multiple criteria of (shift, position, hours, hire date and alphabetically by last name. There are 4 different positions, 4 different shifts. I would like the numbering to be between shift and position and then hours but If people are tied in hours then number by hire date but if tied with that as well then number by first letter of last name alphabetically. If this is too much to ask I understand and we can get by with numbering by hand after printing. here is a mock up of what I am trying to accomplish. Ps. switch column is nothing important

    last first hire shift position hours switch rank

    smith bill 2-13-21 A hauler 19 1
    davis fred 2-22-21 A hauler 22 2
    hill mike 2-22-21 A hauler 22 3
    kreg mary 2-12-19 A lifter 23 1
    hall kate 5-30-18 A lifter 25 2
    gore ned 3-09-19 A sorter 14 1
    toews greg 3-12-17 A sorter 28 2
    lao ning 4-12-20 A tech 22 1
    hicks june 5-23-20 A tech 22 2
    hash brian 7-04-19 B hauler 17 1
    bush kim 4-09-22 B hauler 22 2
    and continued on with other classes in shift b, c and d.
    As you can see by the chart when fred davis and mike hill tie for hours and hire date they are then ranked by first initial of last name and when ning lao and june hicks tie for hours the ranking is next based on hire date and doesnt have to go to last name. everyone else is ranked by low hours first in their own shift and position. Hopefully I have made this readable. I did discover this formula but it only works with ranking shift, hours and position. =COUNTIFS($G$13:$G$150,"<"&G13,$F$13:$F$150,F13)+1
    (g is my hours column, f is my position column and e is my shift column)
    Thanks again for any help you have on this.

    Sincerely, Gordon Stuart

      • So could I somehow join the rank function with the formula I have above?

        • Hi!
          If your table uses automatic sorting by columns D, E, F, G, then the rank will be equal to the serial number in the table. H13+1 and so on.

  15. Hello,

    I want to to do grouping of numbers based on sequence, e.g,

    567, 568, 569, 570, 571 - sequence 5
    230,231,232,233,234,235 - sequence 6
    723, 724, 725, 726 - sequence 4
    123,124,125,126 - sequence 4

    Sequence should be calculated on the basis of count

  16. 1 9 17 25
    2 10 18 26
    3 11 19 27
    4 12 20 28
    5 13 21 29
    6 14 22 30
    7 15 23 31
    8 16 24 32
    33 41 49 57
    34 42 50 58
    35 43 51 59
    36 44 52 60
    37 45 53 61
    38 46 54 62
    39 47 55 63
    40 48 56 64

    -- how to create this sequence.. please help me ?

  17. I am trying to create a spreadsheet to help me keep track of our pay periods and check date.

    Imagine this is a spread sheet..lol:

    Header: Week Period Begins Period Ends Check Date

    1 12/19/21 01/01/21 01/08/21
    2 01/02/21 01/15/21 01/22/21
    3 01/16/21 01/29/21 02/05/21
    4 01/30/21 02/12/21 02/19/21

    I made this with my phone side ways.

    I am a nube when it comes to formulas, but I try.

    Can you help me? Ty!!

    Todd Hurley

      • A simple spreadsheet to keep track of bi-weekly pay periods every fiscal year. So at the beginning of every new year I enter the starting dates for period begins, period ends, and Check Date. The cells below each start date automatically configured with a formula for the remaining pay periods for the remaining year. If that makes sense.

        • Also each pay period header increase by 2 weeks/14 days.

          • Hi!
            If I understand you correctly, write down the date 19.12.21 in cell B1. Try to enter the following formula in cell B2 and then copy it down along the column:

            =B1+14

            Do the same with columns C and D.

            • It worked!

              Thank you very much for your help!! Much appreciated.

              • Hi!?

                I created a Payroll Spread calculation hours per day x pay per hour to get total gross weekly pay.

                I did:

                Row M is the daily total gross pay:

                M3 = $139.88 + M4= $133.13

                =SUM(M3:M4) which should equal

                $273.01

                How ever the cell shows: $273.00

                I made certain to format the cell to currency with 2 decimal places but the .01 is missing. I am a newbie to excel or OpenOffice.

                What am I missing? Thank you for your time.?

                Todd

              • Hello!
                If your numbers are derived from calculations, then in cells M3 and M4 they are displayed with rounding to two decimal places. However, the number is not rounded. For example, 139.884356 is displayed as 139.88
                When adding such numbers, the result is different from what you see in the cells.
                To avoid this, use round numbers to 2 decimal places in your formulas.

  18. Hi there

    Is there a way to combine SEQUENCE and MAX functions? I would like to use SEQUENCE to have a series of descending numbers but don't want the negative numbers in the sequence (i.e. once the descending SEQUENCE goes below zero) as it affects my other SUM functions. I tried combining SEQUENCE and MAX functions in an attempt to remedy this but wasn't having much luck combining the two!

    I am trying to examine the effect of different reduction rates vs time, attempt was:

    =MAX(0,(SEQUENCE(24,1,B1-C1))

    Where, B1 = Starting Number C1= Reduction Rate

    Thanks in advance

      • e.g.

        Starting Point = x = 20
        Time = 24 months
        Reduction Rate = (x-y), where y ranges between y = 2.8 per month
        Would like SEQUENCE to decrease from 20 until it reaches zero and then stop, not continue until the sequence covers the full 24 months.

        • Hello!
          If I understand your task correctly, you can create a sequence using the IF function.

          A1 - 20
          A2 - =IF(A1-2.8>0,A1-2.8,0)

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

  19. Hi there,

    I am wondering if there is a way to create a code based on an order number and quantity

    Order # Quantity

    324954 1
    434928 3
    224860 4
    724782 2

    Would like the codes to look the same as below:

    324954 - 01

    434928 - 01 | 434928 - 02 | 434928 - 03 |

    224860 - 01 | 224860 - 02 | 224860 - 03 | 224860 - 04 |

    724782 - 01 | 724782 - 02 |

    Not sure if this is possible.

  20. A pot can hold 1000 coins. I would like to display this as a primary key. For example,

    P1C1
    P1C2
    P1C3
    ...
    P1C999
    P1C1000
    P2C1
    P2C2
    ...
    P2C998
    P2C999
    P2C1000
    P3C1
    P3C2
    P3C4

    The list should have no bottom limit, allowing as many entries as possible.

    What is the best way to do this?
    Any help would be greatly appreciated!

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

      ="P"&CEILING(SEQUENCE(4500,1,1,1)/1000,1)& "C" &TRUNC(MOD((SEQUENCE(4500,1,1,1)-1)/1,1000)+1)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)