FLATTEN
TheFLATTEN
formula in Google Sheets takes in one or more arrays and returns a flattened version of those arrays. It is commonly used to collapse multidimensional arrays into a single column or row.
 How to use
FLATTEN
formula?  Examples of using
FLATTEN
formula FLATTEN
formula not working? Similar formulas to
FLATTEN
Usage
Use the FLATTEN
formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
 range1 (required):
The first range or array to flatten.  range2,... (optional):
Additional ranges or arrays to flatten.
Examples
Here are a few example use cases that explain how to use theFLATTEN
formula in Google Sheets.
Flattening a multidimensional array
If you have a multidimensional array that you want to collapse into a single column or row, you can use the FLATTEN
formula to accomplish this.
Concatenating multiple ranges
You can use FLATTEN
to concatenate multiple ranges into a single column or row. Simply provide multiple ranges as arguments to FLATTEN
.
Converting a range to a single column or row
If you want to convert a range into a single column or row, you can use FLATTEN
on that range.
Common Mistakes
FLATTEN
not working? Here are some common mistakes people make when using the FLATTEN
Google Sheets Formula:
Incorrect range format
One common mistake is using the wrong format for the range parameter. It should be in the format of 'Sheet1!A1:B5' or 'A1:B5' instead of just 'A1B5'.
Incorrect number of arguments
Another common mistake is providing an incorrect number of arguments. The FLATTEN formula requires at least one range parameter, but can accept multiple. Make sure the number of ranges provided matches the number of arguments in the formula.
Range contains empty cells
If any of the cells in the range provided are empty, the FLATTEN formula will not work correctly. Make sure all cells in the range contain data.
Range includes nonconsecutive columns or rows
The FLATTEN formula can only be used with consecutive columns or rows. If the range includes nonconsecutive columns or rows, the formula will not work correctly.
Range includes merged cells
If the range includes merged cells, the FLATTEN formula will not work correctly. Unmerge any merged cells within the range before using the formula.
Related Formulas
The following functions are similar to FLATTEN
or are often used with it in a formula:

TRANSPOSE
The
TRANSPOSE
formula in Google Sheets allows you to flip a table of data by turning rows into columns and columns into rows. This can be useful for reorganizing data or preparing it for use in other formulas. 
SPLIT
The
SPLIT
formula in Google Sheets is used to split a text string into separate cells based on a delimiter. It is commonly used when you have a large text string that you need to break down into smaller parts. The resulting parts are placed into individual cells, making it easier to work with and analyze the data. 
JOIN
The
JOIN
formula concatenates the elements of one or more arrays using a specified delimiter string. It is commonly used to combine text strings or to create commaseparated lists of values. 
ARRAYFORMULA
The
ARRAYFORMULA
formula in Google Sheets allows for the application of a formula to an entire column or range of data, without needing to manually copy the formula to each cell. It is most commonly used to simplify and automate complex calculations across large data sets.
Learn More
You can learn more about the FLATTEN
Google Sheets function on Google Support.