Array Manipulation Functions Reference

Functions for combining, reshaping, and extracting portions of arrays in Excel 365/2024+.

#HSTACK

Combine arrays horizontally (side by side).

#Syntax

=HSTACK(array1, [array2], ...)

#Basic Examples

Combine two columns:

=HSTACK(A1:A10, B1:B10)

Combine multiple ranges:

=HSTACK(A1:A10, C1:C10, E1:E10)

Add calculated column:

=HSTACK(A1:B10, A1:A10 * B1:B10)

#With Different Row Counts

Arrays with different heights are padded with #N/A:

=HSTACK(A1:A5, B1:B10)

Use IFERROR to replace padding:

=IFERROR(HSTACK(A1:A5, B1:B10), "")

#Practical Examples

Add row numbers:

=HSTACK(SEQUENCE(ROWS(A1:C10)), A1:C10)

Combine data with lookup results:

=HSTACK(A1:A10, XLOOKUP(A1:A10, D:D, E:E))

Create table with headers:

=VSTACK(
  HSTACK("ID", "Name", "Value"),
  HSTACK(A2:A10, B2:B10, C2:C10)
)

#VSTACK

Combine arrays vertically (top to bottom).

#Syntax

=VSTACK(array1, [array2], ...)

#Basic Examples

Stack two ranges:

=VSTACK(A1:C5, A10:C15)

Add header row:

=VSTACK({"Name", "Age", "City"}, A2:C100)

Combine multiple tables:

=VSTACK(Sheet1!A1:C10, Sheet2!A1:C10, Sheet3!A1:C10)

#With Different Column Counts

Arrays with different widths are padded with #N/A:

=VSTACK(A1:B5, A10:D15)

#Practical Examples

Append summary row:

=VSTACK(A1:C10, HSTACK("Total", "", SUM(C1:C10)))

Union of filtered results:

=VSTACK(
  FILTER(A1:C100, B1:B100 = "East"),
  FILTER(A1:C100, B1:B100 = "West")
)

Combine with transformation:

=VSTACK(
  HSTACK(A1:A10, "Source1"),
  HSTACK(B1:B10, "Source2")
)

#TAKE

Extract rows and/or columns from start or end of array.

#Syntax

=TAKE(array, [rows], [columns])

#Parameters

• Positive numbers: Take from start
• Negative numbers: Take from end
• Omit or 0: Take all

#Row Examples

First 5 rows:

=TAKE(A1:D100, 5)

Last 5 rows:

=TAKE(A1:D100, -5)

First and last (combine with VSTACK):

=VSTACK(TAKE(A1:D100, 3), TAKE(A1:D100, -3))

#Column Examples

First 2 columns:

=TAKE(A1:D100, , 2)

Last column:

=TAKE(A1:D100, , -1)

#Combined Row and Column

Top-left corner (5 rows, 2 columns):

=TAKE(A1:D100, 5, 2)

Bottom-right corner:

=TAKE(A1:D100, -5, -2)

Top-right corner:

=TAKE(A1:D100, 5, -2)

#Practical Examples

Top N by value:

=TAKE(SORT(A1:D100, 3, -1), 10)

Preview data:

=TAKE(A:D, 20)

Extract last entry:

=TAKE(FILTER(A1:D100, A1:A100 <> ""), -1)

#DROP

Remove rows and/or columns from start or end of array.

#Syntax

=DROP(array, [rows], [columns])

#Parameters

• Positive numbers: Drop from start
• Negative numbers: Drop from end

#Row Examples

Remove header row:

=DROP(A1:D100, 1)

Remove last row:

=DROP(A1:D100, -1)

Remove first and last:

=DROP(DROP(A1:D100, 1), -1)

#Column Examples

Remove first column:

=DROP(A1:D100, , 1)

Remove last 2 columns:

=DROP(A1:D100, , -2)

#Combined

Remove header and first column:

=DROP(A1:D100, 1, 1)

#Practical Examples

Skip header in calculations:

=SUM(DROP(A1:A100, 1))

Remove totals row:

=DROP(A1:D101, , -1)

