Excel SEQUENCE Function
Summary
The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional. Start and step values are arguments.
Purpose
Get array of list of seuential numbers
Return value
Array of sequential values
Syntax
=SEQUENCE (rows, [columns], [start], [step])
Arguments
- rows - Number of rows to return.
- columns - [optional] Number of columns to return.
- start - [optional] Starting value (defaults to 1).
- step - [optional] Increment between each value (defaults to 1).
Version
Usage notes
The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, controlled by rows and columns arguments. Start and step (increment) values are also supplied as arguments. In the example shown, the formula in B4 is:
=SEQUENCE(10,5,0,3)
With this configuration, SEQUENCE returns an array of sequential numbers, 10 rows by 5 columns, starting at zero and incremented by 3. The result is 50 numbers starting at 0 and ending at 147, as shown in the screen.
To return 10 numbers in one column, starting at -5 and ending at 5, incremented by 1:
=SEQUENCE(10,1,-5,1)
SEQUENCE is a new function available in Excel 365 only.
SEQUENCE formula examples
In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to....
Excel does not provide a formula function to append or combine ranges, either horizontally or vertically. You can use Power Query for this task, and this makes sense for data transformations that must be automated and...
The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range".
In...
Although Excel does not provide a formula function to append or combine ranges (you can append ranges with Power Query), you can use the LAMBDA function to create a custom function to combine ranges. In the example...
When working with Excel formulas, there are some situations where you want an array of sums rather than a single sum. Functions like SUM won't work, because they aggregate results and return a single value. The MMULT...
At the core, this formula uses the WEEKDAY function to test a number of dates to see if they land on a given day of week (dow) and the SUMPRODUCT function to tally up the total.
When given a date, WEEKDAY simply...
The first COLUMN function generates an array of 7 numbers like this:
{2,3,4,5,6,7,8}
The second COLUMN function generates an array with just one item like this:
{2}
which is then subtracted from the first array to...
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by...
The EDATE function moves forward or backwards in time in one-month increments from a given start date. The SEQUENCE function is a dynamic array function that can generate multiple results that "spill" onto the...
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is...
The goal of this formula is to return the next n birthdays in a set of 25 birthdays as shown, based on the current date. The complication is that the birthdays contain the year of birth, so we can't filter and sort the...
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called "names" which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this...
At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant:
MID(...
A MAC (Media Access Control) address is a unique identifier assigned to most network adapters. Two common IEEE 802 standards display a MAC address in 6 groups of 2 hexadecimal digits separated by a colon (:) or hyphen...
Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same...
Related videos
With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior. In this video, we quickly demo all 6.
In this video, we'll introduce the SEQUENCE function. The SEQUENCE function lets you generate numeric sequences, which can be used for dates, times, and more.
In this video, we'll look at how to generate a sequence of dates with the SEQUENCE function.
In this video, we'll look at how to generate a sequence of times with the SEQUENCE function.
Related functions
See also
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
