How to use the EXPAND function

Author: Oscar Cronquist Article last updated on May 05, 2022

The EXPAND function increases a cell range or array by a specified number of columns and rows.

Dynamic array formula in cell B6:

=EXPAND(B2:C3, 3, 3, "-")

The EXPAND function is available to Excel 365 users and is in the "Array manipulation" category.

EXPAND Function Syntax
EXPAND Function Arguments
EXPAND Function example
EXPAND Function example - how to extend an array up and left
EXPAND Function example - extend array dynamically
EXPAND Function example - errors
EXPAND Function - values in random order

1. EXPAND Function Syntax

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

2. EXPAND Function Arguments

array	Required. The source cell range or array.
rows	Required. The new number of rows.
[columns]	Optional. The new number of columns.
[pad_with]	Optional. A string to pad new array values with. Default is #N/A meaning not available.

3. EXPAND Function example

The picture above shows how the EXPAND function increases the original array from two columns and two rows to three columns and three rows.

The number of cells increases to 9 from 4 cells, 3 rows * 3 columns equals 9 cells. The new cells are padded with a minus sign.

Dynamic array formula in cell B6:

=EXPAND(B2:C3, 3, 3, "-")

Note! If any of the values in the source range change, the values in the EXPAND function output change as well.

3.1 Explaining formula

Step 1 - EXPAND function

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

Step 2 - Populate arguments

array - B2:C3
rows - 3
[columns] - 3
[pad_with] - "-". The double quotes are needed if a non-numeric value is used.

Step 3 - Evaluate function

EXPAND(B2:C3, 3, 3, "-")

becomes

EXPAND({89, 27;
68, 84}, 3, 3, "-")

and returns

{89, 27, "-";
68, 84, "-";
"-", "-", "-"}

4. EXPAND Function example - how to extend an array up and left

The EXPAND function lets you add more rows and columns to an array, they are, however, added to the right and below of the source array.

Use the following formula to add array containers above and to the left of the original array.

Dynamic array formula in cell B8:

=VSTACK(B2:D2, HSTACK(B3:B4, C3:D4))

Explaining formula

Step 1 - Add arrays horizontally

The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

HSTACK(array1, [array2],...)

HSTACK(B3:B4, C3:D4)

becomes

HSTACK({0; 0}, {89, 27; 68, 84})

and returns

{0, 89, 27; 0, 68, 84}.

Step 2 - Add arrays vertically

The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array (vertical stacking).

VSTACK(array1, [array2],...)

VSTACK(B2:D2, HSTACK(B3:B4, C3:D4))

becomes

VSTACK({0, 0, 0}, {0, 89, 27; 0, 68, 84})

and returns

{0, 0, 0;
0, 89, 27;
0, 68, 84}.

5. EXPAND Function example - extend array dynamically

The section shows how to return dynamically resize an array meaning the array is always one row and one column larger than the source array.

Try changing the text value in cell B3 and the dynamic array formula in cell D3 adjusts accordingly.

Dynamic array formula in cell B9:

=LET(x, TEXTSPLIT(B3, " "), EXPAND(x, ROWS(x)+1, COLUMNS(x)+1, "-"))

Explaining formula

EXPAND(TEXTSPLIT(B3, " "), ROWS(TEXTSPLIT(B3, " "))+1, COLUMNS(TEXTSPLIT(B3, " "))+1, "-")

Step 1 - Split text

The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3, " ")

Step 2 - Calculate rows in array and add 1

The ROWS function calculates the number of rows in a given cell range or array.

ROWS(array)

ROWS(TEXTSPLIT(B3, " "))+1

Step 3 - Calculate columns in array and add 1

The COLUMNS function calculates the number of columns in a given cell range or array.

COLUMNS(array)

COLUMNS(TEXTSPLIT(B3, " "))+1

Step 4 - Expand array one row and one column

EXPAND(TEXTSPLIT(B3, " "), ROWS(TEXTSPLIT(B3, " "))+1, COLUMNS(TEXTSPLIT(B3, " "))+1, "-")

becomes

EXPAND({"I", "travel", "the", "world"}, 1+1, 4+1, "-")

and returns

{"I", "travel", "the", "world", "-";
"-", "-", "-", "-", "-"}

Step 5 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

TEXTSPLIT(B3, " ") is repeated three times.

EXPAND(TEXTSPLIT(B3, " "), ROWS(TEXTSPLIT(B3, " "))+1, COLUMNS(TEXTSPLIT(B3, " "))+1, "-")

becomes

LET(x, TEXTSPLIT(B3, " "),EXPAND(x,ROWS(x)+1,COLUMNS(x)+1,"-"))

6. EXPAND function error

The EXPAND function returns a #VALUE! error if a negative number is used in the second or third argument.

Numbers smaller than the actual array size also return a #VALUE! error. Cell range B2:D4 has three rows and three columns.

7. EXPAND function - values in random order

The image above demonstrates a formula that adds an additional row to cell range B2:D5 and the returns values in random order.

Dynamic array formula in cell B8:

=LET(x, TOCOL(EXPAND(B2:D5, 5, , "-")), WRAPROWS(SORTBY(x, RANDARRAY(ROWS(x))), 3))

8.1 Explaining formula in cell B8

WRAPROWS(SORTBY(TOCOL(EXPAND(B2:D5,5,,"-")),RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5,5,,"-"))))),3)

Step 1 - Expand array with an extra row

EXPAND(B2:D5,5,,"-")

becomes

EXPAND({89, 27, 26;68, 84, 98;19, 92, 62;37, 63, 45}, 5, , "-")

and returns

{89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45;
"-", "-", "-"}

Step 2 - Rearrange values into a single column

