Whether you are preparing a financial report, a project schedule, or an inventory tracking model, manually creating a number sequence in Excel can quickly become tedious. The SEQUENCE function, introduced since Excel 365, offers a fast and elegant alternative: with a few clicks, you generate lists, matrices, and even dynamic ranges that automatically adjust to your needs. In this article, we explore this little gem step by step, with concrete examples, tips to avoid pitfalls, and some practical comparisons.
What is the SEQUENCE function ?
SEQUENCE is a dynamic function that returns an array (a range) of consecutive numbers, without having to “drag” the fill handle. You can control the number of rows, columns, the starting value, and the step (increment). It integrates seamlessly into array formulas: as soon as you change a parameter, the entire array updates.
Syntax and detailed parameters
The basic syntax is as follows:
=SEQUENCE(lignes; [colonnes]; [début]; [pas])
- rows: number of rows to generate (must be ≥ 1).
- columns (optional): default is 1, indicates the number of columns in the range.
- start (optional): starting value of the sequence (default 1).
- step (optional): increment between two values (default 1). Can be negative to generate a decreasing sequence.
Each of these parameters can be a reference to another cell or the result of a function. For example, if you want to start at the value contained in A2 and add 5 to each row:
=SEQUENCE(10;; A2; 5)
Concrete usage examples
1. Generate a simple ascending sequence
To create a list of 20 numbers starting at 1:
=SEQUENCE(20)
The result is a column from 1 to 20. If you prefer a row, add the second argument:
=SEQUENCE(1; 20)
2. Create a data matrix
SEQUENCE becomes particularly powerful for populating a two-dimensional table. Suppose you want 5 rows and 4 columns, starting at 100 with a step of 10:
=SEQUENCE(5; 4; 100; 10)
Excel then displays a 5×4 matrix:
| 100 | 110 | 120 | 130 |
|---|---|---|---|
| 140 | 150 | 160 | 170 |
| 180 | 190 | 200 | 210 |
| 220 | 230 | 240 | 250 |
| 260 | 270 | 280 | 290 |
3. Combine with other formulas
The real power of SEQUENCE appears when combined with functions like SUMIFS to filter and sum dynamic ranges, or to automatically generate named column headers. For example, to sum only the even columns:
=SOMME(SEQUENCE(1;10;1;1) * (MOD(SEQUENCE(1;10);2)=0))
Here, SEQUENCE(1;10;1;1) creates the numbers 1 to 10, MOD tests parity, and multiplication masks the odd ones.
Tips and Best Practices
Adjust the step and orientation
- For a decreasing sequence, set step to negative.
- If you want to swap rows and columns without changing the function, combine with TRANSPOSE.
- Use the SEQUENCE function as a reference in INDEX or CHOOSE to create repeating patterns (for example, days of the week).
Error handling and compatibility
SEQUENCE is only available starting from Excel 365 and Excel Online. If you are working on an earlier version, the solution involves classic techniques: dragging the fill handle, or creating a formula like:
=SI(LIGNE(A1)>10;"";LIGNE(A1))
You can also combine text formulas to manually generate custom entries, similar to text functions like LEFT or MID that are used in scenarios of cutting or structuring data.
Comparison with other methods
Manual autofill series vs SEQUENCE
Before SEQUENCE, the user had to:
- Write the first term.
- Drag the fill handle to the desired term.
- Click on the “Series” option to define a step.
It’s already fast, but:
Generating a new range means repeating these steps manually, whereas with SEQUENCE, you only have to change one argument.
Extending SEQUENCE to more complex cases
Creating a dynamic schedule
Imagine a monthly schedule: you can create the dates of the month in one formula:
=SEQUENCE(JOUR(DERNIER.JOUR.OUVRE(MOIS(AUJOURD’HUI());0));1;DEBUT.MOIS(AUJOURD’HUI());1)
At the start of each month, your schedule resets without touching the workbook.
Using with slicers
When you combine SEQUENCE with a structured table and interactive slicers, you get a display that adjusts on the fly: each selection filters and reformats the grid. No more need for macros.
FAQ
- What is the difference between SEQUENCE and incremental fill ?
- Incremental fill remains manual and sensitive to location, whereas SEQUENCE is declarative: it is a formula that automatically generates an entire range.
- Can SEQUENCE be used in Google Sheets ?
- Yes, Google Sheets also offers SEQUENCE with a very similar syntax, which facilitates workbook portability between platforms.
- How to hide zeros or empty values in a sequence ?
- You can wrap SEQUENCE in IF or LET to replace certain terms with empty text:
=SI(SEQUENCE(5;1;0;1)=0;"";SEQUENCE(5;1;0;1)). - Is it possible to combine SEQUENCE and VLOOKUP ?
- Absolutely: SEQUENCE can dynamically generate row indexes passed to VLOOKUP or INDEX, which further automates your data extraction.
In summary, SEQUENCE is a real time saver for generating any type of sequences, arrays, or dynamic headers. If you regularly work with repeating patterns, adopting this function will save you many manual operations. Do not hesitate to experiment with it and combine it with other formulas to unleash its full power.