How to Use SEQUENCE Function in Excel

Key Insights

SEQUENCE is a dynamic array function in Excel 365/2021 that generates sequential numbers automatically, eliminating manual data entry for numbered lists, date ranges, and test datasets
The function accepts four parameters—rows, columns, start value, and step interval—allowing you to create both simple lists and complex 2D grids with custom increments (including negative and decimal steps)
SEQUENCE becomes powerful when combined with functions like DATE, INDEX, LET, and RANDARRAY to build dynamic calendars, sample datasets, and self-updating formulas that adapt as your data changes

Introduction to the SEQUENCE Function

The SEQUENCE function generates arrays of sequential numbers based on parameters you specify. Available in Excel 365 and Excel 2021, it’s one of the dynamic array functions that fundamentally changed how Excel handles multi-cell outputs.

Instead of manually typing numbers 1 through 100 or dragging fill handles, SEQUENCE creates these sequences instantly with a single formula. The function automatically spills results into adjacent cells, making it ideal for creating numbered lists, generating test data, building date ranges, and constructing calculation grids.

The syntax is straightforward:

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

rows: Number of rows to return (required)
columns: Number of columns to return (optional, defaults to 1)
start: Starting number (optional, defaults to 1)
step: Increment value (optional, defaults to 1)

Basic SEQUENCE Examples

Let’s start with fundamental applications that demonstrate each parameter.

A simple vertical list of 10 numbers:

=SEQUENCE(10)

This produces 1, 2, 3… through 10 in a single column.

For a horizontal sequence across five columns:

=SEQUENCE(1, 5)

This creates 1, 2, 3, 4, 5 in one row.

To start from a different number, specify the start parameter:

=SEQUENCE(5, 1, 100)

Result: 100, 101, 102, 103, 104 vertically.

For custom intervals, use the step parameter:

=SEQUENCE(6, 1, 0, 5)

This generates: 0, 5, 10, 15, 20, 25—perfect for creating scales or evenly-spaced values.

You can also create even numbers:

=SEQUENCE(10, 1, 2, 2)

Or odd numbers:

=SEQUENCE(10, 1, 1, 2)

Creating 2D Arrays and Grids

SEQUENCE truly shines when generating two-dimensional arrays. Specify both rows and columns to create grids.

A basic 5×5 grid:

=SEQUENCE(5, 5)

This creates a grid numbered 1 through 25, filling left-to-right, top-to-bottom.

For a multiplication table, combine SEQUENCE with itself:

=SEQUENCE(10, 1) * SEQUENCE(1, 10)

This multiplies a vertical sequence (1-10) by a horizontal sequence (1-10), producing a complete 10×10 multiplication table.

For calendar structures, create a 6-row by 7-column grid:

=SEQUENCE(6, 7, 1)

This gives you a 42-cell grid numbered sequentially—the foundation for a monthly calendar layout. You can then use conditional formatting or additional formulas to highlight specific dates.

Practical Use Cases with Other Functions

SEQUENCE becomes exponentially more useful when combined with other Excel functions.

Date sequences are incredibly practical. Generate the next 30 days:

=SEQUENCE(30, 1, TODAY())

This creates a dynamic list starting from today’s date. Since it references TODAY(), the list updates automatically each day.

For a specific date range:

=SEQUENCE(7, 1, DATE(2024, 1, 1))

This produces seven consecutive dates starting January 1, 2024.

Creating test data is another common application. Generate 100 random transaction IDs starting at 1000:

=SEQUENCE(100, 1, 1000, 10)

Result: 1000, 1010, 1020… through 1990.

Combine with RANDARRAY for random sample data:

=RANDARRAY(SEQUENCE(50), 1, 100, 500, TRUE)

This generates 50 random integers between 100 and 500.

Dynamic named ranges benefit from SEQUENCE with INDEX:

=INDEX(A:A, SEQUENCE(10, 1, 2))

This extracts rows 2 through 11 from column A, creating a dynamic reference that doesn’t require manual range selection.

