FLATTENformula in Google Sheets takes in one or more arrays and returns a flattened version of those arrays. It is commonly used to collapse multi-dimensional arrays into a single column or row.
- How to use
- Examples of using
FLATTENformula not working?
- Similar formulas to
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.
ExamplesHere are a few example use cases that explain how to use the
FLATTENformula in Google Sheets.
Flattening a multi-dimensional array
If you have a multi-dimensional 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
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.
FLATTENnot working? Here are some common mistakes people make when using the
FLATTENGoogle 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 non-consecutive columns or rows
The FLATTEN formula can only be used with consecutive columns or rows. If the range includes non-consecutive 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.
The following functions are similar to
FLATTEN or are often used with it in a formula:
TRANSPOSEformula 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.
SPLITformula 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.
JOINformula concatenates the elements of one or more arrays using a specified delimiter string. It is commonly used to combine text strings or to create comma-separated lists of values.
ARRAYFORMULAformula 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.
You can learn more about the
FLATTEN Google Sheets function on Google Support.