digest
Short Summary
Main recommendation: To stack multiple columns into one long dynamic spilled list in Excel, build a two-way table with CHOOSE and then use INDEX, SEQUENCE, MOD, ROWS, and FILTER to pull the values out in order. The video credits Bill Sis for the core trick, noting that Power Query was shown as the easiest method for the two-column case in video 1644, and this formula-based approach handles three or more lists.
Key Points
- CHOOSE with array syntax
{1,2,3}returns three columns side by side as one table, so multiple lists can be treated as a single two-way table. - INDEX then pulls values from that table using a row number and a column number, both generated as dynamic arrays so the result spills.
- Row numbers cycle 1 to 8 (for an 8-row, 3-column example) by using SEQUENCE for 0 to 23, MOD by the row count, and adding 1.
- Column numbers come from SEQUENCE with a step of 1/8 starting at 0, plus 1, then truncated to the integer column index because INDEX ignores decimals.
- FILTER with
NOT(ISNA(...))removes the #N/A results so only real names remain in the stacked output.
Details Worth Noting
- Concrete instructions:
- Use
=CHOOSE({1,2,3}, Name1, Name2, Name3)to create the side-by-side table; the formula spills, and only the top-left cell holds the formula. - The transcript explains this in the context of CHOOSE's array syntax, not INDEX: commas in CHOOSE's array index mean go over a column, while semicolons would mean rows.
- Compute the total number of stacked cells using
=ROWS(F3#)*COLUMNS(F3#), which gives 8 rows times 3 columns = 24. - For row numbers, use
=MOD(SEQUENCE(24,1,0), ROWS(F3#))+1, which yields 1 through 8 repeating. - For column numbers, use
=SEQUENCE(24,1,0,1/8)+1, producing values from 1 up to just under 2 (since 1/8 step over 24 terms starting at 0 reaches 0.875, plus 1 = 1.875), which INDEX truncates to 1 and 2 in that shown example. - Wrap the result in
=FILTER(INDEX(F3#, rowNums, colNums), NOT(ISNA(INDEX(F3#, rowNums, colNums))))to drop #N/A rows. - Optionally, hide helper columns or create a defined name for the table and combine everything into one output.
- Use
- The method requires Excel with dynamic array functions (SEQUENCE, FILTER, and spilling); it will not work in older versions.
- The example uses three lists of uneven length, and the design still works when a new item is added to the third list because the formulas recalculate across the whole spilled table.
Bottom Line
The trick is a CHOOSE table plus INDEX driven by dynamic row and column sequences, followed by FILTER to clean out blanks, which yields a stacked list that updates automatically when source data changes.
