The SEQUENCE function, introduced with dynamic arrays, offers an elegant method to produce number sequences without resorting to manual fill. Whether it’s a column from 1 to 100, a 5×5 matrix, or lists with custom steps, it greatly simplifies the construction and maintenance of sequential data.
Somaire
In brief
🔢 SEQUENCE generates in seconds linear or matrix number sequences in Excel, without copy-pasting.
⚙️ 4 main parameters: rows, columns, start value, and step.
📊 Ideal for preparing distribution bounds, feeding a histogram or a Sparkline chart without tedious manipulations.
🗂️ Compatible with all Excel versions that support dynamic arrays (Office 365 and recent versions).
What is the SEQUENCE function?
Origin and context
SEQUENCE is part of the dynamic array functions launched by Microsoft to reduce the use of complex formulas and overlapping ranges. Rather than manually extending a series down a column, it returns an automatically spillable array as soon as you enter it into a cell.
Detailed parameters
The basic syntax is written as:
=SEQUENCE(rows; columns; start; step)
– rows: number of rows to generate.
– columns: number of columns (optional, default 1).
– start: first value of the array (optional, default 1).
– step: increment between two values (optional, default 1).
Why adopt SEQUENCE?
Automation and time saving
By avoiding dragging the fill handle, SEQUENCE speeds up the preparation of numbered data. For a monthly report, for example, you can create a list from 1 to 12 with a single formula and update it in seconds.
Flexibility for dynamic arrays
Combined with FILTER or INDEX, SEQUENCE becomes a true engine for generating dynamic ranges. You can thus build matrices whose size automatically adapts to the context.
Practical usage examples
Creating a simple sequence
To get a column of 10 increasing numbers:
=SEQUENCE(10)
This returns:
| 1 |
| 2 |
| 3 |
| 10 |
Generating a 3×4 matrix
If you want a table with 3 rows and 4 columns, starting at 5 and advancing by steps of 2:
=SEQUENCE(3;4;5;2)
Result:
| 5 | 7 | 9 | 11 |
| 5 | 7 | 9 | 11 |
| 5 | 7 | 9 | 11 |
Combining SEQUENCE with other functions
- Create dynamic ranges for a frequency histogram, automatically defining the boundaries.
- Produce Sparkline charts based on a sequential series, without additional ranges.
- Generate primary keys or sequential identifiers before a random generation to ensure uniqueness.
Example with FILTER
Imagine extracting the first 5 elements from a list whose exact extent you do not know. Combine:
=FILTER(yourRange; SEQUENCE(5)<=ROW(yourRange))
The SEQUENCE function here defines the limit of rows to retrieve, without manual adjustments.
FAQ
1. Does SEQUENCE work on all versions of Excel?
It is only available in Office 365 and Excel 2021+, where dynamic arrays are supported.
2. Can we generate a decreasing sequence?
Yes: use a negative step; for example, =SEQUENCE(5;1;10;-2) produces 10, 8, 6, 4, 2.
3. How to automatically extend a series if a row is inserted?
The reactive table managed by SEQUENCE resizes: inserting a blank row triggers an automatic update of the result.
4. What is the difference with the fill handle?
The fill handle requires manual adjustment and can break links if you move or insert cells, whereas SEQUENCE remains consistent and maintained by formula.
