If you’re clicking and dragging in Excel to create long lists of data, you’re still in old-school mode. The SEQUENCE function is a modern dynamic array tool that generates an entire series of values from a single formula, making it a powerful tool for automated headers and complex networks.
Here’s how the SEQUENCE function works and three ways you can use it to solve real-world problems.
The SEQUENCE function is available in Excel 2021 or later, Excel for Microsoft 365, Excel for the webThe latest versions of Excel mobile and tablet apps.
How the SEQUENCE function works
Let your formulas do the heavy lifting
Excel’s SEQUENCE function has four arguments, but only the first is required:
=SEQUENCE(rows,(columns),(start),(step))
where:
- ranks is the number of rows to return.
- columns is the number of columns to return.
- to start is the first number of the sequence.
- step is the transition between each value.
You can use SEQUENCE with a single argument or multiple arguments to create lists or grids and control how values are populated. Here are some common examples:
|
The formula |
What does it produce? |
|---|---|
|
=BEE(10) |
Numbers 1-10 in one column |
|
=SEQUENCE(10,10) |
A 10×10 grid of numbers from 1 to 100 |
|
=DIFFERENCE(10,10,10) |
A 10×10 grid of numbers starting at 10 and increasing by 1 across the array (10 to 109) |
|
=DIFFERENCE(10,10,10,10) |
A 10×10 grid of numbers starting at 10 and increasing by 10 across the array (10 to 1000) |
Since SEQUENCE is a dynamic array function, it uses shedding behavior. This means that when you enter a formula in a cell, Excel automatically fills in the adjacent cells. Just make sure the path is clear or you’ll run into one #PUMP! wrong. While this is useful, there is an important trade-off to note dynamic array formulas cannot be poured into an Excel spreadsheet. So, place the SEQUENCE formula in a regular cell and make sure there’s enough room for it to spill into a regular range.
Use case 1: Generating dynamic, formatted project identifiers
Create a self-healing list that updates as you type
The SEQUENCE function can create a perfectly ordered list of numbers that expands or contracts based on the amount of data in an adjacent column. When placed inside other functionsthese raw numbers are converted into complex tracking identifiers.
Scenario: You want a list of IDs in column A according to the format “PRJ-0000”. The next ID should only appear when you type another project name in column B.
In the cell A2type the following formula and press Enter it:
=TEXT(SEQUENCE(COUNTA(B:B)-1), "PRJ-0000")
Here’s how each part of the formula works:
- COUNTA(B:B)-1: This counts how many cells in column B contain data, subtracting one to account for the header row. This works best when it contains column B only your project names and single title.
- Sequence: This takes that count and creates a simple list of numbers (1, 2, 3…). Because only the first argument (ranks) is provided, Excel assumes a starting point of 1 and an increment of 1.
- TEXT(…,”PRJ-0000″): This wraps the numbers in a special format, adds the “PRJ-” prefix, and ensures that each ID contains zeros.
Although you can get a similar look using Custom Number formatUsing the TEXT function ensures that IDs remain in the correct format, even if you copy and paste the values later.
Now when we add a new project name under column B, the COUNTA result is incremented and the SEQUENCE function immediately populates an additional row to create a new sequence ID. On the other hand, if you delete a row from the middle of the list, the formula heals itself by recalculating the number of column B and recreating the entire sequence.
Since these identifiers are dynamic, they are best used for visual organization and clean row labeling. If you need a permanent ID attached to a specific project, you should use the static login method instead. Use SEQUENCE when your priority is a perfectly ordered, gap-free list.
Use case 2: Building a dynamic interval table
Create recurring dates without calendar fatigue
using step argument, SEQUENCE can handle math for repeating dates. Instead of manually calculating when the next bi-weekly or monthly date occurs, the feature projects the entire timeline for you.
Scenario: You need to list maintenance dates starting from a certain date (cell A2), creating a certain number of dates (cell B2), and by a certain number of days (cell C2).
Excel stores dates as serial numbers by default. So before entering your SEQUENCE formula, select the range your formula will spill and change Number format in the year House select the appropriate tab History format.
Now type or paste this into the cell A6 and press Enter:
=SEQUENCE(B2, 1, A2, C2)
If you can enter numbers directly into a formula—like =SEQUENCE(10, 1,DATE(2026,4,3), 14)—the formula’s focus on cells A2, B2, and C2 means you can instantly update the entire table by changing the value of just one cell. Associating your formula with these inputs makes your work reactive and easy to audit, so power users avoid hardcoding values in formulas whenever possible.
Use case 3: Creating an automated “timespan” schedule
Create an appointment log without manual dragging
Creating a schedule with specific time increments is a common task, but doing it manually is tedious. Using SEQUENCE ensures that each time slot is perfectly calculated and, more importantly, incredibly easy to adjust if your appointment time or start time changes.
Scenario: You need to create a daily log that starts at a certain time (cell A2) and lists a certain number of slots (cell B2) at a certain interval in minutes (cell C2).
Before entering the formula, format your destination cells as Time through Number format in the menu House badge.
Then in the cell A6type this formula and click Enter it:
=SEQUENCE(B2, 1, A2, TIME(0, C2, 0))
We use the TIME function here because step The sequence argument expects a numeric value, and for time calculations, this value must represent a fraction of a day. Since cell C2 contains a simple integer for the minutes, the TIME function converts that number to the exact decimal fraction that Excel requires to increment the time correctly.
Now, if your business hours change or you decide to go from a 15-minute time to a 20-minute time, you only need to update the values in your reference cells.
The SEQUENCE function is a huge productivity gain that turns tedious manual dragging into a single, elegant formula. Once you’ve mastered using dynamic identifiers, date ranges, and exact time ranges, you can take your automation even further by learning how to. MAKEARRAY function It builds on the same idea as SEQUENCE, but gives you more control by letting you define an individual formula for each row and column in the array.
- OS
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more. includes
