How to Use the SEQUENCE Function in Excel
SEQUENCE generates a calculated list of consecutive numbers in one formula. Instead of typing 1, 2, 3 and dragging a fill handle, you specify the number of rows, columns, starting value, and step. The result spills into the required cells and changes automatically when you change the inputs.
Quick answer
=SEQUENCE(10,1,1,1)This returns the numbers 1 through 10 in a vertical spill range.
Syntax explained
=SEQUENCE(rows,[columns],[start],[step])- rows: how many rows the result should contain.
- columns: how many columns the result should contain.
- start: the first value; if omitted, Excel uses 1.
- step: the amount added between values; if omitted, Excel uses 1.
Real examples
Automatic invoice numbering
=SEQUENCE(100)Use this for a 100-row working list. If the list must start at 1001, use =SEQUENCE(100,1,1001,1).
Even numbers
=SEQUENCE(20,1,2,2)This creates 2, 4, 6 and so on without helper formulas.
A two-dimensional grid
=SEQUENCE(5,4,1,1)The formula spills five rows by four columns. This is useful for test data, matrix layouts, and controlled demonstrations.
Generate dates
=SEQUENCE(30,1,DATE(2026,1,1),1)Excel stores dates as serial numbers, so adding one advances by one day. Format the spill range as a date.
Using SEQUENCE with other functions
SEQUENCE becomes more useful when it supplies an array to another function. For example, =INDEX(A2:A101,SEQUENCE(10)) returns the first ten items. You can combine it with SORT, FILTER, UNIQUE, INDEX, TAKE, DROP and other dynamic-array functions to build repeatable reports.
SEQUENCE with FILTER
=FILTER(A2:D100,SEQUENCE(ROWS(A2:A100))<=10,"No rows")For ordinary filtering, use FILTER's actual criteria rather than SEQUENCE. The example demonstrates that SEQUENCE can generate positions when an array operation needs them.
Why #SPILL! appears
SEQUENCE returns multiple cells, so every destination cell must be available. If text, formulas, merged cells, or other objects block the spill area, Excel can show #SPILL!. Select the formula cell and inspect the highlighted spill boundary. Clear the blocking cells rather than copying the formula repeatedly.
Excel version compatibility
SEQUENCE is a dynamic-array function available in modern Excel versions including Microsoft 365 and Excel 2021 and later. Older Excel builds may not support it. Google Sheets also supports SEQUENCE, although function behavior and surrounding features can differ.
Common mistakes
- Putting a non-empty value inside the spill range.
- Expecting rows and columns to be reversed.
- Generating date serials but forgetting to format them as dates.
- Using a negative step when a positive sequence was intended.
- Building a volatile or unnecessarily complex formula when a simple SEQUENCE is enough.
Verification checklist
- Confirm the expected number of rows and columns.
- Check the first value and step.
- Confirm the entire spill range is empty.
- Format dates, currency, or IDs appropriately.
- Test the formula after adding rows to the source data when the sequence is part of a larger model.
Takeaway
SEQUENCE is best treated as an array generator. Once you understand rows, columns, start, and step, it can replace manual numbering and provide clean inputs for modern dynamic-array formulas.
Advanced SEQUENCE patterns
Reverse sequences
=SEQUENCE(10,1,10,-1)A negative step creates a descending sequence. This is useful when generating rank positions or reverse indexes.
Horizontal sequences
=SEQUENCE(1,12,1,1)Set rows to 1 and columns to 12 when you need month numbers or period labels across a report.
Dynamic size
=SEQUENCE(COUNTA(A2:A100))The number of generated rows can be driven by another calculation. Test this carefully when blanks occur inside the source because COUNTA counts non-empty cells rather than meaningful records.
SEQUENCE for model testing
A controlled sequence is useful when testing formulas because it produces predictable inputs. For example, generate 1–100 and verify that a percentage, lookup, or aggregation formula behaves correctly at the beginning, middle, and end of the range.
Performance considerations
Dynamic arrays are usually convenient, but enormous spill ranges can increase calculation work. Generate only the rows and columns actually needed. Avoid volatile wrappers around a sequence when a fixed or input-driven size is sufficient.
Related functions
Use RANDARRAY for random test data, SORT for ordering a generated array, UNIQUE for deduplication, FILTER for selecting records, and TAKE/DROP for trimming array results.
Final test
Change the rows, columns, start and step arguments one at a time. If each change produces the expected shape and values, the sequence is ready to become part of a larger workbook.