A to Z of Excel Functions: The EXPAND Function
18 April 2022
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the EXPAND function.
The EXPAND function
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/a-to-z/309/expand1.jpg/b848feb7996433f57e6dcfacb5bcc127.jpg)
The EXPAND function expands (or pads) an array to specified row and column dimensions. It has the following syntax:
EXPAND(array, rows, [columns], [pad with])
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/a-to-z/309/expand2.png/e6b301e3bfe7eb4eb3c118c8620ef889.jpg)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/a-to-z/309/expand3.png/ce93e9cba6a96882863c9a630e8e83b7.jpg)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/a-to-z/309/expand4.png/551ec7a7f8780954b8a84f81635f26b4.jpg)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/a-to-z/309/expand5.png/3d3a3a3194f984b54b569981ce248478.jpg)
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.