SEQUENCE function

The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.

In the following example, we created an array that's 4 rows tall by 5 columns wide with =SEQUENCE(4,5).

Note: September 24, 2018: The SEQUENCE function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

Syntax

=SEQUENCE(rows,[columns],[start],[step])

Argument

Description

rows

Required

The number of rows to return

[columns]

Optional

The number of columns to return

[start]

Optional

The first number in the sequence

[step]

Optional

The amount to increment each subsequent value in the array

Notes:

• An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the array for our SEQUENCE formula is range C1:G4.

• The SEQUENCE function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you're using structured references. For more details, see this article on spilled array behavior.

• Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

Example

Note: The examples shown here are available in this downloadable workbook: SEQUENCE function examples.

Here's an example of nesting SEQUENCE with INTand RANDto create a 5 row by 6 column array with a random set of increasing integers. Our formula is: =SEQUENCE(5,6,INT(RAND()*100),INT(RAND()*100)).

Here's the same example, but this time we're using SEQUENCE with TEXT, DATE, YEAR, and TODAYto create a dynamic list of months for our header row, where the underlying date will always be the current year. Our formula is: =TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,6),1),"mmm").

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.