The SEQUENCE function seems deceptively simple but is a powerful new function with some surprisingly flexible applications for dynamic arrays. This function generates a sequence of values using the specified optional start and step increment.
You can find the examples in this blog for SEQUENCE in this Excel workbook.
SEQUENCE Syntax
| SEQUENCE(rows,[columns],[start],[step]) | |
|---|---|
| rows | Number of rows to generate in the sequence (required) |
| columns | Optional number of columns to generate in the sequence (default is 1) |
| start | Optional starting value (default is 1) |
| step | Optional increment value (default is 1) |
This example generates an array, ten columns by five rows, of sequential values starting from 5 and incrementing by 5:
Figure 1 Example 1
Note that the values are generated from left to right, then top to bottom. This example uses a complex formula to supply the default values for SEQUENCE when the input cells for the function arguments are empty, to simulate using the function in the most common manor with hard-coded arguments:
=SEQUENCE(B10,IF(ISBLANK(B11),1,B11),IF(ISBLANK(B12),1,B12),IF(ISBLANK(B13),1,B13))
You can change or remove arguments in the worksheet to try out different combinations and see how SEQUENCE operates.
The real power of SEQUENCE comes from combining it with other functions. This example generates a six-week calendar layout of dates using SEQUENCE with TODAY:
Figure 2 Example 2
The formula above could instead use a cell reference for the start value and start from a specified date in another cell or calculate the date in some other way.
This example creates a schedule of time slots every ten minutes starting from the current time using SEQUENCE and NOW:
Figure 3 Example 3
The formula above could use a cell reference, or other calculations for the start value, rather than the NOW function.
Sometimes SEQUENCE is useful for generating arguments for other functions to turn those functions into dynamic array functions. This doesn't work with all functions, but for many functions, it does work to pass an array of arguments using the SEQUENCE function, and the result will calculate for each value and spill.
This example uses the LARGE function with SEQUENCE to return an array of the N largest values in a range:
Figure 4 Example 4
The LARGE function expects a scalar value for the second argument. Passing SEQUENCE(G3) specifies the array value "{1;2;3}", which causes the calculation to "lift".
The LARGE function is performed three times, once for each element in the array, and returns an array result containing the three largest values in the random data range.
This example is very similar and uses the SMALL function to return an array of the N smallest values in a range:
Figure 5 Example 5
The above example also shows two different ways of generating horizontal values that spill to adjacent cells. The first way uses the TRANSPOSE function, which transposes the columns and rows of the specified array value.
The second example demonstrates using the SEQUENCE function to generate the sequence of values horizontally instead of vertically, which eliminates the need to use the TRANSPOSE function and returns the same results more efficiently.
This last example for SEQUENCE uses TEXTJOIN with SEQUENCE, CHAR, and CODE to generate sequences of letters separated by dashes:
Figure 6 Example 6
The formulas in the cells C6:C10 are copied down the column, but the formula in cell D6 uses the FORMULATEXT function with the range argument C6:C10, so the result spills down to the adjacent cells.
SEQUENCE in GrapeCity Products
The SEQUENCE function is supported in these GrapeCity spreadsheet controls and components:
- Spread.NET Windows Forms Spreadsheet Control | Demo
- SpreadJS Javascript/Typescript/Angular/React/Vue Spreadsheet Widget | Demo
- GrapeCity Documents for Excel .NET and Java Controls | .NET Demo | Java Demo