Process data portion only:

=SORT(DROP(A1:D100, 1), 2)

#CHOOSEROWS

Select specific rows from array by index.

#Syntax

=CHOOSEROWS(array, row_num1, [row_num2], ...)

#Parameters

• Positive indices count from start (1 = first row)
• Negative indices count from end (-1 = last row)

#Basic Examples

Select specific rows:

=CHOOSEROWS(A1:D100, 1, 5, 10, 20)

Select last 3 rows:

=CHOOSEROWS(A1:D100, -1, -2, -3)

Reorder rows:

=CHOOSEROWS(A1:D10, 3, 1, 2)

Reverse row order:

=CHOOSEROWS(A1:D5, -1, -2, -3, -4, -5)

#Dynamic Row Selection

Using SEQUENCE:

=CHOOSEROWS(A1:D100, SEQUENCE(5))

Every other row:

=CHOOSEROWS(A1:D100, SEQUENCE(50, 1, 1, 2))

Random sample of rows:

=CHOOSEROWS(A1:D100, SORTBY(SEQUENCE(100), RANDARRAY(100)))

#Practical Examples

Header + specific rows:

=VSTACK(
  TAKE(A1:D100, 1),
  CHOOSEROWS(A1:D100, 5, 10, 15)
)

Select rows matching criteria (with helper):

=LET(
  data, A2:D100,
  criteria, B2:B100 > 1000,
  matches, FILTER(SEQUENCE(ROWS(data)), criteria),
  CHOOSEROWS(data, matches)
)

#CHOOSECOLS

Select specific columns from array by index.

#Syntax

=CHOOSECOLS(array, col_num1, [col_num2], ...)

#Parameters

• Positive indices count from start
• Negative indices count from end

#Basic Examples

Select columns 1, 3, 4:

=CHOOSECOLS(A1:E100, 1, 3, 4)

Reorder columns:

=CHOOSECOLS(A1:E100, 5, 1, 2, 3, 4)

Last column first:

=CHOOSECOLS(A1:E100, -1, 1, 2, 3)

#Practical Examples

Remove middle column:

=CHOOSECOLS(A1:E100, 1, 2, 4, 5)

Duplicate column:

=CHOOSECOLS(A1:D100, 1, 2, 2, 3, 4)

Select by column names (with MATCH):

=LET(
  headers, A1:E1,
  data, A1:E100,
  wanted, {"Name", "Total", "Status"},
  cols, MAP(wanted, LAMBDA(h, MATCH(h, headers, 0))),
  CHOOSECOLS(data, cols)
)

#TOCOL

Flatten array into single column.

#Syntax

=TOCOL(array, [ignore], [scan_by_column])

#Parameters

• ignore: 0 = keep all (default), 1 = ignore blanks, 2 = ignore errors, 3 = ignore both
• scan_by_column: FALSE = row by row (default), TRUE = column by column

#Basic Examples

Flatten to column:

=TOCOL(A1:D10)

Ignore blanks:

=TOCOL(A1:D10, 1)

Ignore errors:

=TOCOL(A1:D10, 2)

Ignore blanks and errors:

=TOCOL(A1:D10, 3)

#Scan Order

Row by row (default):

=TOCOL(A1:C2)

From A1,B1,C1,A2,B2,C2 to single column.

Column by column:

=TOCOL(A1:C2, , TRUE)

From A1,A2,B1,B2,C1,C2 to single column.

#Practical Examples

Unique values from 2D range:

=UNIQUE(TOCOL(A1:D10, 1))

Count non-empty in 2D:

=COUNTA(TOCOL(A1:D10, 1))

Sum all numbers in mixed range:

=SUM(TOCOL(A1:D10, 2))

#TOROW

Flatten array into single row.

#Syntax

=TOROW(array, [ignore], [scan_by_column])

#Parameters

Same as TOCOL.

#Examples

Flatten to row:

=TOROW(A1:D10)

Ignore blanks:

=TOROW(A1:D10, 1)

Column by column order:

=TOROW(A1:D10, , TRUE)

#Practical Examples

Create comma-separated list:

