Build Numbered Lists With The Amazing SEQUENCE Function

The SEQUENCE function is a useful function in Google Sheets. It’s a powerful way to generate numbered lists.

Previously, you had to resort to obscure array formulas like 

=ArrayFormula(row(1:5))

to get lists of numbers. Things got ugly fast if you wanted to customize these lists.

Thankfully today, we have the SEQUENCE function.

As arguments, you specify: 1) the number of rows, 2) the number of columns, 3) a start value, and 4) a step size.

Arguments 2, 3 and 4 are optional. However, if you want to set them you need to include the previous ones (e.g. if you want to set a step size in argument 4, then you need to set 1, 2 and 3 as well).

Keep this order in mind as you look through the examples below and you’ll soon understand how the function works.

1. Ascending list of numbers

=SEQUENCE(5)

=SEQUENCE(5)

2. Horizontal list of numbers

Set the row count to 1 and the column count to however many numbers you want e.g. 5:

=SEQUENCE(1,5)

=SEQUENCE(1,5)

3. Two-dimensional array of numbers

Set both row and number values:

=SEQUENCE(10,5)

=SEQUENCE(10,5)

4. Start from a specific value

Set the third argument to the value you want to start from e.g. 100:

=SEQUENCE(5,1,100)

=SEQUENCE(5,1,100)

5. Use a custom step

Set the fourth argument to the size of the step you want to use, e.g. 10:

=SEQUENCE(5,1,1,10)

=SEQUENCE(5,1,1,10)

6. Descending numbers

Set the fourth argument to -1 to count down:

=SEQUENCE(5,1,5,-1)

=SEQUENCE(5,1,5,-1)

7. Negative numbers

Set the start value to a negative number and/or count down with negative step:

=SEQUENCE(5,1,-1,-1)

=SEQUENCE(5,1,-1,-1)

8. Dates

Dates are stored as numbers in spreadsheets, so you can use them inside the SEQUENCE function. You need to format the column as dates:

=SEQUENCE(5,1,TODAY(),1)

=SEQUENCE(5,1,TODAY(),1)

9. Decimal numbers

Unfortunately you can’t set decimal counts directly inside the SEQUENCE function, so you have to combine with an Array Formula e.g.

=ArrayFormula( SEQUENCE(5,1,10,1) / 10 )

=ArrayFormula( SEQUENCE(5,1,10,1) / 10 )

10. Constant numbers

You’re free to set the step value to 0 if you want an array of constant numbers:

=SEQUENCE(5,1,1,0)

11. Monthly sequences

Start with this formula in cell A1, which gives the numbers 1 to 12 in a column:

=SEQUENCE(12)

In the adjacent column, use this DATE function to create the first day of each month (formula needs to be copied down all 12 rows):

=DATE(2021,A1,1)

This can be turned into an Array Formula in the adjacent column, so that a single formula, in cell C1, outputs all 12 dates:

=ArrayFormula(DATE(2021,A1:A12,1))

Finally, the original SEQUENCE formula can be nested in place of the range reference, using this formula in cell D1:

=ArrayFormula(DATE(2021,SEQUENCE(12),1))

This single formula gives the output:

1/1/2021
2/1/2021
.
.
.
12/1/2021

It’s an elegant way to create a monthly list. It’s not dependent on any other input cells either (columns A, B, C are working columns in this example).

With this formula, you can easily change all the dates, e.g. to 2022.

Building in steps like this a great example of the Onion Method, which I advocate for complex formulas.

12. Text and Emoji sequences

You can use a clever trick to set the SEQUENCE output to a blank string using the TEXT function. Then you can append on a text value or an emoji or whatever string you want to create a text list.

For example, this repeats the name “Ben Collins” one hundred times in a column:

=ArrayFormula(TEXT(SEQUENCE(100,1,1,1),"")&"Ben Collins")

And, by using the CHAR function, you can also make emoji lists. For example, here’s a 10 by 10 grid of tacos:

=ArrayFormula(TEXT(SEQUENCE(10,10,1,1),"")&CHAR(127790))

Repeating List with SEQUENCE function

Have you got any examples of using the SEQUENCE function?

10 thoughts on “Build Numbered Lists With The Amazing SEQUENCE Function”

  1. Hi Ben!

    Always loved your way of creativity. You see all possibilities in a single function.

    Please see the first 50 Fibonacci numbers

    =index(((1+5^(1/2))^SEQUENCE(50)-(1-5^(1/2))^SEQUENCE(50))/(2^SEQUENCE(50)*5^(1/2)))

  2. 1. Wrap the SEQUENCE in an ARRAYFORMULA(ROUNDUP & a divide operation to make multiples of each number in the list, e.g. =ARRAYFORUMLA(ROUNDUP(SEQUENCE(9,1,1)/3)) generates a column vector {1;1;1;2;2;2;3;3;3}

    2. Feed a SEQUENCE starting at 65 into an ARRAYFORMULA(CHAR to create a sequence of letters (because CHAR(65)=”A”), e.g. =ARRAYFORMULA(CHAR(SEQUENCE(1,9,65))) generates a row vector {A,B,C,D,E,F,G,H,I}

    3. Concatenate row & column vectors generated with SEQUENCE using ‘&’ within the ARRAYFORUMLA to generate grids containing all the possible combinations (e.g. concatenating the above two examples generates an 9×9 grid starting at 1A & ending at 3I)

    1. Hope I’m not treading on anyone’s toes here, but I’d approach it like this (e.g. for 10 loops): =ARRAYFORMULA(FLATTEN({1,2,3,5}&LEFT(A1:A10,0)))

      N.B. Using 0 as the second argument of LEFT returns an empty string; within an ARRAYFORMULA empty arrays can be made this way. The column of cells referred to in the LEFT can be anywhere on the sheet; it is the number of cells in the column range that controls the number of loops.

  3. Thanks, Ben! Especially for monthly sequences.
    Is there any way to upgrade the formula when I want every month for example three times?
    I mean
    1/7/2021
    1/7/2021
    1/7/2021
    1/8/2021
    1/8/2021
    1/8/2021
    and so on till the end of the next year.
    Thank you.
    Milan

  4. You could do it like this with SEQUENCE combined with QUERY:

    `=ArrayFormula( QUERY(
    MOD( SEQUENCE(50) ,6),
    “Select Col1 Where Col1 != 0 AND Col1 != 4 “,0 ) )`

  5. HI BEN,

    How can we make a list that starts with a constant, the output required :

    AB-1
    AB-2
    AB-3
    ….

    I tried =CONCATENATE(“AB-“,SEQUENCE(10,1,1,1))
    got AB-12345678910

    the only solution for me now is to make a column with sequence
    and then concatenate to another column.

    can you have a solution to make it in one step

    =SEQUENCE(10,1,1,1) | =CONCATENATE(“AB-“,T12)
    1 | AB-1
    2 |AB-2
    3…..

Leave a Reply

Your email address will not be published. Required fields are marked *