In this post, you can learn how to use the FLATTEN function in Google Sheets to flatten a range. Also, I have included the steps to unflatten a flattened range using the SKIPPING clause in QUERY.

As far as I know, both the FLATTEN function and the Query SKIPPING clause are not officially documented but work in Google Sheets.

Let’s begin with how to use the FLATTEN function in Google Sheets.

The better way to find whether a function is correctly spelled or available in Google Sheets is just by entering it in a cell. The function in question must be entered in the following manner.

=function_name()

If the function is misspelled or not available the result would be the #NAME? error. On the other hand, if the function is available, in most cases you will see the #N/A error.

You May Like: Different Error Types in Google Sheets and How to Correct It.

Some of the exceptions to the above are the functions ROW, COLUMN, TODAY, NOW, NA, FALSE, TRUE, and FALSE.

I am talking all these because even the function FLATTEN is not officially documented, or not available under the menu Insert > Function, it’s available in Google Sheets.

=flatten()

Key the above Flatten function in any cell in Google Sheets to see the #N/A? error, The tooltip (hover over the error) would say;

Wrong number of arguments to FLATTEN. Expected at least 1 arguments, but received 0 arguments.

Syntax and Arguments of the Flatten Function in Google Sheets

Since the function is not officially documented, the function argument is also not available in an official form. So here is my interpretation of the same.

Syntax: flatten(value1, [value2, ...])

Arguments:

value1 – The first value or range to consider for flatten.
value2, … – [ OPTIONAL ] – Additional values or ranges to consider for flatten.

Purpose:

The purpose of the FLATTEN function in Google Sheets is to flatten values in an array into separate rows in a column.

Example to the FLATTEN Function

In the below example, the array A1:D flattened to rows in F1:F.

Screenshot # 1:

Real Life Use (Unique Scattered Values and Summarize)

We can use this function to unique or summarize the scattered values in a sheet.

For example, see the berth preferences of passengers and unique preferences and summary.

Screenshot # 2:

The UNIQUE formula, i.e. =unique(B2:D), won’t yield the desired result. Instead, we can use it as =unique(flatten(B2:D)). The formula is in use in cell F2.

For summary, I have used the following QUERY in cell H2.

=query(
     flatten(B2:D),
     "Select Col1, count(Col1)
     where Col1 is not null
     group by Col1
     label count(Col1)''"
)

How to Unflatten a Flattened Range in Google Sheets?

We can use the QUERY function here.

See Screenshot # 1 above. The flattened values are in the array F1:F. Let’s see how to unflatten it using the QUERY.

Since there are numbers and text strings in column F, we can call it a mixed data type column. In such a column you must use the TO_TEXT function with QUERY. I’ll come to that later.

Earlier I’ve explained how to use the clause SKIPPING in QUERY. Didn’t see that? Then here it is – Import Every Nth Row in Google Sheets Using Query or Filter (Same File).

We are going to use that clause here.

Steps to Unflatten a Flattened Array in Google Sheets

From the dataset in A1:D (please refer to Screenshot # 1) you can understand that the range contains 4 columns.

If you only have the flattened range like the one in F1:F, you must analyze it to find the number of columns you want in the unflattened range in each column.

I’m considering it as 4. So we will skip 4 columns in QUERY. Now let’s begin.

Formula in cell H1:

=ArrayFormula(
    query(
      to_text(F1:F),"Skipping 4"
    )
)

I’ve wrapped the range F1:F with the TO_TEXT non-array function since the range contains mixed-type data. To make the range again an array I’ve used the ARRAYFORMULA function.

We need three more formulas as there are 4 columns and that will be in cells I1, J1, and K1. The changes in the formula in each cell are like this. Change the range F1:F in the formula to F2:F, F3:F, and F4:F respectively.

But if you replace F1:F in the above formula in cell H1 with indirect("F"&column(A1)&":F"), you can drag it to I1:K1.

=ArrayFormula(
     query(
        to_text(indirect("F"&column(A1)&":F")),"Skipping 4"
     )
)

Screenshot # 3:

Can you explain to us the Indirect part?

Yes! Here it is!

In the INDIRECT formula, the letter “F” represents the column F.

The cell reference A1 represents the row number. I mean, if the flattened values are in F5:F, not in F1:F, then A1 should be replaced by E1 as column(E1) would return 5.

That’s all about the use of the FLATTEN Function in Google Sheets. Enjoy!

Credit: https://support.google.com/docs/thread/33715001?hl=en

Resources:

1. A Simple Formula to Unpivot a Dataset in Google Sheets (Using FLATTEN).
2. How to Move Each Set of Rows to Columns in Google Sheets.