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