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 that the output from SEQUENCE is an array of values that will spill into adjacent cells. The formula below will create a 5 x 5 array that contains 25 cells with the values 1-25:

=SEQUENCE(5,5) // numbers 1-25 in a 5 x 5 array

The syntax for SEQUENCE indicates that rows is required and columns is optional. However, either 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.

SEQUENCE with text

SEQUENCE generates numeric arrays. However, it is possible to use SEQUENCE to create arrays that contain text. For example, the formula below will generate a 5 x 5 array filled with "x":

=REPT("x",SEQUENCE(5,5,1,0)) // 5 x 5 array of x's

SEQUENCE is configured ot return a 5 x 5 array of 1's, which are fed into the REPT function with "x" as the text to repeat. By replacing "x" with an empty string, we can generate an empty 5 x 5 array:

=REPT("",SEQUENCE(5,5,1,0)) // empty 5 x 5 array

The formula below will generate an array that contains the 26 letters from A-Z:

=CHAR(SEQUENCE(26,,65)) // generate A-Z

The SEQUENCE function returns 26 numbers starting with 65. The CHAR function returns a specific character based on its ASCII number. The letter "A" is 65, and "Z" is 90, so the result is all 26 uppercase letters.

You can also use the MAKEARRAY function to generate an array that contains the result of a custom calculation.