Check Sheet Logo
Check Sheet

FLATTEN

The FLATTEN formula 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.

Usage

Use the FLATTEN formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:

=FLATTEN(range1,[range2,...])
Parameters:
  1. range1 (required):
    The first range or array to flatten.
  2. range2,... (optional):
    Additional ranges or arrays to flatten.

Examples

Here are a few example use cases that explain how to use the FLATTEN formula 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 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 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.

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 comma-separated 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.