Level 3Shaping ArraysReading, 5 min
SEQUENCE, VSTACK and friends
FILTER, SORT and UNIQUE decide which rows you get. A second family decides the shape: how many rows and columns, in what order, stacked how. They all spill, and they all nest.
| Function | Does | Example |
|---|---|---|
SEQUENCE | makes a grid of counting numbers | =SEQUENCE(12) is 1 to 12 down a column |
VSTACK | puts ranges on top of each other | =VSTACK(East!A2:C40, West!A2:C55) |
HSTACK | puts ranges side by side | =HSTACK(A2:A9, D2:D9) |
CHOOSECOLS | keeps the columns you list, in your order | =CHOOSECOLS(A2:F9, 2, 6, 5) |
CHOOSEROWS | keeps the rows you list | =CHOOSEROWS(A2:F9, -1) is the last row |
TOCOL | flattens a block into one column | =TOCOL(B2:F6, 1) skips blanks |
WRAPROWS | folds a long list into rows of N | =WRAPROWS(B2:B13, 3) |
SEQUENCE(rows, columns, start, step) is more useful than it looks. =SEQUENCE(1, 12) numbers twelve columns for a monthly model. =SEQUENCE(6, 1, DATE(2026,1,5), 7) lists six Mondays, because dates are numbers and a step of 7 is a week. Format the cells as dates and you have a calendar header that never needs retyping.
VSTACK is how you combine tabs. Three regional tabs with the same columns become one list with =VSTACK(East!A2:C40, West!A2:C55, South!A2:C31), and SORT or QUERY can work on the result. The ranges need the same number of columns, not rows. Shorter ranges in an HSTACK get padded with #N/A, so keep side-by-side pieces the same height.
CHOOSECOLS reshapes an export without touching it. Ad platforms love 40-column exports; your report needs three. =CHOOSECOLS(FILTER(A2:AN500, C2:C500="Paid Social"), 2, 6, 5) filters first, then keeps Campaign, Spend and Clicks in the order you want.
TOCOL rescues data typed as a grid. A rota with names across five weekday columns becomes one list of names with =SORT(UNIQUE(TOCOL(B2:F6, 1))). The 1 skips blank cells. WRAPROWS goes the other way: twelve monthly numbers in a column become four rows of three, one per quarter, with =WRAPROWS(B2:B13, 3).
All of these exist in current Excel with the same names, so what you learn here travels.