How to replicate Sheets’ FLATTEN function in Excel
Wednesday’s announcement of 14 new Excel functions was welcoming news.
However, despite switching to the BETA Channel yesterday, I am not one of the lucky ones who have access to the new batch yet.
Nevertheless, I was particularly drawn to the TOCOL function, which appears to be Excel’s answer to Google Sheets’ FLATTEN.
FLATTEN flattens all the values from one or more ranges into a single column.
There have been numerous occasions over the years where I’ve wanted to do this in Excel but haven’t been able to (easily) due to the lack of native function. Thankfully, in more recent times, it’s been possible to create a LAMBDA that largely replicates it.
It’s going to be a while before TOCOL has widespread availability, so in the meantime, use FLATTEN!
Function Syntax
FLATTEN ( array )• array — the array or reference to return as a column
Example
Cell D3 uses FLATTEN to reference the Data table values, which contains the numbers 1–10 spelt out. E2 shows the function’s composition, and each part is broken down in columns G to L.
The following named values are stored inside LET:
• rows — 5 rows in the Data table.
• columns — 2 columns in the Data table.
• sequence — multiplies the Data table rows by its columns to create a sequence of numbers from 1 to 10.
• quotient — QUOTIENT returns the integer portion of a division. In numerator, 1 is subtracted from each of the sequence figures. These are then divided by 2 (columns) in denominator and 1 is added to generate an array with two of each row number, e.g. 1, 1; 2, 2; 3, 3, etc.
• mod — MOD returns the remainder after 𝚗𝚞𝚖𝚋𝚎𝚛 is divided by divisor. The calculation is the same as quotient, except the result consists of an alternating 1 and 2.
With the named values defined, the main calculation can now be constructed (column L).
Nested inside the INDEX array argument is an IF statement ensuring any blank values in the Data table are returned as empty strings.
The quotient and mod values in columns J and K represent row_num and [column_name], respectively. This pattern of numbers allows the correct values to be grabbed from the Data table and displayed in a single column.
Limitations
Unfortunately, certain disadvantages do exist compared to Google’s FLATTEN. Non-contiguous ranges are not permitted so all columns must be adjacent to each other. That also means different sized ranges are forbidden.
From my understanding, TOCOL does not have these limitations.
