Combining, Reshaping, and Resizing Arrays in Excel

Excel’s new array functions, introduced in August 2022, give users greater flexibility in manipulating data ranges. These functions allow for efficient combining, reshaping, resizing, and extracting data from arrays, which can be particularly useful when working with large sets of data.

1. Combine Arrays

• VSTACK: Combine arrays vertically.
  • Syntax: =VSTACK(array1, array2, ...)
  • Example:excelCopyEdit=VSTACK(B2:F3, H2:L3) This combines the two ranges vertically.
• HSTACK: Combine arrays horizontally.
  • Syntax: =HSTACK(array1, array2, ...)
  • Example:excelCopyEdit=HSTACK(B2:F3, H2:L3) This combines the two ranges horizontally.

2. Reshape Arrays

• TOROW: Convert an array into a single row.
  • Syntax: =TOROW(array, ignore, by_column)
  • Example:excelCopyEdit=TOROW(B2:F3)
• TOCOL: Convert an array into a single column.
  • Syntax: =TOCOL(array, ignore, by_column)
  • Example:excelCopyEdit=TOCOL(B2:F3)
• WRAPROWS: Convert a row of data into a 2D array by wrapping the values into rows.
  • Syntax: =WRAPROWS(reference, wrap_count, pad)
  • Example:excelCopyEdit=WRAPROWS(B2:K2, 3, "empty")
• WRAPCOLS: Convert a row of data into a 2D array by wrapping the values into columns.
  • Syntax: =WRAPCOLS(reference, wrap_count, pad)
  • Example:excelCopyEdit=WRAPCOLS(B2:K2, 3, "empty")

3. Resize Arrays

• TAKE: Keeps a specific number of rows or columns from the start of the array.
  • Syntax: =TAKE(array, rows, columns)
  • Example:excelCopyEdit=TAKE(B2:F5, 2)
• DROP: Removes a specific number of rows or columns from the start of the array.
  • Syntax: =DROP(array, rows, columns)
  • Example:excelCopyEdit=DROP(B2:F5, 2)
• CHOOSEROWS: Select specific rows from an array.
  • Syntax: =CHOOSEROWS(array, row_num1, row_num2, ...)
  • Example:excelCopyEdit=CHOOSEROWS(B2:F5, 2, 4)
• CHOOSECOLS: Select specific columns from an array.
  • Syntax: =CHOOSECOLS(array, column_num1, column_num2, ...)
  • Example:excelCopyEdit=CHOOSECOLS(B2:F5, 3, 5)
• EXPAND: Expand an array to specific dimensions by adding rows and columns.
  • Syntax: =EXPAND(array, rows, columns, pad)
  • Example:excelCopyEdit=EXPAND(B2:F5, 10, 10, "empty")

Key Benefits:

• Flexibility: You can manipulate arrays in various ways to fit your exact needs.
• Efficiency: These functions allow you to perform complex data transformations with ease, without manually adjusting ranges.
• Time-Saving: These functions remove the need for complicated formulas and manual adjustments, speeding up your workflow.

These new functions are powerful tools for anyone who works with large datasets and needs to frequently reshape or combine data. You can apply them to solve various problems, including adjusting the size of data ranges, extracting specific rows or columns, and consolidating data from multiple arrays.

Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!