The TOCOL function is the fastest way to convert any rectangular range or spilled array into one tidy column that updates automatically. It belongs to Excel’s dynamic array family and is available in Microsoft 365 on Windows, Mac, and the web. The core idea is simple: take values laid out across rows and columns and return them in a single vertical list, in the order you choose, with options to ignore blanks and errors. Microsoft defines the syntax as =TOCOL(array, [ignore], [scan_by_column]), where array is the source range or array, ignore tells Excel which values to drop during the flatten, and scan_by_column controls whether values are read row-wise or column-wise. By default, Excel scans by row and keeps everything, but both behaviors are configurable for cleaner outputs.

Read More

The TOROW function converts any rectangular range or spilled array into one clean row that updates automatically as the source changes. It belongs to Excel’s dynamic array family in Microsoft 365 on Windows, Mac, and the web. The syntax is =TOROW(array, [ignore], [scan_by_column]). The array argument is the range or array to flatten. The optional ignore argument lets Excel drop blanks and/or errors during the transformation. The optional scan_by_column argument controls the read order, letting TOROW process by rows (default) or by columns. Microsoft’s documentation emphasizes that TOROW “returns the array in a single row”, with predictable options for ignoring blanks and errors and a switch for scan direction.

Read More

The CHOOSECOLS function returns exactly the columns specified from any range or spilled array and delivers them as a single dynamic spill. It’s the clean, modern way to curate fields for reports, dashboards, and exports without helper columns or complex INDEX logic. The syntax is =CHOOSECOLS(array, col_num1, [col_num2], …). The array can be a range, a spilled result from another function, or even an inline array. Each col_num is a column index relative to the left of the array, and the output keeps all rows while returning only the requested columns, in the order provided. Microsoft’s reference highlights that out‑of‑range indices return #VALUE and that the result always spills into as many columns as requested. Availability is Microsoft 365 for Windows/Mac and Excel for the web.

Read More

The CHOOSEROWS function returns specific rows from any range or spilled array and delivers them as a single dynamic spill that updates as the source changes. It’s the modern, readable way to pick arbitrary rows without INDEX/OFFSET gymnastics or manual copy‑paste. The syntax is =CHOOSEROWS(array, row_num1, [row_num2], …). The array is the source block; row numbers are positional indexes in that array. Positive numbers count from the top, negative numbers count from the bottom, and duplicates are allowed to repeat rows in the output. Microsoft’s official reference confirms this behavior and notes that a #VALUE error occurs if any requested index is zero or beyond the number of rows. Availability is Microsoft 365 on Windows/Mac and Excel for the web.

Read More

The TAKE function returns a specified number of contiguous rows or columns from either the start or the end of an array, then spills the result automatically as the source changes. It’s the simplest way to build Top‑N lists, “Latest N” panels, preview tables, and compact chart feeds without volatile OFFSET or complex INDEX logic. The syntax is =TAKE(array, rows, [columns]). Positive values take from the start; negative values take from the end; omitting rows or columns means “take all” in that dimension. Microsoft’s documentation also notes that TAKE returns #CALC! when rows or columns is 0 and #NUM when the resulting spill would exceed sheet limits.

Read More

WRAPCOLS converts a single row or single column into a neat, multi‑column grid by filling down first, then moving right. It’s the column‑oriented sibling of WRAPROWS. If a vector is too long to present in one strip, WRAPCOLS reshapes it into a compact block that updates automatically as the source changes. The syntax is =WRAPCOLS(vector, wrap_count, [pad_with]). The vector is a one‑dimensional range or array. The wrap_count sets how many items appear in each output column before a new column begins. The optional pad_with fills any empty cells when the last column isn’t full; by default, Excel pads with #N/A so you can see boundaries during build‑out. Microsoft’s reference describes the behavior crisply and notes this is a dynamic array function available in Microsoft 365 and Excel for the web.

Read More

WRAPROWS takes a one‑dimensional list and reshapes it into a two‑dimensional array by wrapping values across rows after a set count. If a column or row is too long for presentation, WRAPROWS converts it into a compact grid without helper formulas or manual copy‑paste. The syntax is =WRAPROWS(vector, wrap_count, [pad_with]). The vector is the single row or single column to reshape. The wrap_count sets how many items appear in each output row before a new row begins. The optional pad_with fills any empty cells in the last row when the list length is not a perfect multiple of the wrap size. Microsoft’s function reference describes WRAPROWS succinctly and emphasizes that it is part of the modern dynamic array family in Excel for Microsoft 365 and the web. It returns a spill range that updates as the source vector grows or shrinks, which makes it ideal for dashboards, catalogs, menus, and side‑by‑side comparison layouts that need predictable dimensions from variable lists.

Read More

The VSTACK function combines multiple ranges or arrays by stacking them one beneath another, returning a single dynamic spill that expands or contracts as the sources change. If HSTACK builds side‑by‑side panels, VSTACK is its vertical counterpart for building long, tidy tables from separate blocks. The syntax is straightforward: =VSTACK(array1, [array2], …). Each subsequent array is appended to the bottom of the previous one, creating one continuous spill range that can be formatted, charted, or fed into downstream formulas without copy‑paste or helper sheets.

Read More

The HSTACK function appends ranges and arrays horizontally in one step, returning a single dynamic spill range that expands or contracts as sources change. If VSTACK is the vertical stacker, HSTACK is its horizontal twin, designed to line up multiple lists or tables next to each other without helper columns, copy‑paste, or manual resizing. The syntax is simple: =HSTACK(array1, [array2], …). Each subsequent array is placed to the right of the previous one, and the result spills into as many columns as needed. Microsoft’s function reference notes that HSTACK’s row count equals the maximum number of rows among the inputs, while the column count equals the sum of all input columns, with shorter arrays padded to match the tallest input. This behavior makes it ideal for consolidating period columns, stitching lookup results beside IDs, or building side‑by‑side comparison panels that stay in sync with source changes.

Read More

The EXPAND function lets Excel 365 users resize a returned array to exact dimensions and fill added cells with a value of choice. Think of EXPAND as the missing piece when building report‑ready ranges from dynamic arrays. It takes a source array, returns a version with the exact number of rows and columns specified, and pads any new cells with either a custom value or the default error. This makes it ideal for standardizing array sizes for charts, dashboards, and exports. According to Microsoft’s documentation, the syntax is =EXPAND(array, rows, [columns], [pad_with]) and it will return #VALUE if rows or columns are smaller than the source, and #N/A in padded cells if pad_with is omitted. It’s part of the dynamic array family and available to Microsoft 365 users on Windows, Mac, and the web as of recent builds.

Read More