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 entire series of values from a single formula, making it a powerful tool for automated headers and complex grids.
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 web, and the latest versions of the 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:
- rows is the number of rows to return.
- columns is the number of columns to return.
- start is the first number in the sequence.
- step is the jump between each value.
You can use SEQUENCE with a single argument or multiple arguments to generate lists or grids and control how values are filled. Here are a few common patterns:
Formula
What it produces
=SEQUENCE(10)
Numbers 1-10 in a single column
=SEQUENCE(10,10)
A 10×10 grid of numbers from 1 to 100
=SEQUENCE(10,10,10)
A 10×10 grid of numbers starting at 10 and increasing by 1 across the array (10 to 109)
=SEQUENCE(10,10,10,10)
A 10×10 grid of numbers starting at 10 and increasing by 10 across the array (10 to 1000)
Because SEQUENCE is a dynamic array function, it uses spill behavior. This means that when you enter the formula into a cell, Excel automatically fills the neighboring cells. Just make sure the path is clear, or you’ll run into a #SPILL! error. While that’s useful, an important trade-off to note is that dynamic array formulas can’t spill inside an Excel table. So, place the SEQUENCE formula in a regular cell and ensure there’s enough space for it to spill into a regular range.
Related
6 Functions That Changed How You Use Microsoft Excel
Dynamic array functions were a game-changer.
Use case 1: Generating dynamic, formatted project IDs
Build a self-healing list that updates as you type
The SEQUENCE function can generate a perfectly ordered list of numbers that expands or contracts based on the amount of data in an adjacent column. When nested inside other functions, these raw numbers turn into sophisticated tracking IDs.
The scenario: You want a list of IDs in column A, following the format “PRJ-0000”. The next ID should only appear when you type another project name into column B.
In cell A2, type the following formula and press Enter:
=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 column B contains only your project names and a single header.
- SEQUENCE: This takes that count and generates a simple list of numbers (1, 2, 3…). Because only the first argument (rows) is provided, Excel assumes a start point of 1 and an increment of 1.
- TEXT(…,”PRJ-0000″): This wraps the numbers in a custom format, adding the “PRJ-” prefix and ensuring each ID has leading zeros.
While you could achieve a similar look using Custom Number formatting, using the TEXT function ensures the IDs stay formatted correctly even if you copy and paste the values later.
Now, when you add a new project name to the bottom of column B, the COUNTA result increases, and the SEQUENCE function immediately spills down one extra row to create a new consecutive ID. On the other hand, if you delete a row from the middle of the list, the formula self-heals by recalculating the count of column B and re-generating the entire sequence.
Because these IDs are dynamic, they’re best used for visual organization and clean row labeling. If you need a permanent ID that stays attached to a specific project, you should use a static entry method instead. Use SEQUENCE when your priority is a perfectly ordered, gap-free list.
Use case 2: Building a dynamic interval schedule
Create recurring dates without the calendar fatigue
By using the step argument, SEQUENCE can handle the math for recurring dates. Instead of manually calculating when the next bi-weekly or monthly date occurs, the function projects the entire timeline for you.
The scenario: You need to list maintenance dates, starting on a specific date (cell A2), generating a set number of dates (cell B2), and jumping by a set number of days (cell C2).
Excel stores dates as serial numbers by default. So, before you enter your SEQUENCE formula, select the range where your formula will spill and change the Number Format in the Home tab to an appropriate Date format.
Related
Excel’s 12 Number Format Options and How They Affect Your Data
Adjust your cells’ number formats to match their data type.
Now, type or paste this into cell A6 and press Enter:
=SEQUENCE(B2, 1, A2, C2)
While you could type numbers directly into the formula—like =SEQUENCE(10, 1, DATE(2026,4,3), 14)—pointing the formula to cells A2, B2, and C2 means you can update the entire schedule instantly just by changing a single cell value. Linking your formula to these inputs makes your work reactive and easy to audit, which is why power-users avoid hard-coding values in formulas whenever possible.
Use case 3: Generating an automated “time slot” schedule
Build an appointment log without the manual dragging
Creating a schedule with specific time increments is a common task, but doing it manually is tedious. Using SEQUENCE ensures every time slot is perfectly calculated and, more importantly, incredibly easy to adjust if your meeting lengths or start times change.
The scenario: You need to create a daily log that starts at a specific time (cell A2) and lists a set number of slots (cell B2) at a specific interval in minutes (cell C2).
Before entering the formula, format your destination cells as Time via the Number Format menu in the Home tab.
Then, in cell A6, type this formula and press Enter:
=SEQUENCE(B2, 1, A2, TIME(0, C2, 0))
We use the TIME function here because the step argument in a sequence expects a numeric value, and for time calculations, that value must represent a fraction of a day. Since cell C2 contains a simple whole number for minutes, the TIME function converts that number into the exact decimal fraction Excel requires to increment time correctly.
Now, if your office hours change, or you decide to switch from 15-minute to 20-minute slots, you only have to update the values in your reference cells.
Related
13 Microsoft Excel Date and Time Functions You Should Know
Why use a calendar or a clock when Excel can track it all for you?
The SEQUENCE function is a massive productivity win, turning tedious manual dragging into a single, elegant formula. Once you’ve mastered using it for dynamic IDs, date intervals, and precise time slots, you can take your automation even further by learning how the MAKEARRAY function builds on the same idea as SEQUENCE but gives you more control by letting you define a custom formula for every 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.
