A to Z of Excel Functions: The EXPAND Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the EXPAND function.

The EXPAND function

The EXPAND function expands (or pads) an array to specified row and column dimensions.  It has the following syntax:  

The EXPAND function has the following arguments:

• array: this is required and represents the selected array to be expanded
• rows: this is also required and denotes the number of rows in the expanded array.  If this argument is missing (not bad for a required argument!), rows will not be expanded
• columns: this is optional and denotes the number of columns in the expanded array.  Again, should columns not be specified, this dimension will not be expanded
• pad with: this is an optional value with which to pad.  The default is N/A.

It should be noted that:

• if rows isn’t provided or is empty, the default value is the number of rows in the array argument (as aforementioned)
• if columns isn’t provided or is empty, the default value is the number of columns in the array argument
• if pad with is not provided and array has one value for that dimension, then that value is used.  This operation is commonly referred to as array “broadcasting”; however, this does not appear to work presently
• Excel returns an #VALUE! error when the rows or columns argument is less than the rows or columns in the array argument
• Excel returns an #N/A error when pad with is greater than a single column or row
• Excel returns an #NUM! when array is too large.

Please see my examples below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.