Excel 2021: TOCOL and TOROW [UPDATED]

Question

📌 Note: This article was originally created by Lz., a valued member on Answers Support Community. It provided meaningful insights and proved helpful to many. We're recreating it here on their behalf to preserve its value and ensure continued access for others.

Intro.

As of this writing functions TOCOL and TOROW are only available in Excel 365 and Excel online/Web. Excel 2021 supports Dynamic Arrays so simulating these 2 functions is possible

There’s a limitation though. Most Excel functions accept parameter(s), something we can’t replicate here. This means our parameters must be "encapsulated" in our LET formula or referenced on the grid/sheet

Updated workbook supporting this article is available here (inc. examples and formulas embedded as plain text files)

Updated 2024-10-18: Re-written from scratch to reduce the calcs as much as possible + changed the FILTERing so this work exactly as the 365 function when there's no more rows/columns (empty array) => #CALC! error

1 - TOCOL and TOROW parameters

Both functions accept the same parameters:

array (required)

ignore (optional) – Default = 0

scan_by_column (optional) – Default = FALSE

As we can’t write/simulate a function the parameters must be provided in the LET formula (or referenced on the grid/sheet)

2 - About Blanks

The formulas behave exactly as TOCOL/TOROW in Excel 365/Web re. Blanks. A cell is Blank when it's really empty. In other words, cells that contain formulas that return single/double-quotes or have invisible/unprintable chars. won't be filtered out when the ignore parameter is set to 1 or 3