Quick Navigation

EXPAND Function

Excel 365, Excel 2021

Summary

The EXPAND function transforms arrays by extending them to specified dimensions, automatically filling new areas with customizable padding values. This dynamic array function is essential for reshaping data structures in modern Excel workflows.

Syntax

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

Parameters

Parameter	Type	Required	Description
array	`Array`	Yes	The source array or range to expand
rows	`Number`	Yes	Desired row count for expanded array
columns	`Number`	No	Desired column count (optional) for expanded array
pad_with	`Any`	No	Padding value for new cells (optional)

Using the EXPAND Function

EXPAND revolutionizes array manipulation by allowing precise control over output dimensions. Use it to standardize array sizes for consistent dashboard displays, prepare data for machine learning models, or create uniform report structures from variable-sized datasets.

Common EXPAND Examples

Expand 2x2 Array to 3x3 with Default Padding

=EXPAND(A1:B2,3,3)

Transforms {{1,2},{3,4}} into 3x3 array with #N/A in new positions

Pad Single Cell to Full Grid

=EXPAND(A1,5,4,"0")

Expands one value into 5x4 grid filled with zeros

Extend Sales Data for Monthly Report

=EXPAND(Sales[Jan:Mar],12,1)

Expands 3-month sales data to full year with #N/A padding

Frequently Asked Questions

EXPAND returns #VALUE! error. Target dimensions must be equal or larger than source.

No, EXPAND only expands or maintains size. Use TAKE or DROP functions to reduce dimensions.

#N/A appears in expanded areas unless pad_with argument specifies otherwise.

Common Errors and Solutions

#VALUE!

Cause: Target rows/columns smaller than source array

Solution: Use dimensions equal to or larger than original array

#N/A in results

Cause: Default padding value appearing in expanded areas

Solution: Specify custom pad_with value like 0 or ""

#NUM!

Cause: Array too large for Excel memory limits

Solution: Use smaller source array or TAKE function first

Notes

Only available in Excel 365 and Excel 2021
Works with dynamic arrays and spilled ranges
Cannot reduce array size (use TAKE/DROP instead)
Perfect companion to TOCOL, TOROW, TAKE, DROP functions

Compatibility

Available in: Excel 365, Excel 2021

Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021