The SEQUENCE Function in Excel

The Excel SEQUENCE function creates a list of sequential numbers with one formula: =SEQUENCE(10) fills ten cells with 1 to 10. It can count by any step, run across columns, and generate dates, which makes it a building block for numbered lists, calendars and top-N reports. This guide explains each argument with examples for anyone using Excel 2021, Excel 2024 or Microsoft 365.

What does the SEQUENCE function do?#

SEQUENCE returns an array of numbers that spills into the cells around it. You type the formula once, in one cell, and Excel fills the rest. If you change the formula, the whole list updates.

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

SEQUENCE is a dynamic array function, like FILTER and SORT. If spilling is new to you, read dynamic arrays explained simply first.

Version note: SEQUENCE needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. Excel 2019 and older show #NAME?.

How to use SEQUENCE: basic examples#

The four arguments cover most numbering jobs.

Skip an optional argument by leaving it empty between commas. =SEQUENCE(5,,10) starts at 10 and keeps the default of one column and a step of 1.

In a grid, SEQUENCE fills across first, then down: the first row is 1, 2, 3 and the second is 4, 5, 6. To fill down first, wrap it in TRANSPOSE and swap the sizes, =TRANSPOSE(SEQUENCE(3,2)). Our guide to TRANSPOSE explains how flipping works.

How to number rows with SEQUENCE#

Use SEQUENCE for a numbered list that doesn't break when you sort or delete rows. Here are six applicants:

In A2, type =SEQUENCE(6). The numbers 1 to 6 spill down to A7. To match the list length automatically, count the names instead of typing 6:

=SEQUENCE(COUNTA(B2:B100))

COUNTA counts the non-empty cells in B2:B100, so the numbering grows when you add an applicant. Keep the names in one unbroken block: a gap in the middle still counts the names after it, but the numbers won't line up with the rows.

How to number a spilled FILTER or SORT result#

Point SEQUENCE at the spill with ROWS and the # spill reference. If a FILTER in F2 spills the passing candidates:

=SEQUENCE(ROWS(F2#))

F2# means "everything F2 spilled", and ROWS counts its rows. When the filter returns more or fewer rows, the numbering resizes with it. The FILTER function guide shows how to build the list itself.

How to make a list of dates with SEQUENCE#

Dates are numbers in Excel, so SEQUENCE can count in days. Start at a date and step by 1 for every day, or by 7 for every week:

=SEQUENCE(7,1,DATE(2025,1,6),1)

This lists seven days from Monday, January 6, 2025. With a step of 7, =SEQUENCE(4,1,DATE(2025,1,6),7) lists four Mondays in a row. Format the results as dates, or you'll see serial numbers like 45663.

Months have different lengths, so you can't step by 30. Feed SEQUENCE to EDATE instead:

=EDATE(DATE(2025,1,1),SEQUENCE(12,1,0))

SEQUENCE(12,1,0) produces 0 to 11, and EDATE adds each number of months to January 1, giving the first day of every month in 2025.

How to build IDs and labels with SEQUENCE#

Join text to SEQUENCE with & to create a batch of codes:

="INV-"&SEQUENCE(5,1,101)

This returns INV-101 to INV-105. For codes with leading zeros, format the number first: ="EMP-"&TEXT(SEQUENCE(5),"000") gives EMP-001 to EMP-005.

SEQUENCE also works inside other functions. =SUM(SEQUENCE(10)) adds 1 to 10 and returns 55. And =INDEX(SORT(A2:C9,3,-1),SEQUENCE(3),{1,3}) returns the first and third columns of the top three rows of a sorted table; our guide to a top 5 list with SORT and FILTER builds on that idea.

Common SEQUENCE problems#

Most issues come from the spill or the version:

• #SPILL! A cell in the spill area isn't empty, or the formula sits inside an Excel table. Clear the cells or move the formula. Our guide to the #SPILL! error lists every cause.
• #NAME? The file is open in Excel 2019 or older, which doesn't have SEQUENCE.
• Serial numbers instead of dates. Format the spill as dates.
• Wrong direction. =SEQUENCE(5) goes down; =SEQUENCE(1,5) goes across.
• Numbers that don't change when you sort. That's by design: SEQUENCE always counts from the top. If you want each row to keep its number, paste the numbers as values first.

SEQUENCE alternatives in older Excel#

Without SEQUENCE, you can still number rows. Type 1 in the first cell, select the range, and choose Home > Fill > Series to fill a step value, or type 1 and 2 and drag the fill handle. For a formula, =ROW()-1 in row 2 returns 1 and counts up as you fill down.

To find where a value sits in a list rather than generating positions, use MATCH or XMATCH; see MATCH vs XMATCH. For sorting the numbered list, the guide to SORT and SORTBY covers both functions.

Practise it#

Try numbering the applicants in the exercise above. Then open the Modern Excel lesson to practise spill references like K2#, SORTBY and XMATCH, the functions SEQUENCE works best with, in a live spreadsheet.