The SEQUENCE function generates a list of sequential numbers in an array.
We can generate a list of sequential numbers in rows, columns, or both.
By default, the SEQUENCE function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here.
The syntax of the SEQUENCE function is as follows:
=SEQUENCE ( rows ; [columns] ; [start] ; [step] )
Rows specifies the number of rows to fill.
Columns specifies the number of columns to fill. If omitted, 1 column is returned.
Start specifies the starting number in the sequence. If omitted, it defaults to 1.
Step defines the increment for subsequent values. If omitted, it defaults to 1.
Rows and columns need to be positive whole numbers, while start and step can be any type of number.
We can populate the desired number of rows with sequential numbers in the following way:
We can populate the desired number of columns with sequential numbers in the following way:
Note, in order to populate only columns instead of an array, we had to enter number 1 as rows part of the argument.
We can populate the whole array by specifying both the number of rows and the number of columns larger than 1:
Note the order in which sequential numbers will populate an array: first, all of the columns in the first row are populated, then all of the columns in the second row are populated, etc.
Optional part of the argument start is defined after rows and columns; in this example, using a formula:
The optional part of the argument step (how much larger each subsequent value will be in relation to the previous value) is defined last. In this example, we’ve defined it using the SUM function:
The SEQUENCE function can be appropriately used wherever we may need a sequence of numbers.
Among the most popular examples, given that the underlying values behind date and time are decimal numbers, by combining the SEQUENCE function with various date and time functions such as the EOMONTH function, we can generate sequences of dates:
Here, we are generating a sequence of end-of-month dates, a sequence of start-of-month dates, and a sequence of end-of-quarter dates by nesting different SEQUENCE functions inside of the EOMONTH function.
The SEQUENCE function is also invaluable in various types of array calculations.
Previously, for those, often-needed arrays of numbers starting with number 1 had to be “hacked” using the ROW and the COLUMN functions.
However, by combining SEQUENCE with ROWS, or by combining SEQUENCE with COLUMNS, we can generate those arrays with less calculations.
Consider the following example:
Instead of returning row numbers and deducting rows not included from those row numbers, by combining the SEQUENCE function with the ROWS function, we can retrieve the total number of rows in scope and use that number as the rows part of the argument of the SEQUENCE function.
In this practical example, we’ve used the SEQUENCE function in order to add headers and columns to the left of the values for the FILTER function returns:
Dig deeper:
5 thoughts on “SEQUENCE function”