SEQUENCE is one of the new functions which Microsoft announced on 24 September 2018. These new functions make use of changes made to Excel’s calculation engine, which enable a single formula to display (or “spill” if using the new terminology) results in multiple cells. In the case of SEQUENCE, it will generate a list of sequential numbers in an array.
At the time of writing, Microsoft has only made this new functionality available to Office 365 subscribers on the Insider channel. The updates will be rolled out to all Office 365 subscribers at a future point, but first Microsoft need to assess the impact the changes to the calculation engine will have. The new dynamic array functions and features are not available in Excel 2019 or previous versions, so it is definitely worth getting an Office 365 subscription, otherwise, you will be waiting until Excel 2022, and that’s just too long to wait!
The arguments of the SEQUENCE function
The SEQUENCE function has four arguments:
=SEQUENCE(Rows, [Columns], [Start], [Step])
- Rows: The number of rows to return
- [Columns]: The number of columns to return. If excluded, it will return a single column.
- [Start]: The first number in the sequence. If excluded it will start at 1.
- [Step]: The amount to increment each subsequent value. If excluded, each increment will be 1.
Please note, Excel creates sequences across the columns, before moving down to the next row.
Examples of using the SEQUENCE function
The following examples illustrate how to use the SEQUENCE function.
Example 1 – Basic Usage
The two examples below show the basic usage of the SEQUENCE function.
The formula in Cell B2 is:
In this formula, only the Rows argument is provided; all the optional arguments have been excluded. Therefore, the default options are applied. SEQUENCE has created a list of 8 rows, 1 column, starting at 1 and incrementing by 1.
The formula in Cell E2 is:
This formula is creating an array which is 8 rows, 2 columns, starting at 5 and incrementing by 10. The order of the numbers is important here, the function increments across the columns before moving to the next row.
Example 2 – Using SEQUENCE inside other functions
The example below shows how to use SEQUENCE inside the DATE function.
The formula in Cell B2 is:
This formula creates a sequence of monthly dates starting January 1st 2019. As the SEQUENCE function is returning 13 values, the DATE function will cause the result to roll-over into the next year.
My post about the SORT function also contains an example of using the SEQUENCE function to create a list of the top 5 results.
Want to learn more?
There is a lot to learn about dynamic arrays and the new array functions. Check out my other posts here to learn more:
- Introduction to dynamic arrays – learn how the excel calculation engine has changed.
- UNIQUE – to list the unique values in a range
- SORT – to sort the values in a range
- SORTBY – to sort values based on the order of other values
- FILTER – to return only the values which meet specific criteria
- SEQUENCE – to return a sequence of numbers
- RANDARRAY – to return an array of random numbers
Also, here are some other resources you might find useful:
- Ebook – Dynamic arrays straight to the point – By Bill Jelen (free until December 2018)
- Video – Comprehensive dynamic array formulas: The power of dynamic arrays
- Blog Post – Dynamic array formulas & spill ranges