The FLATTEN function in Google Sheets is a powerful tool that allows users to transform data from multiple ranges into a single column. This is particularly useful for creating a more manageable view of data, especially when you want to analyze or manipulate it further.

Syntax

FLATTEN(range1, [range2, ...])

• range1: The first range to flatten, which can include multiple rows and columns.
• range2: Optional additional ranges that you want to include in the flattening process.

Example #1

FLATTEN(A1:B3)

This function call takes the values from the range A1 to B3 and consolidates them into a single column. For instance, if A1 to B3 contains {1, 2, 3; 4, 5, 6}, the result would be: 1, 2, 3, 4, 5, 6.

Example #2

FLATTEN({D1:D3, E1:E3})

Here, the function takes values from two separate ranges, D1 to D3 and E1 to E3. If D1 to D3 has values {10; 20; 30} and E1 to E3 has {40; 50; 60}, the output will be: 10, 20, 30, 40, 50, 60.

Example #3

FLATTEN(F1:F5)

This example flattens a vertical range from F1 to F5. Assuming these cells contain values {A; B; C; D; E}, the result will appear as A, B, C, D, E.

Error handling

• REF!: This error occurs when a range reference is invalid, indicating that the specified cell or range is misplaced or deleted.
• VALUE!: This error appears when the function is given a non-numeric input or incorrect data type, often indicating mismatched parameter expectations.
• ERROR!: Often indicates a flaw in the formula structure, suggesting that the syntax used is incorrect or incompatible with the provided ranges.

Conclusion

The FLATTEN function is an invaluable feature in Google Sheets, simplifying data management by enabling users to efficiently combine multiple ranges into a single column. Its straightforward syntax and flexibility make it essential for anyone looking to streamline their data analysis process.