In this post, you’ll learn how to effectively use the FLATTEN function in Google Sheets to flatten a range. Additionally, I’ll explain how to “unflatten” a previously flattened range using the WRAPROWS function.
Originally, FLATTEN was an undocumented function for quite some time. However, it is now part of the native functions in Google Sheets.
One important thing to note is that you can now use the TOCOL function, which offers additional features beyond FLATTEN. In this post, I’ll also compare FLATTEN with TOCOL, so you can choose the best option for your use case.
Let’s begin with how to use the FLATTEN function in Google Sheets.
FLATTEN Function: Syntax and Arguments
The syntax of the FLATTEN function is:
=FLATTEN(range1, [range2, …])
Arguments:
- range1 – The first range to flatten.
- range2, … – (Optional) Additional ranges to flatten.
Purpose:
The FLATTEN function takes the input ranges and flattens them into a single column by scanning row-by-row. It processes the first row, then the second, and so on.
Examples of the FLATTEN Function and Comparison with TOCOL
In the example below, the array A1:D is flattened into rows in F1:F.
=FLATTEN(A1:D)
Screenshot #1:
The equivalent TOCOL formula for this is:
=TOCOL(A1:D)
The advantage of using TOCOL here is that it allows you to remove empty cells and errors from the flattened data. To do this, you can use the formula:
=TOCOL(A1:D, 3)
This makes TOCOL more versatile for many situations.
If you want to flatten specific ranges individually, like in the formula below:
=FLATTEN(A1:A3, B1:B3, C1:C3, D1:D3)
Screenshot #2:
Each range will be stacked one after another in the output. The TOCOL equivalent would be:
=TOCOL(A1:D3, ,TRUE)
This version of TOCOL flattens the array by column instead of row.
Real-Life Use Case: Unique Scattered Values and Summary
You can use the FLATTEN function in various real-life scenarios in Google Sheets. Here’s an example where we use FLATTEN to gather unique or summarize scattered values in a sheet.
For instance, consider the berth preferences of passengers. To find the unique preferences and a summary:
The usual UNIQUE formula, like this:
=UNIQUE(B2:D)
won’t give the desired result because it works across columns. Instead, you can use:
=UNIQUE(FLATTEN(B2:D))
This formula in cell F2 will provide the unique values from the scattered data.
Screenshot #3:
To generate a summary of these preferences, use the following QUERY formula in cell H2:
=QUERY(FLATTEN(B2:D), "SELECT Col1, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL COUNT(Col1) ''")
This query will count the occurrences of each unique value in the flattened data.
How to Unflatten a Flattened Range in Google Sheets
To “unflatten” a previously flattened range, you can use the WRAPROWS function.
Referring to Screenshot #1, let’s take the flattened values in the range F1:F. To unflatten this data, we need to determine how many values should be placed in each row.
Based on the original dataset in A1:D, we know the range contains 4 columns, meaning each row contains 4 values.
Therefore, we can use the WRAPROWS function with a wrap count of 4 to unflatten the data. Here’s the formula in H1:
=WRAPROWS(F1:F12, 4)
This formula will restore the flattened data back into its original 4-column format.
Now FLATTEN is an official function!!!
This example gives a single-cell formula (without using Google Apps Script) to unpivot a matrix by using the FLATTEN function.
Honestly speaking, I don’t think it can be further simplified.
Thanks a ton for introducing the FLATTEN function.
Hi, S k srivastava,
Thanks for your feedback and also sharing a cool formula with other readers.
Cheers!
That is a FANTASTIC formula. I’ve been using a script from GitHub called UNPIVOT for years. It works very well but this is built right into Sheets. I’m going to put it to use and see how it works in battle. Great find! Thanks!
It is really incredible to know that there are formulas that are valid in Google Sheets but are not included in the list.
This FLATTEN immediately gave me an idea to use it to unpivot a matrix. And it worked !!!
Thanx and regards.