How to Use the FLATTEN Function in Google Sheets

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:

FLATTEN function flattens a range by scanning row by row

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:

Flattening individual ranges

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:

Using the UNIQUE function in a 2D array

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.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. This example gives a single-cell formula (without using Google Apps Script) to unpivot a matrix by using the FLATTEN function.

    Unpivot a pivot table data using Flatten function in Google Sheets

    Honestly speaking, I don’t think it can be further simplified.

    Thanks a ton for introducing the FLATTEN function.

    • 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!

  2. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.