The Excel SEQUENCE function returns a list of sequential numbers in an array. Among other things, it will replace the need to use the ROW or COLUMN function to return an array of values for use in other functions.
The syntax is:
=SEQUENCE(rows, [columns], [start], [step])
rows Here you specify the number of rows to be returned.
columns is optional and specifies the number of columns to be returned. If omitted it will return 1 column.
start is optional and specifies the first number in the sequence. If omitted it will start at 1.
step is optional and specifies the increment for each subsequent value in the array. If omitted it will increment by 1.
Note: The SEQUENCE function is part of the new Excel Dynamic Arrays family. At the time of writing, Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. Excel 2019 will not have the Dynamic Array functions.
Excel SEQUENCE Function Examples
In the first example of the Excel SEQUENCE function below we used the following formula:
=SEQUENCE(5, 1, 5, 10)
To generate a simple list of 5 values, in 1 column starting at 5 and increment by 10. As this is a dynamic array formula it spills the results into the cells below:
You can also have your sequence of numbers that spill into multiple columns, as you can see below where the column argument is 2:
You can start at any number, including zero or a fraction and then increment by fractions, as shown below:
Sequences can count down by placing a negative value in the step argument, as shown below:
And you can get clever with other dynamic array formulas to create a random list of distinct values using the SORTBY, SEQUENCE and RANDARRAY functions, as shown below:
Enter your email address below to download the sample workbook.
|Excel FILTER Function||Filter cells based on criteria.|
|Excel RANDARRAY Function||Returns an array of random numbers between 0 and 1.|
|Excel SORT Function||Sort cells or arrays in ascending or descending order.|
|Excel SORTBY Function||Sort cells or arrays based on criteria.|
|Excel UNIQUE Function||Extract a unique or distinct list from a range or array.|