1) Cheat Sheet (Basic Syntax)

// Combine vertically
=VSTACK(A2:C10, E2:G10)

// Combine horizontally
=HSTACK(A2:C10, E2:G10)

// Flatten into one column (remove blanks)
=TOCOL(A2:D10, 1)      // [ignore] = 1 → remove blanks

// Flatten into one row
=TOROW(A2:D10, 1)

// Take from or drop from the top, bottom, left, or right
=TAKE(A2:D100, 10)     // 10 rows from the top
=DROP(A2:D100, -5)     // remove 5 rows from the bottom

TIP Converting ranges to Excel Tables helps keep formulas resilient when columns move.

2) Combine: 10 VSTACK/HSTACK Recipes (Ready to Copy)

// ① Combine monthly sheets
=VSTACK(Jan!A1:F100, DROP(Feb!A1:F100,1), DROP(Mar!A1:F100,1))

// ② Force a consistent column order (CHOOSECOLS)
=VSTACK(CHOOSECOLS(T1,1,3,2), CHOOSECOLS(T2,1,3,2))

// ③ Append only tables that meet a condition
=VSTACK(FILTER(Sales1, Sales1[Region]="Seoul"),
        FILTER(Sales2, Sales2[Region]="Seoul"))

// ④ Remove duplicates after combining
=UNIQUE(VSTACK(TblA, TblB))

// ⑤ Keep only one header
=VSTACK(TAKE(TblA,1), DROP(TblA,1), DROP(TblB,1))

// ⑥ Use HSTACK to join descriptions and prices
=HSTACK(Products[Name], Products[Price])

// ⑦ Merge variable columns with HSTACK
=HSTACK(CHOOSECOLS(Tbl,1), TOCOL(CHOOSECOLS(Tbl,2,3,4)))

// ⑧ Place a table and summary table side by side
=HSTACK(Tbl, H2:J10)

// ⑨ Summarize multiple files (external references)
=VSTACK('[Jan.xlsx]Sheet1'!A2:F100, '[Feb.xlsx]Sheet1'!A2:F100)

// ⑩ Place monthly and regional totals side by side without a PivotTable
=HSTACK(SUMIFS(Total, Month, K2:K13), UNIQUE(Region))

3) Reshape: 10 TOCOL/TOROW Recipes

// ① 2D → 1 column (remove blanks)
=TOCOL(A2:D20, 1)

// ② 2D → 1 row
=TOROW(A2:D20, 1)

// ③ Control column-major/row-major scanning ([scan_by_col])
=TOCOL(A2:D20, 1, TRUE)   // scan by column
=TOROW(A2:D20, 1, FALSE)  // scan by row

// ④ Unique tag list
=SORT(UNIQUE(TOCOL(Posts[Tags], 1)))

// ⑤ Filter by condition after flattening
=FILTER(TOCOL(B2:E20,1), TOCOL(B2:E20,1)>=100)

// ⑥ Add column headers as labels and turn them into text
=TEXTJOIN(", ",,TOROW(HSTACK(Tbl[#Headers],TAKE(Tbl,1)),1))

// ⑦ Find the nth value in a 2D range
=INDEX(TOCOL(A2:D20), n)

// ⑧ Rank after flattening
=LET(c,TOCOL(Scores,1), RANK(c, c))

// ⑨ Count unique values with TOCOL and COUNTIF
=ROWS(UNIQUE(TOCOL(A2:D20,1)))

// ⑩ Use TOCOL and TEXTSPLIT to split multiple delimiters and create a list
=SORT(UNIQUE(TOCOL(TEXTSPLIT(A2:A,", "),1)))

4) Slice: 6 TAKE/DROP Recipes

// ① Show only the top n rows
=TAKE(SORT(Tbl,-1), 10)

// ② Remove the bottom n rows
=DROP(Tbl, -5)

// ③ Remove the rightmost column
=DROP(Tbl,, -1)

// ④ Keep only the first 2 columns on the left
=TAKE(Tbl,, 2)

// ⑤ Separate only the header
=TAKE(Tbl, 1)

// ⑥ Separate only the data (exclude the header)
=DROP(Tbl, 1)

5) 4 Combo Recipes

① Put 12 months of sales into one table and keep only the latest 3 months

=TAKE(SORT(VSTACK(Sales2025, Sales2024), 1, -1), 90)

② Gather products by category into one column and return unique values

=SORT(UNIQUE(TOCOL(CHOOSECOLS(Products, XMATCH({"A","B","C"}, Products[#Headers])), 1)))

③ Summarize after VSTACK (unique values and counts)

=LET(v, VSTACK(TblA[Item], TblB[Item]), HSTACK(UNIQUE(v), BYROW(UNIQUE(v), LAMBDA(x, COUNTIF(v, x)))))

④ Create product cards with descriptions, prices, and stock using HSTACK

=HSTACK(Products[Name], CHOOSECOLS(Products, XMATCH({"Price","Stock"}, Products[#Headers])))

6) Error and Performance Checklist

• #SPILL! — Clear the spill range (check for values, merged cells, and shapes).
• Different headers or column order → align with CHOOSECOLS.
• Large datasets → preprocess with Power Query (merge/append), then use VSTACK.
• Excel Tables recommended: references remain resilient when columns move, and performance improves.
• Standardize error messages: IFERROR(formula,"Not found")

Next, combine these functions with COUNTIF and COUNTIFS or a SEARCHABLE DROPDOWN to automate data entry and summaries.