EXPAND in Excel: Pad Arrays to a Required Output Size

• Introduced: Preview announced March 16, 2022: Windows Beta 2203 (15104.20004), Mac 16.60 (22030400). Web and Microsoft 365 Current Channel deployment reported September 29, 2022. Release history.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac.
• Checked: October 14, 2026; official EXPAND reference. Documentation check; examples independently calculated, not native-tested.

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

An omitted row or column dimension leaves that original dimension unchanged. The default padding is the error #N/A. A requested dimension smaller than the source produces #VALUE!; an excessively large array can produce #NUM!. Official EXPAND reference

The result keeps the source at its top-left corner and adds positions below or to the right. Dimension arguments describe the final size, not the number of extra rows or columns to append.

Enter the following in A2:B3 with no headings in that range:

10  20
30  40

Use D2 or another clear output anchor. For the first examples, reserve three rows by four columns around that anchor.

=EXPAND(A2:B3,3,4)

Expected three-by-four output:

10    20    #N/A  #N/A
30    40    #N/A  #N/A
#N/A  #N/A  #N/A  #N/A

There are twelve output positions: four original values and eight padding errors. The requested 3 and 4 are the final height and width. Adding three more rows and four more columns would be a different size.

These #N/A positions are part of the returned array. They indicate that the output position was added by padding, not that EXPAND failed to find the original values.

=EXPAND(A2:B3,3,4,0)

Expected output:

10  20  0  0
30  40  0  0
 0   0  0  0

The sum remains 100, because the added zeros do not change the total. The arithmetic average of all twelve positions, however, is 100/12, approximately 8.333333. The original four-value average was 25.

That difference is why padding is a modeling choice. A placeholder zero is a real numeric value to downstream statistical functions; it is not automatically treated as missing data.

=EXPAND(A2:B3,,4,"-")

Expected output is two rows by four columns:

10  20  -  -
30  40  -  -

The empty second argument keeps the original two-row height. The supplied 4 belongs to columns, and the final hyphen is the padding text.

Similarly, =EXPAND(A2:B3,4) returns four rows by two columns. Its added last two rows contain the default #N/A padding because no alternative filler was supplied.

On a separate sheet, put Pen, Pad and Box in D2:D4. A five-position panel can use:

=EXPAND(D2:D4,5,1,"")

Expected vertical output is Pen, Pad, Box, empty text, empty text. The final two cells look empty but belong to the spill. You cannot type a new label into one of them without obstructing that formula’s result.

This is useful for matching a display template’s height. It does not invent two new records, nor does it automatically pull extra values below D4 if the source reference remains unchanged.

For the numeric zero-padding example:

=INDEX(EXPAND(A2:B3,3,4,0),2,2)
=INDEX(EXPAND(A2:B3,3,4,0),3,4)

Expected results are 40 and 0. Position (2,2) belongs to the original matrix, while (3,4) is newly added padding. The check distinguishes data preservation from correct output dimensions.

EXPAND adds positions outside the original rectangle. It is not a general replace-blanks function for empty positions inside the source. If interior blanks need cleanup, define that transformation separately before expanding the result.

A request such as =EXPAND(A2:B3,1,4) asks for fewer source rows and should be rejected with the documented #VALUE! error. Use an intentional selection function such as TAKE when the goal is to shorten data.

If a valid expansion is blocked, inspect the destination rectangle for existing content. If zeros distort a later average, reconsider the padding policy rather than changing the original matrix. Keep an unpadded source available when downstream calculations need to distinguish real observations from layout placeholders.

• TAKE in Excel
• INDEX in Excel
• SUM in Excel
• AVERAGE in Excel

Verification: These are fictional inputs and independently calculated expected results, not native Excel observations. See the editorial testing standards.