Syntax
=FLATTEN(range1, [range2], ...)Arguments
| Argument | Required | Description |
|---|---|---|
range1 | Required | A range or array |
range2 | Optional | More ranges or arrays |
How to use FLATTEN
When FLATTEN returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
FLATTEN takes 1 required argument and 1 optional argument, and accepts more values in the same pattern. Type =FLATTEN( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=FLATTEN({1,2;3,4})Result Spilled result
| A | |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
=FLATTEN({1,2}, 3)Result Spilled result
| A | |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
Related array functions
- CHOOSEROWSReturns the given rows of an array (negative numbers count from the end).
- DROPRemoves a number of rows or columns from the start (positive) or end (negative) of an array.
- EXPANDExpands an array to the given number of rows and columns, filling new cells with a pad value.
- FILTERReturns the rows (or columns) of a range or array that meet a condition.
- GROUPBYGroups rows by the values of one or more columns and aggregates the values of each group.
- HSTACKPlaces arrays side by side; shorter columns are padded with #N/A.
- MAKEARRAYBuilds an array of the given size by calling a LAMBDA with each row and column number.
- MAPApplies a LAMBDA to each value of one or more arrays and returns the results.
- PERCENTOFReturns the sum of a subset of values as a fraction of the sum of all values.