Free Practice WorkbookGone are the days when you had to manually drag your mouse or copy-paste formulas down columns just to create a list of numbers. Microsoft Excel has evolved into a powerhouse of automation and efficiency, and one of the gems in its toolkit is the SEQUENCE function
First introduced in Office 365 (now Microsoft 365) in 2018, the SEQUENCE function is part of Excel’s dynamic array formulas—meaning it can spill results into multiple rows and columns effortlessly. In this article, we’ll explore how it works, break down the syntax, walk through an example step-by-step, and look at practical applications that make this function more than just a number generator.
Key Takeaways:
- SEQUENCE eliminates manual number entry by dynamically generating lists or tables of numbers with a single formula.
- The formula syntax is flexible, allowing optional inputs like columns, starting number, and increment values.
- You can create custom patterns, including date lists, invoice numbers, and even multiplication tables.
- It’s part of Excel’s dynamic array feature, so results automatically spill into adjacent cells.
- Only available in Excel 365 and Excel 2021, the SEQUENCE function won’t work in older Excel versions.
Download excel workbookSEQUENCE.xlsx
Table of Contents
Understanding Sequence Formula
Definition
The SEQUENCE function creates an array of sequential numbers. Whether you want a vertical list, a table of numbers, or even a pattern starting at 100 and increasing by 5, SEQUENCE has your back.
It eliminates the need for:
- Manual input of number lists
- Autofill dragging
- Helper columns
With a single formula, you get a dynamic, auto-updating series. This is particularly useful when working on regularly updated reports, templates, or dashboards where numbering needs to stay consistent even as the data changes.
Formula Breakdown
Here’s the basic syntax of the SEQUENCE formula:
By tweaking these values, you can go from a simple vertical list to a custom grid of values that grows across both rows and columns—perfect for advanced layout planning.
Step-by-Step Guide to Use the Sequence Formula
This step-by-step guide will walk you through how to set up the formula, customize it with different arguments, and instantly generate a dynamic array of numbers—no dragging, no fuss. Let’s dive in!
I explain how you can do this below:
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)
Try it out now and see that your sequence of numbers is generated magically!
Where Can You Use SEQUENCE?
Here are some practical scenarios where SEQUENCE is incredibly useful:
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 is the purpose of the SEQUENCE function in Excel?
The SEQUENCE function is used to generate an array of sequential numbers in Excel. It helps users create lists or tables of numbers without having to manually drag cells or write repetitive formulas. It’s ideal for automating number patterns, indexing tables, or populating data quickly. Since it’s a dynamic array formula, it adjusts automatically when the referenced cells or arguments change.
Can I use SEQUENCE to generate a series of dates?
Yes, absolutely! You can pair SEQUENCE with the DATE function to generate a continuous list of dates. For instance, =SEQUENCE(30,1,DATE(2025,1,1),1) will produce every date from January 1 to January 30, 2025. The function works well for building calendars, schedules, and timelines in Excel. Just remember the result will be numeric unless the cells are formatted as dates.
What happens if there isn’t enough space for the SEQUENCE output?
If there isn’t enough empty space for SEQUENCE to spill its output (e.g., existing data in adjacent cells), Excel will return a #SPILL! error. To fix this, make sure the output area is clear and free of any values or formatting blocks. Once there’s sufficient room, SEQUENCE will automatically populate the intended array without errors.
Can I create decreasing sequences with the SEQUENCE formula?
Yes, you can create decreasing sequences by using a negative step value. For example, =SEQUENCE(5,1,100,-10) will produce: 100, 90, 80, 70, 60. This makes it useful for countdowns, reverse orders, or generating offset data patterns. Just make sure the starting number and step align with your intended output length.
Is SEQUENCE compatible with older versions of Excel?
No, SEQUENCE is only available in Excel for Microsoft 365 and Excel 2021. If you try using it in Excel 2016, 2013, or older, you’ll get a #NAME? error. For backward compatibility, you’ll need to use alternatives like ROW() or INDEX() formulas combined with helper columns—though those lack the simplicity and flexibility of SEQUENCE.
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 
