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

431 comments

  1. Hi, Could you help with the below:

    We need to have a cell to enter the number, example 10. So, here 10 refers to the number of classes and we should get the same number of rows/columns to be added automatically to input the date & time for each of the class.

    If I input 5 in the target cell. Then it should create 5 rows/columns for each date & time. The date & time should be in sequence.

    Many thanks in advance

  2. Is it possible to get number as below-

    20220801
    20220901
    20221001
    20221101
    20221201
    20230101

  3. I have a date usage sheet for our inventory where someone inputs the start date, how many days used, then the calculated end date. I have given the user 5 dates to set the start and days used. Is it possible to have the sequence function do all these with one function entry? Is it possible to have multiple starts and stops?

    EX:
    Start Date Days Used End Date
    4/1/2023 21 4/22/2023
    5/1/2023 4 5/5/2023
    5/10/2023 8 5/18/2023
    6/1/2023 10 6/11/2023
    6/15/2023 3 6/18/2023

    Sequence would go 4/1/2023 then fill the dates in until 4/22/2023, 5/1/2023......5/5/2023,5/10/2023.....5/18/2023 and so on.

      • I think this might work, if I understand what you're after:

        =SEQUENCE(1, + 1,,1)

        You could also calculate the by calculating the number of days between and to make it more dynamic.

        • Oops...used the wrong formatting in the formula.

          =SEQUENCE(1,[Days Used] + 1, [Start Date],1)

          Hopefully this shows up correctly

  4. HI,
    I have a set of data in one column, say Company Name in A1, PO No-A2,MRN Numbers in A3,A4,A5 etc. How do i bring these datas in different coulmn in sucha a way that All the company name in 1 coulmn, related POs in next column, MRNs in next column. I cannot use transpose since it will arrange all the data horizontally, not in a table manner.

    Thank you

    • Hi! I don't really understand why you can't use transpose. To help you, give an example of the source data and the desired result.

  5. Hi!

    Tried figuring out through the well done article, but didn't manage to find my way.
    I'm looking for the following to be implemented in a booking sheet I'm working on where certain conditions apply;

    Once data has been entered in a specific cell, it generates a random number in another cell which will function as a reference to that booking.
    The number generated must partly consist of specific values from two given cells, as in the following logic: "data cell 1" - "random number generated" - "data cell 2" .
    The number generated is preferably in sequence which can not be duplicated, ie must respect what is generated in other cells (same column).

    So in other words;

    Once data has been entered in certain cells (column A) then the corresponding cell (column B) generates a random number with the above conditions.

    Hope I was able to convey this understandably enough, and thanks in advance for any help!

  6. How do I sequence this?
    Is there a formula?
    Ex. Wk13 up to WK50
    I want to sequence it and stop doing it manually

    • Create a sequence of numbers using the SEQUENCE function and combine it with a text string.
      Try this formula:

      ="WK"&SEQUENCE(38,1,13,1)

  7. Hi!
    I have a set of project codes (example format is 90-00480-90GT001-ProjectName 1) in column D. The 90-00480 prefix is entered manually in column A, as is the project name suffix in column C.

    In column B, the 90GT001 part is a sequential number, and I need to ensure that only the next available number in the sequence is used, when a new code needs to be generated. I've tried using the SEQUENCE function in column B and formatted the column to include leading zeros, then concatenated the prefix and suffix using the formula =A2&"-"&"90GT"&B2&"-"&C2, however this results in 90-00480-IT-90GT1-ProjectName 1 (the leading zeros do not pull through so the code format is incorrect).

    How can I fix this?

    Thanks

  8. 1st row: 2000 0 0 2000 0 0 and so on over a month period. How to do it with a click?

    Next step:
    If i have multiple rows.. each has a different sequence of numbers.. how to fill a sheet over a month period (30 columns)?

  9. Hi, how do I create a sequence based on values from another cell.
    Example: Column "A" has values such as General Admin, Finance, Marketing, etc and I would like column B to generate a sequence such as below please.

    Department Code
    Finance FIN-01
    General Admin GA-01
    Marketing MKT-01
    General Admin GA-02
    General Admin GA-03
    Marketing MKT-02
    Marketing MKT-03
    Marketing MKT-04
    Finance FIN-02

  10. Hi, how to generate a sequence of say:
    1,1,1 (3 times)
    2,2,2 (3 times)
    3,3,3 (3 times) e.t.c all in one column?

    Meaning

    1
    1
    1 (3 times)

    2
    2
    2 (3 times)

    3
    3
    3 (3 times) e.t.c

    all in one column?

      • Thank you so much Sir for your response.

        It works absolutely, your answer really helped, thanks 👍

  11. It's possible create a sequences like this 10,-10,20,-20,30,-30 ..... 100,-100 and start again 10,-10,20,-20 ???

    • Given A1 contains the number of terms you want:

      =2.5*((-1)^(MOD(SEQUENCE(A1)-1,20)+1))*((-2)*(MOD(SEQUENCE(A1)-1,20)+1)+(-1)^(MOD(SEQUENCE(A1)-1,20)+1)-1)

    • by the way, I determined this by:

      1. Copying the series of terms into Wolfram Alpha to identify an equation to produce the series --> a(n) = 5/2(-1)^n(-2n+(-1)^n-1)
      2. Replicating the formula in Excel, using SEQUENCE(20) instead of x --> =2.5*((-1)^SEQUENCE(20))*((-2)*SEQUENCE(20)+(-1)^SEQUENCE(20)-1)
      3. Modifying the formula to create a repeating series using MOD(ULO) function --> SEQUENCE(20) --> (MOD(SEQUENCE(A1)-1,20)+1)

      • Wow it works!!! thank you so much

    • Hi,

      I would like to generate a reference number in sequential order every time a new data is added in the next row. Example:

      In the A1: reference is “RED1”.
      If a new data is added in A2 it will automatically generate “RED2” as a reference number. And so on. Which will look like something like this:
      A1: RED1
      A2: RED2
      A3: RED3

      This will be in relation to other data in the table.

      Thanks

      • Hi!
        Based on your description, it is hard to completely understand your task.
        You can create a sequence of values with a formula like this.

        ="RED"&SEQUENCE(10,1,1)

        But if you add some value to A2, then you can only replace it with RED2 using VBA.

  12. Hi, how to make sequence lets say of : 0 21 times, 20 15 times, 40 10 times, 60 6 times, 80 3 times, and 100 1 time; all of it in one row?

    thanks in advance !!!

  13. Hi

    The below is a part of report, I need to add a numbers 1-7 in the end of the text for every new product description. Can you help please?

    15 Pouch Box (595 * 241 * 125 mm)-1
    15 Pouch Box (595 * 241 * 125 mm)-2
    15 Pouch Box (595 * 241 * 125 mm)-3
    15 Pouch Box (595 * 241 * 125 mm)-4
    15 Pouch Box (595 * 241 * 125 mm)-5
    15 Pouch Box (595 * 241 * 125 mm)-6
    15 Pouch Box (595 * 241 * 125 mm)-7
    4 Slice Clear Plastic Tray (156 Cut)-1
    4 Slice Clear Plastic Tray (156 Cut)-2
    4 Slice Clear Plastic Tray (156 Cut)-3
    4 Slice Clear Plastic Tray (156 Cut)-4
    4 Slice Clear Plastic Tray (156 Cut)-5
    4 Slice Clear Plastic Tray (156 Cut)-6
    4 Slice Clear Plastic Tray (156 Cut)-7
    7 Month Caramel - RSPO SG (M/ SO2)-1
    7 Month Caramel - RSPO SG (M/ SO2)-2
    7 Month Caramel - RSPO SG (M/ SO2)-3
    7 Month Caramel - RSPO SG (M/ SO2)-4
    7 Month Caramel - RSPO SG (M/ SO2)-5
    7 Month Caramel - RSPO SG (M/ SO2)-6
    7 Month Caramel - RSPO SG (M/ SO2)-7

  14. Hi!
    I want to generate a random sequence based on previous data.
    E.g., Column A - E has values from 1 to 45

    So if I have 100 rows of such a sequence, is there a formula that can predict/generate the next sequence?

  15. How to predict the series for
    100
    200
    300
    500
    600
    700
    900

  16. Hello,

    I need a formula that would return the sequence for this below:

    Part Number: Sequence: (Formula for this)
    47009 1
    47009 2
    47009 3
    47014 1
    47014 2

  17. How can I generate a 5-digit PID with a check digit on it let's say a batch of 100 PIDs?

    • Hi!
      We have a tool that can solve your task in a couple of clicks – Random Generator. It offers a variety of ways to get random values, which you can look out at this link.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      To use the formula, read the article above carefully.

  18. Hi
    How do I present such data in a sequence.
    1- 51
    51-101
    101-151

  19. if i have the minimum and maximum of different batches of serials in two columns, which function can i call to know if a number is between the two columns. For example, min: 1 and Max: 100 in two columns, how do i find out if 15 is contained in that range for different range and different number instead of having to open up the 1 to 100

  20. Hi, how do I create a sequence by 1 in a cell that depends on the max number of other cells (H) and if it is selected "NEW" (C) and also depends on the year (F)

    I have this formula: =MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1

    However, a year can have two max numbers which I need to take the sequence from; for instance, 110943000 and 110943700. I would still need the sequences on 110943001, 110943701, 110943002, 110943702, and so on. The formula is only giving me the sequences from 110943700 which is the maximum.

    C18 = NEW
    D18 = 110943000 (ID)
    F18 = IFERROR(VLOOKUP(D18,$D$3:$F$17,3,FALSE),"-") = 9 {this is how the year is calculated), there's no issue with this one}
    H18 = MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1 = 110943701 {instead of 110943001}

    it only works in this case:
    C18 = NEW
    D18 = 110943700 (ID)
    F18 = 9
    H18 = MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1 = 110943701

    Thank you!

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