Free Practice WorkbookEarlier, you had to manually drag your mouse or copy-paste formulas down columns to create a list of numbers. Microsoft Excel has a gem in its toolkit called the SEQUENCE function. The SEQUENCE function is part of dynamic array formulas that can spill results into multiple rows and columns effortlessly. In this article, we will learn how to use the SEQUENCE formula in Excel.
Key Takeaways:
- SEQUENCE creates a list of numbers with one formula.
- You can choose the rows, columns, starting number, and step value.
- It is useful for numbering rows, dates, and invoice numbers.
- It can update the result when the formula changes.
- It works only in Excel 365 and Excel 2021.
Download excel workbookSEQUENCE.xlsx
Table of Contents
Introduction to Sequence Formula
Definition
The SEQUENCE function is used to create a list of numbers in order. You can use it to create a vertical list, horizontal list, or a table of numbers. With SEQUENCE, you no longer have to type numbers manually or drag a formula to fill a series.
Since it is a dynamic array function, the results update automatically when the formula changes.
Formula Breakdown
The syntax of the SEQUENCE formula is:
How to Use the Sequence Formula
STEP 1: We need to enter the SEQUENCE function in a blank cell:
=SEQUENCE(
STEP 2: The SEQUENCE arguments:
rows
How many rows to fill with values?
Let us go for 10 rows.
=SEQUENCE(10,
[columns]
How many columns to fill with values?
Let us go for 3 columns.
=SEQUENCE(10, 3,
[start]
Which number do you want the sequence of numbers to start?
Let us have it start at the number 10.
=SEQUENCE(10, 3, 10,
[step]
Which increment for each number in the sequence?
Let us increment it by 100. So that numbers should look like: 10, 110, 210, 310, and so on…
=SEQUENCE(10, 3, 10, 100)
Where Can You Use SEQUENCE?
Generating Dates
Want a list of dates starting from January 1, 2025?
=SEQUENCE(31, 1, DATE(2025,1,1), 1)
Boom! You get a full list of January 2025. Perfect for making calendars, schedules, or time series charts in your reports.
Numbered Rows in Tables
If you’re building a custom table in Excel and want auto-numbered rows, use:
=SEQUENCE(COUNTA(B2:B100))
This will automatically number every filled row in column B.
Unique Invoice Numbers
Want invoice numbers starting from 1000 and increasing by 10?
=SEQUENCE(50, 1, 1000, 10)
No more typing them manually or worrying about duplicate numbers.
Multiplication Tables
Want to generate a multiplication table (1 to 10)? Combine SEQUENCE with other formulas!
=SEQUENCE(10,1,1,1)*SEQUENCE(1,10,1,1)
The result is a 10×10 multiplication matrix!
Pro Tips
- Live updates: Change the values in the arguments, and your sequence updates instantly.
- Spill range: SEQUENCE spills over adjacent cells. Make sure there’s enough empty space or Excel will return a #SPILL! error.
- Compatibility: SEQUENCE is only available in Excel 365 and Excel 2021. It will not work in older versions like 2016 or 2013.
FAQs
What does the SEQUENCE function do?
It creates a series of numbers automatically in Excel.
How to use SEQUENCE to create dates?
You can combine SEQUENCE with the DATE function to generate a list of dates.
Why am I getting a #SPILL! error?
If there isn’t enough empty space for SEQUENCE to spill its output, Excel will return a #SPILL! error. To fix this error, you need to make sure that the cells where the output will appear is blank.
Can I change the starting number?
Yes. Use the start argument to begin the sequence from any number
Is SEQUENCE available in all Excel versions?
No. It is available only in Excel 365 and Excel 2021.
Bryan
Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).
He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.
30 Days - Full Access 
