How does FLATTEN differ from TRANSPOSE?

FLATTEN converts any 2D range into a single column (removing blanks), while TRANSPOSE swaps rows and columns—turning rows into columns. Use FLATTEN to linearize data, TRANSPOSE to rotate it.

Does FLATTEN preserve the order of values?

Yes. FLATTEN reads left-to-right within each row, top-to-bottom across rows. When multiple ranges are passed, they're concatenated in the order you list them. Empty cells are removed, but non-empty values keep their relative order.

Can FLATTEN work with multiple sheets?

Yes. Use sheet references like =FLATTEN(Sheet1!A2:B6, Sheet2!A2:B6) to flatten data from different sheets and combine them into one column.

What's the maximum size FLATTEN can handle?

Google Sheets arrays can contain up to 10 million cells. However, spill output is limited by available sheet space—if you flatten a large range, ensure enough empty cells exist below to hold the result without triggering #SPILL!.

FLATTEN function

FLATTEN converts a 2D range into a single column, automatically removing empty cells and combining multiple ranges vertically.

=FLATTEN(range1, ...)

Generate a FLATTEN formula

Describe what you need. The generator will reach for FLATTEN where FLATTEN is the right tool, and tell you when it is not.

Arguments

How FLATTEN reads its arguments

Argument	Required	Description
range1	Required	The primary range or array to flatten. Can be 1D or 2D; values are read row-by-row from left to right, top to bottom.
...	Repeating	Optional additional ranges to append vertically to the result. Multiple ranges are concatenated in the order specified, with range1 first.

Returns

A single-column array containing all non-empty values from the input range(s), read row-by-row, left-to-right.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Flatten customer names and plans into a single list

Customer	Plan
Acme Corp	Pro
TechStart	Basic
Global Inc	Enterprise
StartupX	Pro
SmallBiz	Basic

=FLATTEN(A2:B6)

Result: Acme CorpProTechStartBasicGlobal IncEnterpriseStartupXProSmallBizBasic

FLATTEN reads the 2×5 range left-to-right, top-to-bottom: row 1 gives Acme Corp, Pro; row 2 gives TechStart, Basic, etc. This converts multiple columns into a single-column array with 10 values, essential for functions that expect columnar input.

2. Merge signup and churn dates from separate columns

Signup Date	Churn Date
2023-01-15
2023-03-20	2024-08-10
2022-11-01
2024-01-10
2024-02-14

=FLATTEN(D2:D6, E2:E6)

Result: 2023-01-152023-03-202022-11-012024-01-102024-02-142024-08-10

FLATTEN stacks multiple ranges vertically: first D2:D6 (5 signup dates), then E2:E6, while automatically removing the 4 empty churn-date cells. The result contains 6 dates total in one column—all startup dates plus one churn date.

3. Create a single column of billing values for reporting

MRR

2500

500

5000

2500

500

=FLATTEN(C2:C6)

Result: 250050050002500500

Even though C2:C6 is already a single column, FLATTEN ensures proper array output for dependent formulas. This is essential when piping billing data into SUMPRODUCT, QUERY, or functions that require array input rather than range references.

Common errors

Which FLATTEN error are you seeing?

Error	Why it happens	How to fix it
#REF!	You referenced a range that no longer exists—for example, =FLATTEN(D2:D6, DeletedSheet!E2:E6) after the sheet was removed or a column was deleted.	Verify both the sheet name and cell range still exist. Correct any typos in sheet names or update references to the new location. Use the Name Manager to check linked ranges.
#VALUE!	You passed an argument that is not a valid range, such as =FLATTEN("A2:B6") using quoted text instead of a cell reference, or a data type FLATTEN cannot process.	Remove quotes around range references: write =FLATTEN(A2:B6) instead of =FLATTEN("A2:B6"). Ensure all arguments are direct range references, not text strings or unsupported data types.
#SPILL!	The flattened result is too large to fit in available cells below the formula, or the cells are blocked by protection, merges, or existing data.	Clear or delete data below the formula to make room for the output. Unprotect the sheet if cells are locked. Alternatively, use a narrower input range to reduce the result size.

Tips and when to use something else

FLATTEN removes empty cells automatically—pass any range with blanks and only non-empty values appear in the result. Useful for cleaning data without FILTER.
Combine multiple ranges quickly with =FLATTEN(range1, range2, range3). Ranges are stacked vertically in order, so all of range1 appears first, then range2, etc.
For filtering by criteria (e.g., 'flatten only MRR > 2000'), use QUERY instead—FLATTEN has no filtering logic and only removes blanks. QUERY lets you add WHERE conditions.
FLATTEN outputs a spill array in a single cell. Never place it adjacent to other formulas or data, as the array will overflow and cause #SPILL! errors. Leave empty cells below.

Related functions

QUERYQuery a dataset using SQL-like syntax to filter, sort, and extract specific rows and columns that match your conditions.TRANSPOSETRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.ARRAYFORMULAARRAYFORMULA applies a single formula to an entire array or range, automatically filling results into multiple cells.CHOOSEROWSCHOOSEROWS returns specific rows from a given array based on one or more row numbers, spilling the selected rows into adjacent cells.

Frequently asked questions

How does FLATTEN differ from TRANSPOSE?: FLATTEN converts any 2D range into a single column (removing blanks), while TRANSPOSE swaps rows and columns—turning rows into columns. Use FLATTEN to linearize data, TRANSPOSE to rotate it.
Does FLATTEN preserve the order of values?: Yes. FLATTEN reads left-to-right within each row, top-to-bottom across rows. When multiple ranges are passed, they're concatenated in the order you list them. Empty cells are removed, but non-empty values keep their relative order.
Can FLATTEN work with multiple sheets?: Yes. Use sheet references like =FLATTEN(Sheet1!A2:B6, Sheet2!A2:B6) to flatten data from different sheets and combine them into one column.
What's the maximum size FLATTEN can handle?: Google Sheets arrays can contain up to 10 million cells. However, spill output is limited by available sheet space—if you flatten a large range, ensure enough empty cells exist below to hold the result without triggering #SPILL!.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17