Using the EXPAND Function to expand an array

Dec 22, 2022 | Excel Hints and Tips

The EXPAND Function expands an array to a specific number of rows and columns.  The expanded array can be padded with any character you require.

At the time of writing this function is only available to some Microsoft 365 Insider users but is likely to roll out to others over the coming months.

Structure

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

Array – The array to expand

Rows – The number of rows in the expanded array.  Note that this is the total number of rows not the additional rows

Columns – The number of columns in the expanded array.  Note that this is the total number of columns not the additional columns

Pad with – The value to pad with.  If this is left blank the extra cells will pad with #N/A

NOTE: If the number of rows is less than the rows in the array the result will show as #VALUE.  If the number of columns is less than the columns in the array the result will show as #VALUE.  Excel returns #NUM when the array is too large. If pad with is left blank then #N/A will be returned.

Example 1

This example expands the 3 x 3 grid of numbers to 4 x 6.  The pad with part of the function is left blank which forces the extra cells to be filled with #N/A

Expand example 1 image

Example 2

In this second example the 3 x 3 grid of numbers also expands to 4 x 6.  The pad with part of the function forces the extra cells to be filled with ‘-‘ on this occasion.

Expand example 2 image

Note that there have been a huge number of functions rolled out in Microsoft 365 over the last few months.  These include TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, VSTACK, HSTACK, TEXTSPLIT, TEXTBEFORE, TEXTAFTER and TEXT JOIN.  Although everything you can do with these new functions could have been done before they are providing simpler solutions to problems that Excel users have on a day to day basis.  Why not check out my other blogs on all these functions.

Further Reading

If you’ve enjoyed reading how to use the EXPAND Function to expand an array in Excel, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.