SEQUENCE in Excel: numbered lists and date series
One SEQUENCE formula writes 1 to 100, a list of dates, or a grid of numbers. How its four parts work, with examples you can copy and check.
The SEQUENCE function writes a list of numbers from one formula.
=SEQUENCE(100) fills 100 cells with 1 to 100, and you type
nothing else.
SEQUENCE needs Excel 2021, Excel 2024 or Microsoft 365. It also works in Excel for the web.
Excel 2019 and older show #NAME? instead.
The examples below come from one real job. You run a 6-week training course. You need a numbered list of people, a date for each session, and some headings.
The four parts
The shape is =SEQUENCE(rows, columns, start, step). Only the
first part is required. Each part you leave out counts as 1.
| Part | What it means | If left out |
|---|---|---|
| rows | How many rows to fill | Required |
| columns | How many columns to fill | 1 |
| start | The first number | 1 |
| step | How much to add each time | 1 |
The formula sits in one cell. Its results fill the cells below or to the right. This is called
spilling, and those cells must be empty. If one is not, you get
#SPILL!.
Job 1: number a list of names
The names are in B2:B6. In A2, type =SEQUENCE(5). The numbers 1
to 5 fill A2:A6.
| Row | A | B |
|---|---|---|
| 1 | No. | Name |
| 2 | 1 | Amira |
| 3 | 2 | Joel |
| 4 | 3 | Priya |
| 5 | 4 | Tomas |
| 6 | 5 | Wen |
A fixed 5 goes wrong when a sixth person signs up. Count the names instead. Type
=SEQUENCE(COUNTA(B2:B50)) in A2. Today it gives 1 to 5. Type a
name in B7 and a 6 appears in A7.
COUNTA only works here if the names have no gaps. With an empty row in the middle, the count is too small. Then the last name has no number.
These numbers are not IDs. They belong to the rows, not to the names. If the names move, the numbers stay where they are. For a number that stays with a person, copy the column and paste it as values.
Job 2: a date for each week
The course starts on Monday 5 January 2026 and meets every Monday. A date is a number in Excel, so SEQUENCE can count in dates. The step is 7 days.
=SEQUENCE(6,1,DATE(2026,1,5),7) fills 6 cells down:
| 5 Jan 2026 |
| 12 Jan 2026 |
| 19 Jan 2026 |
| 26 Jan 2026 |
| 2 Feb 2026 |
| 9 Feb 2026 |
Each one is a Monday, because each is 7 days after the one before. If the cells show numbers like 46027, format them as dates.
Better still, put the settings in cells. Type the number of weeks in E1 and the first date in
E2. Then type =SEQUENCE(E1,1,E2,7). Change E1 to 8, and the
list grows to 8 dates.
Job 3: headings across a row
Rows come first, so a list across a row needs 1 as the first part.
=SEQUENCE(1,4,2024) gives 2024, 2025, 2026 and 2027 in 4 cells
side by side.
Months are harder, because they are not the same length. A step of 30 days goes wrong within a
year. Use DATE and give it the SEQUENCE as the month:
=DATE(2026,SEQUENCE(1,12),1). It gives the first day of each
month, from 1 January 2026 to 1 December 2026. Format the cells as mmm to show Jan to Dec.
Job 4: a grid of numbers
Give SEQUENCE rows and columns, and it fills a block. The room has 3 rows of 4 seats.
=SEQUENCE(3,4) fills 3 rows and 4 columns:
| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
| 9 | 10 | 11 | 12 |
It fills across first, then down. So seat 5 is at the start of the second row, not under seat 1.
Job 5: a list you never see
Put SEQUENCE inside another function, and nothing spills. The other function uses the list and gives one answer.
-
=SUM(SEQUENCE(100))gives 5050, the total of 1 to 100. -
=INDEX(SEQUENCE(6,1,DATE(2026,1,5),7),6)gives the date of the last session, Monday 9 February 2026. -
="Week "&SEQUENCE(4)does spill. It gives Week 1 to Week 4, as text labels.
What goes wrong
The count in the wrong place. You want 12 month headings across row 1. You
type =SEQUENCE(12), and the numbers run down column A. The first
part is rows. For a row, write =SEQUENCE(1,12).
Something in the way. A note or an old number sits in a cell the list needs.
The whole formula shows #SPILL!. Click the formula cell and
Excel draws a dashed border around the space it wants. Clear that space.
Typing over the list. You click A4 and type a number over it. Now A2 shows
#SPILL!, because A4 is in the way. Change the formula, not the
cells it fills.
Questions people ask
How do I count down with SEQUENCE?
Use a negative step. =SEQUENCE(5,1,5,-1) gives 5, 4, 3, 2 and
1.
Can the step be a decimal?
Yes. =SEQUENCE(5,1,0,0.25) gives 0, 0.25, 0.5, 0.75 and 1.
What do I use in Excel 2019 or older?
Type =ROW()-1 in A2 and fill it down. Row 2 gives 1, row 3
gives 2, and so on. It does not grow by itself, so you fill it down again for new rows.
How is this different from dragging the fill handle?
Dragging writes fixed numbers into each cell. SEQUENCE is one formula, so you change the list by changing one cell. Use the fill handle for a list that never changes. Use SEQUENCE when the count or the start date can change.
SEQUENCE is one of the functions that spill, like FILTER. The FILTER function article has more on spilling. The lessons below are real sheets you type into, and each answer is checked when you press Enter.