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(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.

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