Syntax
=EXPAND(array, rows, [columns], [pad_with])Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range or array |
rows | Required | Rows in the result |
columns | Optional | Columns in the result |
pad_with | Optional | The value for new cells (default #N/A) |
How to use EXPAND
When EXPAND returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
EXPAND takes 2 required arguments and 2 optional arguments. Type =EXPAND( 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.
=EXPAND({1,2}, 2)Result Spilled result
| A | B | |
|---|---|---|
| 1 | 1 | 2 |
| 2 | #N/A | #N/A |
=EXPAND({1}, 1, 3, 0)Result Spilled result
| A | B | C | |
|---|---|---|---|
| 1 | 1 | 0 | 0 |
=EXPAND({1;2}, 1)Result Related array functions
- BYROWApplies a LAMBDA to each row of an array and returns one result per row.
- CHOOSECOLSReturns the given columns of an array (negative numbers count from the end).
- 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.
- FILTERReturns the rows (or columns) of a range or array that meet a condition.
- FLATTENPuts the values of one or more ranges into a single column, reading row by row.
- 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.