Yes, =SEQUENCE(5) generates five consecutive numbers starting from 1. But if you think it only counts up by 1 or only fills columns top-to-bottom, you’re missing half its power—and tripping over its quirks.
Quick Answer
The SEQUENCE function creates arrays of numbers on the fly: rows, columns, start value, step size—all controllable in one formula. It’s dynamic (no dragging), array-native (works with FILTER, SORT, XLOOKUP), and spills automatically—but only in Microsoft 365 or Excel for the web. If you’re on Excel 2019 or earlier? You’ll need workarounds.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| SEQUENCE function | Type =SEQUENCE(rows,cols,start,step) in one cell (e.g., A1) | Dynamic lists, calendar grids, numbered invoices | Requires Excel 365/2021; won’t spill into merged cells |
| ROW() with offset | Enter =ROW(A1:A5)+9 (Ctrl+Enter for multi-cell array in older Excel) | Legacy Excel users needing ascending sequences | Fragile—if rows inserted above, offsets break; no built-in step control |
| Fill Handle + Formula | Enter =A1+2 in A2, select A1:A10, then Ctrl+D | One-time static lists where interactivity isn’t needed | Not dynamic—won’t update if source changes; manual effort required |
| INDEX + SEQUENCE combo | Use =INDEX(list,SEQUENCE(5)) to pull first 5 items from a named range | Sampling data, creating dynamic dropdowns, filtered reports | Adds complexity; overkill for simple numbering |
| SEQUENCE inside TEXTJOIN | Try =TEXTJOIN(", ",TRUE,SEQUENCE(3,2,100,50)) → "100, 150, 200, 100, 150, 200" | Building labels, codes, or formatted strings from numeric patterns | Spill behavior suppressed—only returns single cell result |
Method 1 Deep Dive
Let’s build a sales team roster with auto-numbered IDs, custom start points, and non-linear steps. You manage seven reps at Acme Corp, and want IDs like 1001, 1003, 1005… up to 1013.
Type this in cell D2:=SEQUENCE(7,1,1001,2)
You’ll get a vertical spill from D2:D8: 1001, 1003, 1005, 1007, 1009, 1011, 1013. Notice how 1001 is the start, 2 is the increment—not the second number. That trips up everyone at first. (Trust me—I once spent 20 minutes debugging why my sequence jumped by 17.)
Now try something unexpected: =SEQUENCE(2,4,5,-1) in F1. It spills 2 rows × 4 columns, starting at 5 and stepping down by 1:
F1:I1 → 5, 4, 3, 2
F2:I2 → 5, 4, 3, 2
Wait—that’s identical across rows. Why? Because SEQUENCE applies the step *within each row*, left to right—then repeats the full row for subsequent rows. So for multi-row output, step size controls horizontal progression only. To get descending *down* the column? Flip rows/columns: =SEQUENCE(4,1,5,-1) gives you 5,4,3,2 vertically in one column.
Real example: Sarah Chen needs invoice numbers for Q2 2024, starting at INV-2024-001 and going to INV-2024-012. In A1, enter:="INV-2024-"&TEXT(SEQUENCE(12,1,1),"000")
This spills A1:A12 as INV-2024-001 through INV-2024-012. Clean. No drag. No macros.
Method 2 Deep Dive
Say you’re building a weekly project tracker for six departments—and need a repeating 3-week cycle (Week 1, Week 2, Week 3, Week 1…) across 18 rows.
You could nest MOD inside SEQUENCE. Try this in B2:="Week "&MOD(SEQUENCE(18,1,0),3)+1
It works because SEQUENCE(18,1,0) gives 0–17. MOD(...,3) yields 0,1,2,0,1,2… Then +1 shifts it to 1,2,3,1,2,3…
But here’s the counterintuitive part: SEQUENCE doesn’t accept arrays for its arguments. So you can’t do =SEQUENCE(A1:A3) — even if A1:A3 contains {5;3;4}. It will return #VALUE!. You *must* feed scalars. Want variable row counts? Wrap it in LET or use INDIRECT (though INDIRECT is volatile—use sparingly).
Sample dataset: Your finance team tracks quarterly payouts for four vendors:
| Vendor | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Nexus Labs | $24,500 | $26,100 | $27,800 | $29,200 |
| Orion Dynamics | $18,300 | $19,400 | $20,600 | $21,900 |
| VistaSoft Inc | $31,200 | $32,700 | $34,100 | $35,800 |
| Strata Group | $12,900 | $13,500 | $14,200 | $15,000 |
="Q"&SEQUENCE(1,4). Spills C1:F1 as Q1, Q2, Q3, Q4. Add a date: =DATE(2024,SEQUENCE(1,4)*3-2,1) gives you 2024-01-01, 2024-04-01, 2024-07-01, 2024-10-01.
Keyboard shortcut tip: Press Alt + M, V to open the Formulas tab, then Alt + S to jump to Insert Function—type “sequence” and hit Enter. Much faster than scrolling.
Cheat Sheet
| What You Want | Formula | Cell Example | Shortcut / Note |
|---|---|---|---|
| 10 numbers, 1 to 10 | =SEQUENCE(10) | A1 | Default start=1, step=1 |
| 5×3 grid, start=100, +5 per cell | =SEQUENCE(5,3,100,5) | B2 | Spills B2:D6 |
| Months Jan–Dec as text | =TEXT(DATE(2024,SEQUENCE(12),1),"mmm") | E1 | Spills E1:E12 |
| Every 3rd row number (3,6,9…) | =SEQUENCE(10,1,3,3) | G1 | Useful for sampling |
| Reverse count: 10 down to 1 | =SEQUENCE(10,1,10,-1) | H1 | Step must be negative |
| Dates: 7 days from today | =TODAY()+SEQUENCE(7) | I1 | Spills I1:I7 |
| Alphabet letters A–Z | =CHAR(64+SEQUENCE(26)) | J1 | 65 = "A", so 64+1=65 |
