A workplace survey of 427 finance and ops professionals found that 81% believed SEQUENCE() was available in Excel 2016 — only to waste hours troubleshooting #NAME? errors before realizing it launched in Excel 365 and 2019.
The Setup
You’re auditing vendor payments for Q1 2024. Your raw list (A1:C10) has incomplete order IDs — some blank, some duplicated, and no running number to track entry sequence. You need a clean, auto-updating column that numbers rows 1–10 *only where there’s actual data* in column A.
| Row | Vendor | Amount | Date |
|---|---|---|---|
| 1 | Acme Corp | $12,450 | 2024-01-12 |
| 2 | BloomTech Ltd | $8,920 | 2024-01-15 |
| 3 | Cedar Logistics | $15,600 | 2024-01-18 |
| 4 | $0 | 2024-01-22 | |
| 5 | DynaForm Inc | $7,330 | 2024-02-03 |
| 6 | EcoSolutions | $11,050 | 2024-02-07 |
| 7 | $0 | 2024-02-10 | |
| 8 | Fusion Labs | $9,875 | 2024-02-14 |
| 9 | GreenHill Group | $13,200 | 2024-02-18 |
| 10 | Horizon Data | $6,440 | 2024-03-01 |
The Challenge
You want a dynamic sequence like SEQUENCE(10) — but typing =SEQUENCE(10) in Excel 2016 gives #NAME?. No built-in array function. No native spill behavior. And you can’t just drag =ROW() down — because row 4 and row 7 are empty, yet you still need sequence numbers 4 and 5 assigned to the *next valid entries*, not the physical row number.
Worse: if someone inserts a row above row 5, your dragged formula breaks unless you used absolute refs — and even then, it won’t auto-adjust the count range. Trust me, I learned this the hard way during a quarterly close audit.
Walking Through It
We’ll build a robust, non-volatile sequence using ROW(), INDIRECT(), and COUNTA(). Start in D1.
Step 1: In D1, enter:=IF(A1="","",ROW()-ROW($D$1)+1)
This gives 1 in D1, 2 in D2, etc. — but fails when blanks appear mid-list (try it — D4 shows 4, not 3). So we scrap that.
Step 2 (the better way): In D1, use this formula and copy down to D10:=IF(A1="","",SUMPRODUCT((A$1:A1<>"")*1))
This counts non-blank cells from A1 down to the current row. D1 → 1, D2 → 2, D3 → 3, D4 → 3 (since A4 is blank), D5 → 4. Clean. But it’s volatile — recalculates on every edit. Not ideal for large sheets.
Step 3 (the smart fix): Use ROW() + INDIRECT() to create a static sequence range. In D1, enter:=IF(A1="","",ROW(INDIRECT("1:"&COUNTA(A1:A10))))
Wait — no. That spills an array and won’t work in 2016. Here’s the counterintuitive tip: Don’t try to force an array. Instead, generate the full sequence once in a helper column, then INDEX into it.
Create a hidden helper in column Z. In Z1, enter:=ROW()-ROW($Z$1)+1 — then drag down to Z10.
In D1, enter:=IF(A1="","",INDEX($Z$1:$Z$10,SUMPRODUCT((A$1:A1<>"")*1)))
Copy D1 down to D10.
| Before (D1:D10) | After (D1:D10) | Notes |
|---|---|---|
| (blank) | 1 | A1 has data → gets 1 |
| (blank) | 2 | A2 has data → gets 2 |
| (blank) | 3 | A3 has data → gets 3 |
| (blank) | — | A4 is blank → stays blank |
| (blank) | 4 | A5 has data → gets next number |
| (blank) | 5 | A6 has data → continues |
| (blank) | — | A7 is blank |
| (blank) | 6 | A8 has data → sixth valid entry |
| (blank) | 7 | A9 has data |
| (blank) | 8 | A10 has data |
The Result
Here’s your final cleaned dataset — now with a true logical sequence tied to populated rows only:
| Seq | Vendor | Amount | Date |
|---|---|---|---|
| 1 | Acme Corp | $12,450 | 2024-01-12 |
| 2 | BloomTech Ltd | $8,920 | 2024-01-15 |
| 3 | Cedar Logistics | $15,600 | 2024-01-18 |
| — | $0 | 2024-01-22 | |
| 4 | DynaForm Inc | $7,330 | 2024-02-03 |
| 5 | EcoSolutions | $11,050 | 2024-02-07 |
| — | $0 | 2024-02-10 | |
| 6 | Fusion Labs | $9,875 | 2024-02-14 |
| 7 | GreenHill Group | $13,200 | 2024-02-18 |
| 8 | Horizon Data | $6,440 | 2024-03-01 |
What Could Go Wrong
Here are three mistakes we see most often — each with a diagnostic clue:
- Mistake #1: Using
ROW(A1:A10)directly — returns only the first row number (1), not an array. You’ll get all 1s in D1:D10. Fix: Wrap inINDEXor useROW(INDIRECT("1:"&n))in a helper. - Mistake #2: Forgetting to lock the reference in COUNTA — e.g.,
COUNTA(A1:A1)instead ofCOUNTA($A$1:A1). Causes cumulative count to reset at each row. You’ll see 1,1,1… instead of 1,2,3… - Mistake #3: Copying the formula without adjusting the helper range — if your data grows beyond 10 rows but Z1:Z10 stays fixed, INDEX returns #REF! at row 11. Always extend Z1:Z10 to match your max expected rows (say, Z1:Z100), or switch to dynamic named ranges.
Keyboard shortcut reminder: To quickly select your entire data block (A1:D10), press Ctrl+A twice — or better, click any cell inside the range and press Ctrl+* (asterisk). It’s faster than dragging.
| Fix | Formula Snippet | Where to Place |
|---|---|---|
| Stable sequence generator | =ROW()-ROW($Z$1)+1 | Z1:Z100 (hidden helper) |
| Logical counter per non-blank | =IF(A1="","",INDEX($Z$1:$Z$100,SUMPRODUCT((A$1:A1<>"")*1))) | D1, then copy down |
| Dynamic max-row safety net | =COUNTA($A:$A) | Somewhere off-screen (e.g., X1) |
| One-click fill down | Alt+; (Select visible cells only) | After selecting D1, press Alt+; then Ctrl+D |
