EXPAND Function in Excel (5 Examples)

If you want to grow an array to a set number of rows or columns and fill the extra cells with a value of your choice, the EXPAND function is what you’re looking for.

EXPAND is a dynamic array function, so it spills its results across the cells below and to the right.

In this article, I’ll show you how to use EXPAND in Excel with a few practical examples.

This Tutorial Covers:

EXPAND Function Syntax

Here is the syntax of the EXPAND function:

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

array – The range or array you want to expand.
rows – The number of rows you want the result to have. Leave it out and the row count stays the same, but then you have to give the columns argument.
columns – The number of columns you want the result to have. Leave it out and the column count stays the same, but then you have to give the rows argument.
pad_with – The value that goes in the extra cells. Leave it out and Excel fills them with #N/A.

When to Use EXPAND in Excel

Use this function when you need to:

Grow a small array to a fixed number of rows or columns.
Fill the new cells with a specific value like 0, a dash, or a blank instead of #N/A.
Lock the size of a FILTER or other dynamic result so it always takes up the same space.
Pad a narrow table so its column count matches another table before you stack them.

Let me show you a few practical examples of how to use this function.

Example 1: Grow a Small Table to More Rows

Let’s start with a simple example.

Below is the dataset. It has a small book inventory with the book title in column A and the number of copies in column B, three rows in all.

Excel table with Book and Copies columns containing three rows of data for use in an EXPAND function example

I want to grow this three-row table into a six-row block so there is room for more books later.

Here is the formula:

=EXPAND(A2:B4,6,2)

Excel formula =EXPAND(A2:B4,6,2) in formula bar, showing an array expanded to 6 rows with #N/A padding in new cells

In the above formula, EXPAND takes the range A2:B4 and grows it to 6 rows and 2 columns. The three original rows stay put, and the three new rows appear below them.

Since I left out the pad_with argument, Excel fills those new cells with #N/A. That is the default, and I’ll show you how to change it in the next example.

Example 2: Pad the Extra Cells With a Value

Here’s another practical scenario.

Below is the dataset. It has a headcount for three teams, with the team name in column A and the number of people in column B.

Excel table showing Team and Headcount data in columns A and B, with an empty column D grid for the EXPAND example

I want the headcount column to always be six rows tall so a chart built on it never changes height, and I want the empty rows to show 0 instead of #N/A.

Here is the formula:

=EXPAND(B2:B4,6,1,0)

Excel formula bar showing EXPAND function with result in column D, padding rows 5 and 6 with 0

Here, EXPAND grows the single column of headcounts to 6 rows and 1 column. The fourth argument, 0, tells Excel to put a 0 in each new cell instead of the default #N/A.

The pad_with value can be anything you like. A number, some text in quotes, or a dash all work the same way.

Pro Tip: Use an empty string (“”) as the pad_with value when you want the extra cells to look blank rather than show a placeholder.

Example 3: Add More Columns to a Table

Now let’s look at something a bit more interesting.

Below is the dataset. It has a class roster with the student name in column A and two test scores in columns B and C, four students in all.

Excel table with student names and test scores in columns A through C, representing the expand example 3 dataset

I want to add two more test columns to the roster now so the layout is ready for the rest of the term, and I want those cells to stay blank for now.

Here is the formula:

=EXPAND(A2:C5,4,5,"")

Excel formula bar showing EXPAND function expanding a table to 4 rows and 5 columns with empty string padding

In the above formula, the array A2:C5 is 4 rows by 3 columns. I keep the rows at 4 and grow the columns to 5, so two new columns appear on the right.

The pad_with value is an empty string, so the new cells come out blank instead of showing #N/A.

Example 4: Lock a FILTER Result to a Fixed Size

Let’s step it up with a use case I reach for quite often.

Below is the dataset. It has a gym check-in list with the member name in column A and their status (Active or Inactive) in column B, eight members in all.

Excel dataset with Member and Status columns A and B, and empty cells in column D for EXPAND function output

I want to pull only the active members and always show them in a six-row block, even though the number of active members can change.

Here is the formula:

=EXPAND(FILTER(A2:A9,B2:B9="Active"),6,1,"")

Excel formula bar showing EXPAND and FILTER functions, with result spilling into column D to list active members

How this formula works:

FILTER(A2:A9,B2:B9=”Active”) pulls the five active members into a spilled list.
EXPAND then grows that list to 6 rows and 1 column, so the block is a fixed height no matter how many members come back.
The empty string in the pad_with slot leaves the leftover row blank.

This is handy for a dashboard, where a FILTER result that grows and shrinks would otherwise push your other elements around. Wrapping it in EXPAND keeps the footprint steady.

Pro Tip: The rows value must be at least as large as the number of results FILTER returns. If FILTER brings back more rows than you asked EXPAND to show, you’ll get a #VALUE! error, since EXPAND only grows an array and never trims it.

Example 5: Match Column Counts Before Stacking

This last one combines EXPAND with VSTACK.

Below is the dataset. It has two small price lists side by side. The first list in columns A to C has three columns (Item, Price, Status), and the second list in columns E and F has only two (Item, Price).

Excel table showing two datasets with Item, Price, and Status columns, demonstrating the EXPAND function example 5

I want to stack the second list under the first, but VSTACK needs both to have the same number of columns.

Here is the formula:

=VSTACK(A2:C3,EXPAND(E2:F3,2,3,"-"))

Excel formula bar showing VSTACK and EXPAND functions, with the resulting array displaying padded hyphens in row 8

How this formula works:

EXPAND(E2:F3,2,3,”-“) grows the two-column list to three columns, keeping its 2 rows and adding a dash in the new Status cells.
VSTACK then stacks that padded list under the first list, and both now have three columns, so there is no size mismatch.

Without EXPAND, VSTACK would fill the missing column with #N/A. Padding it first lets you decide exactly what those cells show.

Tips & Common Mistakes

EXPAND only grows, it never shrinks. If the rows or columns value is smaller than the array you pass in, you get a #VALUE! error. To pull a set number of rows off the top or bottom, use the TAKE function. To remove rows or columns instead, use the DROP function.
You must give at least one of rows or columns. If you leave both out, the formula returns an error, since Excel has no target size to grow to.
EXPAND grows down and to the right only. There is no built-in way to add cells above or to the left. To do that, put the new rows or columns first with VSTACK or HSTACK.
Watch the spill range. EXPAND is a dynamic array function, so its result spills. If a cell in the spill range already has something in it, you’ll see a #SPILL! error until you clear the blocking cell.
EXPAND is a newer function. It works in Excel for Microsoft 365 and Excel 2024. It is not available in Excel 2021 or earlier.

In this article, I covered how to use EXPAND to grow an array, how to pad the new cells with a value of your choice, and how to lock a FILTER result to a fixed size.

I also showed you how to pair it with VSTACK to line up mismatched tables.

I hope you found this article helpful.

List of All Excel Functions

Other Excel Functions You May Also Like:

Other Excel Articles You May Also Like: