EXPAND

EXPAND(array, rows [,columns] [,pad_with])

Returns the array expanded or padded to specific dimensions.

array	The array to expand.
rows	The number of rows in the expanded array.
columns	(Optional) The number of columns in the expanded array.
pad_with	(Optional) The value with which to pad.

REMARKS

* This function was added in Microsoft 365.
* This function can create a Dynamic Array Formula.
* If "rows" is left blank, then the rows will not be expanded.
* If "rows" < the number of rows in the array, then #VALUE! is returned. Example 4.
* If "columns" is left blank, then the columns will not be expanded.
* If "columns" < the number of columns in the array, then #VALUE! is returned.
* If "pad_with" is left blank, then #N/A is used.
* If the array is too large, then #NUM! is returned.
* You can use the CHOOSECOLS function to return the array with just a specific number of columns.
* You can use the CHOOSEROWS function to return the array with just a specific number of rows.
* You can use the DROP function to return the array without certain rows or columns.
* You can use the TAKE function to return the intersection of specific rows and columns in an array.
* This function was first released in March 2022.
* For the Microsoft documentation refer to support.microsoft.com

	A	B	C	D
1	=EXPAND(C1,1) = {1}		1	2
2	=EXPAND(C1:D1,1) = {1,2}		3	4
3	=EXPAND(C1:D1,1,2) = {1,2}
4	=EXPAND(C1:C2,1,1) = #VALUE!
5	=EXPAND(C1:C2,2,1) = {1 ; 3}
6	=EXPAND(C1:C2,2,2) = {1,#N/A ; 3,#N/A}
7	=EXPAND(C1:D2,3,3) = {1,2,#N/A ; 3,4,#N/A ; #N/A,#N/A,#N/A}
8	=EXPAND(C1:D2,3,3,"-") = {1,2,"-" ; 3,4,"-" ; "-","-","-"}

1 - What is the array "C1" after expanding it to 1 row. No expansion because the array has 1 row.
2 - What is the array "C1:D1" after expanding it to 1 row. No expansion because the array has 1 row and 2 columns.
3 - What is the array "C1:D1" after expanding it to 1 row and 2 columns. No expansion because the array has 1 row and 2 columns.
4 - What is the array "C1:C2" after expanding it to 1 row and 1 column. Cannot expand this to have 1 row and 1 column because the array has 2 rows.
5 - What is the array "C1:C2" after expanding it to 2 rows and 1 column. No expansion because the array has 2 rows and 1 column.
6 - What is the array "C1:C2" after expanding it to 2 rows and 2 columns. Expanded to a 2x2 because the array has 2 rows and 1 column.
7 - What is the array "C1:D2" after expanding it to 3 rows and 3 columns. Expanded to a 3x3 because the array has 2 rows and 2 columns.
8 - What is the array "C1:D2" after expanding it to 3 rows and 3 columns, when "pad_with" = "-". Expanded to a 3x3 because the array has 2 rows and 2 columns.