Excel like a finance pro.
SEQUENCE
Dynamic arrays (Microsoft 365)The period headers every model starts with, as one formula that stretches.
Difficulty
1What is it?
Every model opens with a row of periods — twelve months, thirteen weeks, five years — and most of them are hand-dragged, which means adding a period is a manual job in several places. SEQUENCE generates them as a single formula that spills as far as it needs, so changing one number changes the whole model's horizon. Combined with EDATE it produces month-ends; combined with EOMONTH it produces the end of each month regardless of length. It's the difference between a model with a hard-coded shape and one you can re-point at a different period.
2What it looks like
SEQUENCE(rows, [columns], [start], [step])- rows
- How many rows to generate. Use 1 for a horizontal header row.
- [columns]
- How many columns. Defaults to 1.
- [start]
- First value, default 1. A date serial here makes it a date sequence.
- [step]
- Increment, default 1. Use 7 for weekly, or a negative to count down.
3When you use it
- Generate 12 month-ends from one start date as a model header.
- Build a 13-week date strip for a cash forecast.
- Produce period numbers that stretch when the horizon changes.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Change the count and the whole strip reshapes — one formula, no dragging.
=EOMONTH(B1, SEQUENCE(1, B2, 0))Jan 26 · Feb 26 · Mar 26 · Apr 26 · May 26 · Jun 26 · Jul 26 · Aug 26 · Sep 26 · Oct 26 · Nov 26 · Dec 26| A | |
|---|---|
| 1 | Result |
| 2 | Jan 26 · Feb 26 · Mar 26 · Apr 26 · May 26 · Jun 26 · Jul 26 · Aug 26 · Sep 26 · Oct 26 · Nov 26 · Dec 26 |
| 3 | One formula. Change B2 and the model's whole horizon changes with it. |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#SPILL!Something is sitting in the cells the result needs.Fix: Clear the range. The error message names the blocking cell if you click it.
Dates show as 5-digit numbersSEQUENCE returns numbers; they're date serials but the cells aren't formatted.Fix: Format the spill range as dates — it keeps the format as it grows.
#NAME?The file is open in a version of Excel without dynamic arrays.Fix: SEQUENCE needs Microsoft 365 or Excel 2021. Older versions can't read it at all.
Only one cell fillsSomeone typed it with Ctrl+Shift+Enter, from habit.Fix: Dynamic arrays spill on their own. Just press Enter.
6Better functions & alternatives
- Dragging the fill handle — Fine once. The point of SEQUENCE is that the length is a formula, so the model reshapes when the horizon does.
- EDATE / EOMONTH — The pair you combine with SEQUENCE to turn an integer series into a month series.
Want SEQUENCE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.