The Problem with Drag-and-Fill
One of the most common tasks in Excel is creating a numbered list. You type “1” in cell A2, “2” in cell A3, highlight them both, grab the little green square in the corner, and drag it down to row 100.
While this works, it is entirely static and highly fragile. If you delete row 50, your numbers instantly jump from 49 to 51, ruining your sequential numbering. If you need a sequence of 10,000 numbers, dragging the mouse down the screen takes an infuriating amount of time.
With the introduction of Dynamic Arrays, Microsoft created a function that instantly generates massive, mathematically perfect grids of numbers with a single formula: the SEQUENCE function.
Step 1: The Basic Syntax
The SEQUENCE function allows you to define exactly how large of an array you want, and it will instantly “spill” the numbers into the adjacent cells.
The syntax is:
=SEQUENCE(rows, [columns], [start], [step])
To instantly generate a list of numbers from 1 to 100 in a single column, click cell A1 and type:
=SEQUENCE(100)
Press Enter. Excel will instantly fill A1 down to A100. If you ever need to expand it to 500, you don’t drag anything; you simply change the formula to =SEQUENCE(500).
Step 2: Creating a 2D Grid of Numbers
The SEQUENCE function is not limited to a single column. By utilizing the second argument, [columns], you can create a two-dimensional matrix.
Imagine you are building a calendar or a seating chart, and you need a grid that is 5 rows deep and 4 columns wide, numbering from 1 to 20.
Type:
=SEQUENCE(5, 4)
Excel will instantly generate a perfectly formatted 5×4 block of numbers, wrapping the sequence seamlessly from the end of one row to the beginning of the next.
Step 3: Controlling the Start and Step
You don’t always want to start at number 1, and you don’t always want to count by 1.
The final two arguments allow you to control the mathematical progression.
- [start]: The first number in the sequence.
- [step]: The amount to add to each subsequent number.
If you want to generate a list of exactly 50 numbers, but you want to start at the number 1000 and count by tens (1000, 1010, 1020, etc.), the formula is:
=SEQUENCE(50, 1, 1000, 10)
This is incredibly useful for generating unique SKU numbers, invoice identifiers, or randomized test data matrices where the step value prevents obvious consecutive sequencing.
Step 4: Dynamic Numbering (The Ultimate Trick)
The true power of SEQUENCE is unleashed when you combine it with the COUNTA function to create a self-updating numbered list.
Imagine Column B contains a list of Employee Names, and you want Column A to automatically number them. If you add a new employee, you want a new number to appear. If you delete an employee, you want the numbers to instantly recalculate so there are no gaps.
In cell A2, type:
=SEQUENCE(COUNTA(B2:B1000))
How it works:
The COUNTA function looks at the list of names and counts how many there are (e.g., 14 names). It passes that number (14) directly into the SEQUENCE function, causing it to generate exactly 14 numbers.
If you type a 15th name into Column B, COUNTA instantly updates to 15, and SEQUENCE instantly generates the 15th number. You now have a perfectly bulletproof, auto-calculating numbered list that cannot be broken by deleting rows.