Excel EXPAND function: To grow array to specified number of rows and columns
This tutorial explains a new Excel 365 function that helps you make an array bigger to the size you want by filling it with a chosen value.
Do you need to expand an array to a certain number of rows or columns so all similar arrays in your worksheet are the same size? In Excel 365, you can do this with just one simple formula. Say hello to the new EXPAND function, which lets you grow an array to any size you need!
Table of Contents
Excel EXPAND function
The EXPAND function in Excel is used to make an array (a group of cells) bigger by adding more rows and columns. You can choose what value to put in the extra cells.
Array (required) – the original array.
Rows (optional) – the number of rows in the returned array. If omitted, new rows are not added, and the columns argument must be set.
Columns (optional) – the number of columns in the returned array. If omitted, new columns are not added, and the rows argument must be set.
Pad_with – the value to fill new cells with. If omitted, defaults to #N/A.
EXPAND function availability
Currently, the EXPAND function is available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.
How to use the EXPAND function in Excel
To make a group of values (an array) bigger in Excel, use the EXPAND formula like this:
- Array: This is the starting group of cells or values. You can type them directly or use a formula that gives values.
- Rows and Columns: Type numbers bigger than the current number of rows or columns in your array. These numbers tell Excel the total size you want — not how many extra rows or columns to add. You must enter at least one (rows or columns). If you leave one out, Excel will keep the original size for that part.
- Pad_with: This is the value Excel will use to fill the new cells. If you want to fill with text, put it in quotes (like “Hello”). If it’s a number, just type it (like 5). If you leave it out, Excel will fill new cells with #N/A.
The EXPAND function is a dynamic array function, which means you only need to type the formula in one cell. Excel will automatically fill in the extra rows and columns based on what you set in the formula.
For example, to make the array in C6:D13 expand to 12 rows and 3 columns, use this formula:
=EXPAND(C6:D13, 12, 3)
Because we didn’t set a pad_with value, Excel will fill the extra cells with #N/A.
To fill the new cells with a different value, simply add it to the formula. For example, to use a hyphen (-) instead of #N/A, write:
=EXPAND(C6:D13, 12, 3, “-“)
Below you will find a few more examples of using the EXPAND function in Excel to grow an array in a specific direction.
Expand array to a certain number of rows
If you want to make an array longer by setting the number of rows, you only need to fill in the rows part of the formula. You can leave the columns part empty — Excel will keep the original number of columns.
For example, if you want the array from C6:D13 to have a total of 12 rows, use this formula:
Since you don’t want to change the columns, put a comma after the rows argument. Then, after the comma, add the padding value (in this case, a hyphen) for the new cells.
Expand array to a certain number of columns
To add extra columns to an array, only
specify the columns argument and leave the rows argument empty.
For example, if you want the array in A4:C15
to have a total of 4 columns, use this formula:
Here, leaving the rows argument blank (with a comma) tells Excel to keep the same number of rows while adding columns. The hyphen (“-“) fills the new cells.
That’s how to use the EXPAND function in Excel to extend an array to as many rows and columns as your business logic requires. I thank you for reading and hope to see you on our blog next week!
