Concept
1. SEQUENCE
=SEQUENCE(rows, [columns], [start], [step])
=SEQUENCE(10)→ 1 to 10 (serial numbers that never break when rows are deleted).=SEQUENCE(1, 12)→ 1 to 12 across.=SEQUENCE(5, 1, 100, 10)→ 100, 110, 120, 130, 140.- Numbered list matching another spill:
=SEQUENCE(ROWS(F2#)).
2. Date lists
Every day of April 2026:
=SEQUENCE(30, 1, DATE(2026,4,1))
(format as dates). Month starts for FY2026-27:
=EDATE(DATE(2026,4,1), SEQUENCE(12, 1, 0))
→ 1-Apr-2026 … 1-Mar-2027. (Don't use SEQUENCE with step 30 for months — months aren't 30 days.)
Calendar headers across: =TEXT(EDATE(DATE(2026,4,1), SEQUENCE(1,12,0)), "mmm-yy") → Apr-26 … Mar-27.
3. RANDARRAY — test data
=RANDARRAY(rows, [columns], [min], [max], [integer])
=RANDARRAY(5, 1, 1, 100, TRUE)→ 5 random whole numbers 1–100.- Random sample of 5 orders:
=TAKE(SORTBY(tblSales, RANDARRAY(ROWS(tblSales))), 5).
It recalculates on every change — copy and Paste Values to freeze the result.
4. TOCOL and TOROW (Microsoft 365 / 2024)
Turn a block into one column or row:
| Formula | Result |
|---|---|
=TOCOL(B2:D4) |
9 values in one column, read row by row |
=TOCOL(B2:D4, 1) |
same, ignoring blanks |
=TOCOL(B2:D4, 0, TRUE) |
read column by column |
=TOROW(B2:D4) |
9 values in one row |
| Uses: |
- A unique list from a messy multi-column block:
=SORT(UNIQUE(TOCOL(B2:D20, 1))). - A quick "unpivot-lite" of a small wide table (for real reporting, Power Query's Unpivot is better — Module 3).
The opposite: WRAPROWS(array, n) / WRAPCOLS fold a single column into a block of n columns.
5. Combining them
A 12-month target grid for 3 regions, regions down and months across:
B1: =TEXT(EDATE(DATE(2026,4,1), SEQUENCE(1,12,0)), "mmm")
A2: =SORT(UNIQUE(tblSales[Region]))
Then fill values with a formula that uses A2# and B1#.
Common mistakes
Month lists with day steps. Forgetting RANDARRAY changes constantly. TOCOL including blanks (use ignore = 1). Using these in Excel 2019 or older (#NAME?).