What happens if I expand an array to fewer rows than it currently has?

EXPAND returns a #NUM! error because it cannot reduce an array's size—it only enlarges. Use TAKE to truncate arrays to a smaller dimension instead.

Can I expand by columns only without changing the number of rows?

Yes. Omit the columns argument to keep the original width, or specify columns equal to the array's current width. Only the rows argument forces row expansion.

What's the default padding value if I don't specify pad_with?

EXPAND has no default padding value; if pad_with is omitted and expansion is needed, it returns #VALUE!. Always provide a pad_with argument, even an empty string, to avoid this error.

Can I expand both rows and columns at the same time?

Yes, EXPAND expands both dimensions simultaneously. Provide both rows and columns arguments; the new cells created in the expanded areas are filled with the pad_with value.

EXPAND function

EXPAND enlarges an array to specified rows and columns, padding newly created cells with an optional value to create uniform-sized result tables.

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

Generate a EXPAND formula

Describe what you need. The generator will reach for EXPAND where EXPAND is the right tool, and tell you when it is not.

Arguments

How EXPAND reads its arguments

Argument	Required	Description
array	Required	The array or range to expand; can contain numbers, text, dates, or any data type. The array height and width determine its starting size.
rows	Required	The target number of rows; must be a positive integer greater than or equal to the array's current height. If smaller, EXPAND returns an error.
columns	Optional	The target number of columns (optional); defaults to the array's original width if omitted. Must be a positive integer if provided.
pad_with	Optional	The value to fill newly created cells (optional); can be a number, text, date, or formula result. If omitted and expansion is needed, returns an error.

Returns

An array with the specified number of rows and columns, with original data in the top-left and padding filling the rest.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Expand quiz scores with dash padding

Alice Math Q1: 95

Alice Math Q2: 88

Bob Math Q1: 92

=EXPAND({95,88,92}, 4, 3, "—")

Result: [[95,88,92],["—","—","—"],["—","—","—"],["—","—","—"]]

The formula takes a 1×3 array containing three quiz scores and expands it to 4 rows and 3 columns. Rows 2–4 are filled with dash characters ("—") as padding, creating a template for additional data entry.

2. Expand student names to fill a 5-row roster

Alice

Bob

Charlie

=EXPAND({"Alice";"Bob";"Charlie"}, 5, 1, "(empty)")

Result: [["Alice"],["Bob"],["Charlie"],["(empty)"],["(empty)"]]

The array contains three student names arranged vertically. EXPAND extends it to 5 rows while keeping 1 column. Rows 4–5 are padded with "(empty)" to indicate available slots for additional students.

3. Expand a score matrix and pad with zeros

Alice Math: 95	Alice Math: 88
Bob Math: 92	Bob Math: 95
Charlie English: 87	Charlie English: 91

=EXPAND({95,88;92,95;87,91}, 4, 4, 0)

Result: [[95,88,0,0],[92,95,0,0],[87,91,0,0],[0,0,0,0]]

A 3×2 matrix of student scores expands to 4 rows and 4 columns. The original data occupies the top-left corner, and all newly created cells in columns 3–4 and row 4 are filled with zeros as padding.

Common errors

Which EXPAND error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The rows or columns argument is zero, negative, or less than the array's current height or width.	Specify rows and columns as positive integers at least as large as the array's dimensions. For example, change =EXPAND(array, -1) to =EXPAND(array, 5).
#VALUE!	The rows or columns argument is text, a logical value, or another non-numeric type.	Ensure rows and columns contain only numeric values. For example, change =EXPAND(array, "five", 3) to =EXPAND(array, 5, 3).
#SPILL!	The expanded array is too large to fit in the available worksheet space, or cells in the target spill range are already occupied or protected.	Clear any content blocking the spill range, or reduce the rows or columns arguments to fit within available cells. Move the formula to a location with more room.

Tips and when to use something else

Use EXPAND to build uniform-sized templates for data entry; if you need to wrap or rearrange data instead, use WRAPROWS or WRAPCOLS.
Always provide a pad_with value even if it's an empty string ("")—omitting it when expansion occurs causes an error.
EXPAND preserves the data type of pad_with; padding a numeric array with text will create mixed-type columns.
Combine EXPAND with VSTACK or HSTACK to build larger datasets by first expanding individual arrays to the same size, then stacking them.

Related functions

WRAPROWSWRAPROWS wraps a 1D array into multiple rows, specifying how many columns each row should contain, with optional padding for incomplete rows.WRAPCOLSWRAPCOLS reformats a single row or column of values into a multi-column array, useful for creating balanced layouts from long lists.VSTACKVSTACK combines multiple arrays or ranges by stacking them vertically into a single array, placing each input one below the other.HSTACKHSTACK combines multiple arrays horizontally (side by side) into a single rectangular array, expanding shorter arrays with blanks to create a unified output.TAKETAKE extracts a contiguous subset of rows and columns from an array, starting from the beginning or end; use it when you need the first N items or last M items.

Frequently asked questions

What happens if I expand an array to fewer rows than it currently has?: EXPAND returns a #NUM! error because it cannot reduce an array's size—it only enlarges. Use TAKE to truncate arrays to a smaller dimension instead.
Can I expand by columns only without changing the number of rows?: Yes. Omit the columns argument to keep the original width, or specify columns equal to the array's current width. Only the rows argument forces row expansion.
What's the default padding value if I don't specify pad_with?: EXPAND has no default padding value; if pad_with is omitted and expansion is needed, it returns #VALUE!. Always provide a pad_with argument, even an empty string, to avoid this error.
Can I expand both rows and columns at the same time?: Yes, EXPAND expands both dimensions simultaneously. Provide both rows and columns arguments; the new cells created in the expanded areas are filled with the pad_with value.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17