For extracting every nth row:

=INDEX(A:A, SEQUENCE(10, 1, 1, 3))

This pulls every third row: A1, A4, A7, A10, etc.

Advanced Techniques

Reverse sequences use negative step values:

=SEQUENCE(10, 1, 10, -1)

This counts down from 10 to 1—useful for countdown timers or reverse-ordered lists.

Decimal sequences work perfectly for creating precise scales:

=SEQUENCE(20, 1, 0, 0.5)

Result: 0, 0.5, 1, 1.5, 2… through 9.5.

This is valuable for financial calculations, scientific measurements, or any scenario requiring fractional increments.

Nested SEQUENCE functions create complex patterns:

=SEQUENCE(5, 1, 1) * SEQUENCE(1, 4, 10, 10)

This produces a grid where each row is a different multiple of 10, 20, 30, 40.

Using LET for reusable calculations makes complex formulas maintainable:

=LET(
    rows, 12,
    start_date, DATE(2024, 1, 1),
    dates, SEQUENCE(rows, 1, start_date, 7),
    TEXT(dates, "mmm dd, yyyy")
)

This creates a readable formula that generates 12 weekly dates starting January 1, 2024, formatted as “Jan 01, 2024” etc. The LET function assigns names to intermediate calculations, making the logic clear.

Another LET example for a dynamic commission table:

=LET(
    sales, SEQUENCE(10, 1, 10000, 5000),
    rate, 0.05,
    commission, sales * rate,
    HSTACK(sales, commission)
)

This generates sales values from 10,000 to 55,000 (incrementing by 5,000) alongside calculated commissions, displayed side-by-side using HSTACK.

Common Errors and Troubleshooting

#SPILL! errors occur when SEQUENCE tries to output results but adjacent cells contain data. The solution is simple: clear the cells where SEQUENCE needs to spill its results. Excel shows a dotted border indicating the required space.

If you need to keep existing data, move your SEQUENCE formula to an area with sufficient empty cells.

#CALC! errors indicate invalid parameters. Common causes:

Negative values for rows or columns (must be positive)
Non-numeric parameters
Excessively large arrays that exceed Excel’s calculation limits

Check your parameters and ensure they’re appropriate numeric values.

Compatibility issues are significant. SEQUENCE only works in Excel 365 and Excel 2021. If you share workbooks with users on Excel 2019 or earlier, they’ll see #NAME? errors. For backward compatibility, you must use traditional methods like fill handles or array formulas with ROW() and COLUMN() functions.

Performance considerations matter with large arrays. A formula like =SEQUENCE(1000000) creates one million cells of data, which can slow down your workbook significantly. Keep arrays reasonably sized, and consider whether you actually need all that data or if a smaller sample would suffice.

For extremely large datasets, database tools or Power Query may be more appropriate than worksheet formulas.

Best Practices and When to Use SEQUENCE

Use SEQUENCE when you need:

Dynamic sequences that update automatically
Quick test data generation
Complex patterns that would be tedious to create manually
Formulas that reference sequential values in calculations

Stick with traditional fill handles when:

You need a simple one-time sequence
Sharing with users on older Excel versions
The sequence is static and won’t change

For maintainability, add comments or use LET to name your parameters. Instead of =SEQUENCE(52, 1, DATE(2024,1,1), 7), write:

=LET(weeks, 52, start, DATE(2024,1,1), interval, 7, SEQUENCE(weeks, 1, start, interval))

This makes your intent crystal clear to anyone reviewing your formulas later.

Explore related dynamic array functions to expand your capabilities:

RANDARRAY: Generate random numbers
SORTBY: Sort data dynamically based on SEQUENCE-generated indices
FILTER: Use SEQUENCE to create row numbers for filtering logic
UNIQUE: Combine with SEQUENCE for numbered unique lists

SEQUENCE is a foundational tool in modern Excel. Master it, and you’ll find dozens of applications that save time and make your spreadsheets more dynamic and powerful.