🎯 What You'll Learn
- Use
SEQUENCEto generate dynamic number arrays that grow with your data - Combine
SEQUENCEwithHSTACKto attach automatic row numbers to any dataset - Build ranked leaderboards, date series, countdowns, and grids
- Reverse a list and generate an alphabet using clever SEQUENCE tricks
- Avoid brittle hardcoding by using
ROWS()for truly dynamic formulas
📋 Before You Begin
- Comfortable entering and editing formulas in Excel or Google Sheets
- Basic understanding of cell references (e.g.,
A2:A100) - Familiarity with what a function is and how arguments work
- Optional: exposure to SORT or INDEX will help with later examples
The Core Syntax
Dynamic arrays in Excel and Google Sheets let a single formula produce many values at once. SEQUENCE is the engine behind most of them.
SEQUENCE(rows, [columns], [start], [step])
rowsHow many rows to generate (required)
[columns]How many columns wide — defaults to 1
[start]The first number — defaults to 1
[step]How much to add each time — defaults to 1. Can be negative!
The most powerful habit: replace a fixed number with ROWS(your_range). This makes the sequence breathe with your data.
Formula
=SEQUENCE(ROWS(A2:A100))
Plain English
Count how many rows are in A2:A100
Then generate 1, 2, 3 … up to that count
If I add rows, the sequence grows automatically
formula
=SEQUENCE(5, 1, 10, -2) -- 5 rows, 1 col, start at 10, step -2
Output (spills into 5 cells)
10 → 8 → 6 → 4 → 2
Why SEQUENCE Changes Everything
Before SEQUENCE, numbering rows meant typing 1, 2, 3 manually or dragging a fill handle. Here's why that breaks — and why SEQUENCE fixes it.
ME
I have a list of 500 names. How do I auto-number them so the number updates if I delete a row?
XL
Great question! If you typed numbers manually and delete row 50, you get a gap — 49 jumps to 51. 😬
ME
That's exactly my problem. I have to re-number everything by hand every time!
How SEQUENCE Generates an Array
Think of SEQUENCE like a factory conveyor belt. You set the settings once — how many items, where to start, how far apart — and the belt fills the cells automatically.
12 Real-World Examples
Click any example to expand the formula and see why it works.
1 Dynamic Row Indexing
▾
=HSTACK(SEQUENCE(ROWS(A2:A100)), A2:A100)
✅ Why it works:
ROWS(A2:A100) returns however many rows exist. SEQUENCE generates 1 to that count. HSTACK glues the numbers to your data. Add a row → numbers auto-update.
2 Ranked Leaderboard
▾
=HSTACK(SEQUENCE(ROWS(D2:D10)), SORT(D2:E10, 2, -1))
✅ Why it works: SORT rearranges scores from highest to lowest. SEQUENCE then adds 1st, 2nd, 3rd labels perfectly aligned — because both have the same row count.
3 Monthly Column Headers
▾
=SEQUENCE(1, 12, DATE(2026,1,1), 31)
✅ Why it works: 1 row, 12 columns, starting at Jan 1 2026, stepping ~31 days. Format the output as "MMM YYYY" to get Jan 2026 → Dec 2026 across your header row. Note: EDATE is more precise for exact month steps.
4 Countdown Timer
▾
=SEQUENCE(10, 1, 10, -1)
✅ Why it works: Start at 10, step by −1. Generates 10, 9, 8 … 1. Negative steps are just as valid as positive ones.
5 Even Numbers Only
▾
=SEQUENCE(10, 1, 2, 2)
✅ Why it works: Start at 2, step by 2 → 2, 4, 6, 8 … 20. Change start to 1 and you get odd numbers instead.
6 Weekly Pay Dates
▾
=SEQUENCE(52, 1, DATE(2026,1,2), 7)
✅ Why it works: Jan 2 2026 is a Friday. Stepping by 7 days gives every Friday for 52 weeks. Format as Date to see them properly.
7 Grid Generation
▾
=SEQUENCE(5, 5)
✅ Why it works: When you specify both rows AND columns, SEQUENCE fills a 2D matrix. 5×5 generates 1 through 25, filling left-to-right, top-to-bottom.
8 Financial Modeling Intervals
▾
=SEQUENCE(5, 1, 0, -5)
✅ Why it works: Generates 0, −5, −10, −15, −20. Useful for modeling scenarios like drawdowns or loss intervals at fixed increments.
9 Test Data — User IDs
▾
="USR-"&SEQUENCE(20, 1, 100, 1)
✅ Why it works: The
& operator concatenates text to each number. Produces USR-100, USR-101 … USR-119. Great for mock data and prototypes.
10 Bi-Monthly Audit Dates
▾
=SEQUENCE(6, 1, DATE(2026,1,1), 60)
✅ Why it works: 6 dates, stepping ~60 days, gives roughly every two months across the year. Perfect for compliance or audit scheduling trackers.
11 Reverse a List
▾
=INDEX(A2:A10, SEQUENCE(ROWS(A2:A10), 1, ROWS(A2:A10), -1))
✅ Why it works: SEQUENCE counts down from 9 to 1. INDEX uses those descending numbers as row positions, so it reads the list from bottom to top — effectively flipping it.
12 Generate A–Z Alphabet
▾
=CHAR(SEQUENCE(26, 1, 65))
✅ Why it works: ASCII code 65 is "A". SEQUENCE generates 65 to 90. CHAR converts each number to its letter. You get A through Z in one formula.
Pro Tips
🔑 The HSTACK Marriage
- Combining SEQUENCE with HSTACK is the go-to way to attach row numbers to a dataset that has no built-in index column in the source.
- The result is a clean numbered table that updates live as rows are added or removed.
🔑 Never Hardcode the Row Count
- Always use
ROWS(your_range)instead of a fixed number like100. - If your range is a named Table, use
ROWS(Table1)— the sequence will expand the moment you tab into a new row.
🔑 The Zero Step Edge Case
- A step of 0 repeats the start value for every position. Rarely useful, but it won't throw an error.
- For dates, the step is always measured in days. Use
EDATEwrapped around SEQUENCE when you need exact same-day-of-month jumps.
Test Your Understanding
Apply what you learned — these questions test real-world scenarios, not definitions.
1. You delete 3 rows from your list. You used =HSTACK(SEQUENCE(ROWS(A2:A100)), A2:A100). What happens to the row numbers?
2. A colleague wants every second Friday of every month for 2026. Which formula approach would you suggest?
3. You need a 10×10 grid of numbers (1–100). What single formula achieves this?
4. A user reports their alphabetical sequence =CHAR(SEQUENCE(26,1,65)) is suddenly showing numbers instead of letters. Where would you look first?
🎓 Summary
- SEQUENCE generates arrays of numbers — dynamic, no fill handle needed
- Use
ROWS(range)instead of a fixed number so your formula grows with your data - Combine with HSTACK for auto-numbered tables, SORT for leaderboards, INDEX for reversals
- Use DATE() as the start value to create date sequences; step is always in days
- For exact monthly jumps, wrap SEQUENCE inside EDATE instead of using a fixed step