The Excel SEQUENCE function is a part of dynamic array functions, and it generates a list of sequential numbers in an array.
Using the SEQUENCE function arguments, you can create two types of arrays: one-dimensional (single row or single column) and two-dimensional.
SEQUENCE function is only available for Microsoft 365 users. The function returns an array of a list of sequential numbers.
SEQUENCE Function Arguments
Here is the list of arguments:
Type the formula in cell B2 to use all arguments: rows, columns, start, and step. It is good to know that the last three arguments are optional.
=SEQUENCE (rows, [columns], [start], [step])
How to use the SEQUENCE Function
You can easily manage the size of the dynamic array using the first and second arguments.
For example, the formula below generates numbers between 0 and 115 using four rows and six columns:
=SEQUENCE(4,6,0,5)
Using these arguments, the formula creates a two-dimensional array (4 rows x 6 columns array) with sequential numbers starting with 0. In this case, the function uses the arguments below:
- Rows = 4
- Columns = 6
- Start = 0
- Step = 5
The function starts filling the array from cell B5 and increments the numbers across columns until the end of the row. After that, the function jumps to the next row and continues filling until cell G8.
SEQUENCE Function Examples
Let’s talk about the one-dimensional array!
Example: Basic usage
In the example, apply the SEQUENCE function using the first (required) argument. We will exclude all the other optional arguments: ([Columns], [Start], and [Step].
So, in this case, Excel will apply the default values for each other arguments.
Apply the formula:
=SEQUENCE(7)
Result:
The function created a sequential number list that contains 7 rows and 1 column, starts the sequence with 1, and increases the next item by 1.
Example 2: Using SEQUENCE inside Date and Time functions
In the example, we will show you how to use Excel dates with the SEQUENCE function to generate sequential dates.
Let’s generate a list that combines the SEQUENCE with the TODAY function in one formula.
=SEQUENCE(1,5,TODAY(),7)
Tip: The formula returns serial numbers by default, so we recommend changing the raw output to date format.
Example 3: Switch Row and Column orders TRANSPOSE
By default, the SEQUENCE creates sequential numbers into an array using
Combining it with the TRANSPOSE function allows you to change the rows/columns orders to columns/rows.
Compare the following formulas:
=(SEQUENCE(2,4,10,10)
=TRANSPOSE(SEQUENCE(2,4,10,10))
The first formula generates sequential numbers using 2 rows and 4 columns. The second formula switches the row/column order and returns a dynamic array containing 2 columns and 4 rows.
Tip: you will get the same result without using the TRANSPOSE function. Replace the values of the first and second arguments.
=SEQUENCE(4,2,10,10)
Create a calendar using a date sequence
In the example, you want to create a dynamic array that contains dates. To do that, combine the SEQUENCE function and the DATEVALUE function.
Create a header from cell C3 to I3 and enter the formula below:
=SEQUENCE(5,7,DATEVALUE(“2022/01/31”),1)
You can learn more about functions using our guide.
- Excel Functions