How to Use FLATTEN Function in Google Sheets

The FLATTEN function in Google Sheets is useful for creating a column that includes the numbers, characters, or strings in all cells within a given range.

What makes the FLATTEN function special is that it is an undocumented function in Google Sheets. Try it yourself – you will find that you cannot find it on the Google Sheets support page, or the dropdown list of functions. Despite that, it works in Google Sheets and is quite a helpful function, too.

It was a recent discovery that is speculated to function hidden as a back-end formula. Given the nature of the function, its functionality may cease at any time and may disappear from Google Sheets. You can view the documentation of its discovery here.

The FLATTEN function is pretty simple to use when you want to create a clean copy of all the items in a table.

Let’s take an example.

You have a spreadsheet that lists various classes and the students belonging to each class. You want a quick way to compile them all into one column.

How should we approach this problem?

Simple – the FLATTEN function just needs the input range, and it will list the items within the said range.

The Anatomy of the FLATTEN Function

The syntax (the way we write) the FLATTEN function is simple, and it’s as follows:

=FLATTEN(range)

Let’s break this down to understand the syntax of FLATTEN and each term meaning:

• = the equal sign is how we begin any function in Google Sheets.
• FLATTEN is our function. We need to add the range attribute for it to work properly.
• range is the only attribute of this function. It is the input range that we want to see listed as a single column.

Note that if your range has multiple rows and columns, it lists the cells in the final single column from left to right, and from top to bottom.

A Real Example of Using the FLATTEN Function

Let’s look at an example below to see how to use the FLATTEN function in Google Sheets.

List All The Elements of a Table as a Single Column

This is a simple problem. If left to your own devices, you would probably copy-paste these short columns into one long column, however, there is a function that lets you create a single column with all this data.

What you can do to avoid that is to use the FLATTEN function. This function reads the inserted array from left to right to create a coherent column, without doing the tedious process of copy-pasting any data together.

The way to input the array is through the cell range:

=FLATTEN(B3,D7)

As a result, we get one coherent column. Once you have this single column, you are free to sort and filter this data for a helpful manner, and even create a to-do list with it on Google Sheets!

This simple problem can be practiced. Use the link below to use our spreadsheet sample:

How to Use the FLATTEN Function in Google Sheets

Let’s look at an example to see how to use the FLATTEN function in Google Sheets.

1. To begin, click on the cell where you want your result to be. This turns it into the active cell. Make sure that the rest of the cells below the column are empty. Organize your spreadsheet accordingly.

2. Next, type the equal sign to start off the function. After that, type in the name of the function which is ‘FLATTEN’ – note that you will not see it in the function suggestion dropdown of Google Sheets.

3. Add an open bracket, and choose your range.

4. Close off the range with a closing bracket and hit Enter. Your new list should populate the column!

That’s all, good job! You can now use the FLATTEN function together with other numerous Google Sheets formulas to create effective and powerful spreadsheets!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

Subscribe