SEQUENCE Function

The SEQUENCE function generates a list of sequential numbers in an array. The array can be one-dimensional, or two-dimensional, controlled by rows and columns arguments. SEQUENCE can be used on its own to create an array of sequential numbers that spill directly on the worksheet. It can also be used to generate a numeric array inside another formula, a requirement that comes up frequently in more advanced formulas.

The SEQUENCE function takes four arguments: rows, columns, start, and step. All values default to 1. The rows and columns arguments control the number of rows and columns that should be generated in the output. For example, the formulas below generate numbers between 1 and 5 in rows and columns:

=SEQUENCE(5,1) // returns {1;2;3;4;5} in 5 rows
=SEQUENCE(1,5) // returns {1,2,3,4,5} in 5 columns

Note the output from SEQUENCE is an array of values that will spill into adjacent cells. The syntax for SEQUENCE indicates that rows is required, but either rows or columns can be omitted:

=SEQUENCE(5) // returns {1;2;3;4;5} in 5 rows
=SEQUENCE(,5) // returns {1,2,3,4,5} in 5 columns

The start argument is the starting point in the numeric sequence, and step controls the increment between each value. Both formulas below use a start value of 10 and a step value of 5:

=SEQUENCE(3,1,10,5) // returns {10;15;20} in 3 rows
=SEQUENCE(1,3,10,5) // returns {10,15,20} in 3 columns

Examples

In the example in the screen above, the formula in B4 is:

=SEQUENCE(10,5,0,3)

With this configuration, SEQUENCE returns an array of sequential numbers, 10 rows by 5 columns, starting at zero and incremented by 3. The result is 50 numbers starting at 0 and ending at 147, as shown in the screen.

Positive and negative

SEQUENCE can work with both positive and negative values. To count from -10 to zero in increments of 2 in rows, set rows to 6, columns to 1, start to -10, and step to 2:

=SEQUENCE(6,1,-10,2) // returns {-10;-8;-6;-4;-2;0}

To count down between 10 and zero:

=SEQUENCE(11,1,10,-1) // returns {10;9;8;7;6;5;4;3;2;1;0}

Sequence of dates

Because Excel dates are serial numbers, you can easily use SEQUENCE to generate sequential dates. For example, to generate a list of 10 days starting today in columns, you can use SEQUENCE with the TODAY function.

=SEQUENCE(1,10,TODAY(),1)

More details here. To generate a list of 12 dates corresponding to the first day of the month for all months in a year (2022 in this case) you can use SEQUENCE with the DATE and EDATE functions:

=EDATE(DATE(2022,1,1),SEQUENCE(12,1,0))

To generate a list of twelve-month names (instead of dates) you can wrap the formulas above in the TEXT function like this:

=TEXT(EDATE(DATE(2022,1,1),SEQUENCE(12,1,0)),"mmmm")

More information about these formulas here.