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

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

1. 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!

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.

## How to Use the SUBSTITUTE Function in Google Sheets

The SUBSTITUTE Function in Google Sheets is useful for situations where you need to replace existing text with…

## How to Use DVAR Function in Google Sheets

The DVAR function in Google Sheets is useful if you need to estimate the variance of a population,…

There are several methods available that allow you to remove trailing and leading spaces in Google Sheets. Leading…

## How To Use INTRATE Function in Google Sheets

The INTRATE function in Google Sheets is useful when you need to calculate the effective interest rate of…