The TOCOL function rearranges values from a 2D cell range or array to a single column.

TOCOL(array, [ignore], [scan_by_col])

TOCOL(EXPAND(B2:D5,5,,"-"))

becomes

TOCOL({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45;
"-", "-", "-"})

and returns

{89; 27; 26; 68; 84; 98; 19; 92; 62; 37; 63; 45; "-"; "-"; "-"}

The semicolon is a row delimiting character, your computer may use an other row delimiting character.

Step 3 - Count cells

The ROWS function returns the number of columns in a given cell range or array.

ROWS(array)

ROWS(TOCOL(EXPAND(B2:D5,5,,"-")))

becomes

ROWS({89; 27; 26; 68; 84; 98; 19; 92; 62; 37; 63; 45; "-"; "-"; "-"})

and returns 15.

Step 4 - Create random cells

The RANDARRAY function returns a table of random numbers across rows and columns.

RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5, 5, , "-")))))

becomes

RANDARRAY(15)

and returns

{0.215398134613085, 0.390607168196479, ... ,0.83231474462401}.

Step 6 - Rearrange values in random order

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(TOCOL(EXPAND(B2:D5, 5, , "-")), RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5, 5, , "-")))))

becomes

SORTBY({89; 27; 26; 68; 84; 98; 19; 92; 62; 37; 63; 45; "-"; "-"; "-"}, {0.215398134613085, 0.390607168196479, ... ,0.83231474462401})

and returns

{"-"; "-"; 84; 62; 89; 92; 98; 37; 63; "-"; 26; 27; 45; 68; 19}.

Step 7 - Rearrange values to the original array size

The WRAPROWS function rearranges values from a single row to a 2D cell range based on a given number of values per row.

WRAPROWS(vector, wrap_count, [pad_with])

WRAPROWS(SORTBY(TOCOL(EXPAND(B2:D5,5,,"-")),RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5,5,,"-"))))),3)

becomes

WRAPROWS({"-"; "-"; 84; 62; 89; 92; 98; 37; 63; "-"; 26; 27; 45; 68; 19},3)

and returns

{84,92,"-";
"-",68,63;
27,37,19;
45,62,"-";
98,89,26}

Step 8 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

WRAPROWS(SORTBY(TOCOL(EXPAND(B2:D5,5,,"-")),RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5,5,,"-"))))),3)

becomes

LET(x, TOCOL(EXPAND(B2:D5, 5, , "-")), WRAPROWS(SORTBY(x, RANDARRAY(ROWS(x))), 3))

Functions in this article

EXPAND VSTACK HSTACK LET TEXTSPLIT ROWS COLUMNS TOCOL WRAPROWS SORTBY RANDARRAY

Functions in 'Array manipulation' category

The EXPAND function function is one of many functions in the 'Array manipulation' category.

CHOOSECOLS function

Returns given columns from a cell range or array.

CHOOSEROWS function

Returns given rows from a cell range or array.

DROP function

Removes a given number of rows or columns from a 2D cell range or array.

EXPAND function

Increases a cell range or array by a specified number of columns and rows.

HSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

TAKE function

Returns a given number of rows or columns from a 2D cell range or array.

TOCOL function

Rearranges values in 2D cell ranges to a single column.

TOROW function

Rearranges values from a 2D cell range to a single row.

VSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

WRAPCOLS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPROWS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

How to use the EXPAND function

Table of Contents

1. EXPAND Function Syntax

2. EXPAND Function Arguments

3. EXPAND Function example

3.1 Explaining formula

Step 1 - EXPAND function

Step 2 - Populate arguments

Step 3 - Evaluate function

4. EXPAND Function example - how to extend an array up and left

Explaining formula

Step 1 - Add arrays horizontally

Step 2 - Add arrays vertically

5. EXPAND Function example - extend array dynamically

Explaining formula

Step 1 - Split text

Step 2 - Calculate rows in array and add 1

Step 3 - Calculate columns in array and add 1

Step 4 - Expand array one row and one column

Step 5 - Shorten the formula

6. EXPAND function error

7. EXPAND function - values in random order

8.1 Explaining formula in cell B8

Step 1 - Expand array with an extra row

Step 2 - Rearrange values into a single column

Step 3 - Count cells

Step 4 - Create random cells

Step 6 - Rearrange values in random order

Step 7 - Rearrange values to the original array size

Step 8 - Shorten formula

Functions in this article

Functions in 'Array manipulation' category

Excel function categories

Excel categories

Leave a Reply

How to comment

How to use the EXPAND function

Table of Contents

1. EXPAND Function Syntax

2. EXPAND Function Arguments

3. EXPAND Function example

3.1 Explaining formula

Step 1 - EXPAND function

Step 2 - Populate arguments

Step 3 - Evaluate function

4. EXPAND Function example - how to extend an array up and left

Explaining formula

Step 1 - Add arrays horizontally

Step 2 - Add arrays vertically

5. EXPAND Function example - extend array dynamically

Explaining formula

Step 1 - Split text

Step 2 - Calculate rows in array and add 1

Step 3 - Calculate columns in array and add 1

Step 4 - Expand array one row and one column

Step 5 - Shorten the formula

6. EXPAND function error

7. EXPAND function - values in random order

8.1 Explaining formula in cell B8

Step 1 - Expand array with an extra row

Step 2 - Rearrange values into a single column

Step 3 - Count cells

Step 4 - Create random cells

Step 6 - Rearrange values in random order

Step 7 - Rearrange values to the original array size

Step 8 - Shorten formula

Excel '365' functions

Functions in this article

Functions in 'Array manipulation' category

Excel function categories

Excel categories

Leave a Reply

How to comment