How to Pad an Array with EXPAND in Excel

Give dynamic lists a fixed size — a five-slot board, a padded qualifier list and an extra pre-filled column — with EXPAND.

You will learn to

• Pad a short list to a fixed number of rows
• Keep a filtered list a constant size
• Add a pre-filled column to a result

How it works

EXPAND(array, rows, [columns], [pad_with]) enlarges an array to the size you specify and fills the new cells with pad_with (or #N/A if you leave it out). =EXPAND(E2:E4,5,1,"Open slot") turns three nominations into a five-row board with two “Open slot” placeholders.

This keeps dashboards and printed templates the same shape however many results a FILTER returns. EXPAND can also add columns — =EXPAND(FILTER(A2:B9,C2:C9="East"),,3,"TBC") appends a column pre-filled with TBC. The target size must be at least as large as the array, or EXPAND returns #VALUE!.

=EXPAND(E2:E4,5,1,"Open slot")