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.
Table of Contents
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.
- 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.
- 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.
- Add an open bracket, and choose your range.
- 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!

1 comment
It should be mentioned that FLATTEN() is an undocumented function discovered recently by a user (March 2020). https://support.google.com/docs/thread/33715001?hl=en It’s believed it is intended to remain “hidden” in the back end of the sheets programming, but there is no guarantee that the formula will continue to work indefinitely. Here is the demo sheet I made when it was discovered.
https://docs.google.com/spreadsheets/d/196NDPUZ-p2sPiiiYlYsJeHD6F_eJq7CWO_hP7rFqGpc/edit?usp=sharing
I am one of the discoverers and have spoken with Google Engineers about it. It’s not responsible for you to advertise it’s utility without mentioning that it might disappear.