=TEXTJOIN(", ", TRUE, TOROW(A1:C10, 1))

Horizontal unique values:

=UNIQUE(TOROW(A1:D10, 1))

#WRAPROWS

Convert 1D array into 2D array by filling rows.

#Syntax

=WRAPROWS(vector, wrap_count, [pad_with])

#Parameters

• vector: 1D array to wrap
• wrap_count: Values per row
• pad_with: Value for incomplete last row (default #N/A)

#Examples

12 values into 4 columns:

=WRAPROWS(A1:A12, 4)

Result: 3 rows x 4 columns

Pad with empty:

=WRAPROWS(A1:A10, 4, "")

Pad with zero:

=WRAPROWS(A1:A10, 4, 0)

#Practical Examples

Create calendar layout:

=WRAPROWS(SEQUENCE(28), 7, "")

Display products in grid:

=WRAPROWS(A1:A20, 5, "-")

Reshape for display:

=WRAPROWS(TOCOL(A1:E10), 10)

#WRAPCOLS

Convert 1D array into 2D array by filling columns.

#Syntax

=WRAPCOLS(vector, wrap_count, [pad_with])

#Parameters

• vector: 1D array to wrap
• wrap_count: Values per column
• pad_with: Value for incomplete last column (default #N/A)

#Examples

12 values, 4 per column:

=WRAPCOLS(A1:A12, 4)

Result: 4 rows x 3 columns

#Practical Examples

Phone book style layout:

=WRAPCOLS(SORT(A1:A100), 25, "")

Multi-column list:

=WRAPCOLS(UNIQUE(A1:A100), 20)

#EXPAND

Resize array to specified dimensions with padding.

#Syntax

=EXPAND(array, rows, [columns], [pad_with])

#Parameters

• rows: Target row count (cannot be less than current)
• columns: Target column count (cannot be less than current)
• pad_with: Value for new cells (default #N/A)

#Examples

Expand to 10x5:

=EXPAND(A1:B3, 10, 5)

Expand with zeros:

=EXPAND(A1:B3, 10, 5, 0)

Expand with empty:

=EXPAND(A1:B3, 10, 5, "")

#Practical Examples

Pad arrays for HSTACK:

=HSTACK(
  EXPAND(A1:A5, 10, , 0),
  EXPAND(B1:B10, 10, , 0)
)

Create fixed-size output:

=EXPAND(FILTER(A1:C10, B1:B10 > 0), 10, 3, "")

Ensure minimum dimensions:

=EXPAND(A1:B2, MAX(ROWS(A1:B2), 5), MAX(COLUMNS(A1:B2), 5))

#Combining Functions

#Create Paginated View

=LET(
  data, A2:D100,
  page_size, 10,
  page_num, E1,
  start_row, (page_num - 1) * page_size + 1,
  CHOOSEROWS(data, SEQUENCE(page_size, 1, start_row))
)

#Transpose and Reshape

=WRAPROWS(TOCOL(TRANSPOSE(A1:E10)), 5)

#Dynamic Column Selection

=LET(
  data, A1:J100,
  cols_to_show, {1, 3, 5, 7},
  CHOOSECOLS(data, cols_to_show)
)

#Split and Rejoin

=VSTACK(
  TAKE(A1:D100, 50),
  {"---", "---", "---", "---"},
  DROP(A1:D100, 50)
)

#Create Matrix from Vectors

=LET(
  row_labels, A2:A10,
  col_labels, B1:E1,
  values, B2:E10,
  VSTACK(
    HSTACK("", col_labels),
    HSTACK(row_labels, values)
  )
)

#Error Handling

#Handle Mismatched Dimensions

=IFERROR(HSTACK(A1:A5, B1:B10), "")

#Safe TAKE/DROP

=LET(
  data, A1:D100,
  n, 5,
  actual_rows, ROWS(data),
  safe_n, MIN(n, actual_rows),
  TAKE(data, safe_n)
)

#Handle Empty Results

=LET(
  result, FILTER(A1:D100, B1:B100 > 1000, ""),
  IF(result = "", "No data", result)
)