The ARRAYFORMULA function spreads its output across multiple rows and columns. You can use this function to repeat non-array functions such as SUM, PRODUCT, or AVERAGE.

As with other functions that generate arrays, such as FILTER and QUERY, the output needs space to expand below and right. Keep this in mind so you do not overwrite other data.

*Tip: Use the shortcut key combination of Ctrl+Shift+Enter to wrap your function inside ARRAYFORMULA.*

## Syntax

`=ARRAYFORMULA(array_formula)`

`arrayformula`

– One of the following:- A range
- Mathematical expression with ranges of the same size
- A function that returns more than one cell

## Related Functions

ARRAY_CONSTRAIN – Limits the output of an array

QUERY – SQL-like function that you can use to return more complex arrays

SUMPRODUCT – Calculates the sum of two or more ranges of multiplied values. If using ARRAYFORMULA to extend simple multiplication, consider this function instead.

## Video Tutorial

## Examples

The table pictured above is the source data used for all examples. It represents a company’s inventory that consists of different types of items, number of cases, number of items per case, and location. As the data contains a mix of numbers and strings, it will make for easy examples of using the ARRAYFORMULA.

### Example 1 – Return a Column

The formula in the top cell is `=ARRAYFORMULA(A1:A13)`

.

You can use a simple cell range as the only input, and the function outputs just that column as shown on the left. A cell range is not a typical use of the function but serves to illustrate how it writes data into more than one cell.

You may want to return unmodified data if you need to duplicate a table of data while leaving the source intact. When you update the source data, the output of this function will update accordingly.

### Example 2 – Return a Row

The formula in the left cell is `=ARRAYFORMULA(A2:D2)`

.

The image above shows another simple illustration where we use the function to output data without further manipulation. However, this example shows that the formula can work horizontally on rows and vertically on columns.

### Example 3 – Add a Column with Multiplication

The formula in cell E2 `=ARRAYFORMULA((B2:B13)*(C2:C13))`

Using the ARRAYFORMULA to extend multiplication down a column is an excellent example of the power of this function. This one formula accomplishes the same thing as repeating a multiplication formula 12 times, once in each row. If you delete rows from the table, the formula will automatically adjust.

Note that each range in the input needs to be the same size. In this example, the ranges of `B2:B13`

and `C2:C13`

both span 12 rows. If you have a very long table, you could drop the ending row values such as B2:B and C2:C. Removing the ending row numbers would extend the formula to the bottom of the spreadsheet.

### Example 4 – Multiply Values if a Word Matches

The formula in cell E2 is `=ARRAYFORMULA(IF(D2:D13="North",(B2:B13)*(C2:C13),0))`

.

The formula used for the `array_formula`

input can be more complex if needed. In this case, we have added an `IF`

statement to limit the multiplication to only the `North`

warehouse.

### Example 5 – Join Words and Numbers

The formula in the top row is

`=ARRAYFORMULA("The "&D2:D13&" warehouse has "&B2:B13&" cases of "&A2:A13&"s.")`

.

The formula does not need to be mathematical. In this example, we use it to join words and numbers to create simple phrases. Using the & sign is similar to the CONCATENATE formula.

### Example 6 – Multidimensional Arrays

For this last example, we will use a different data set.

Previously we looked at how arrays can be vertical **or** horizontal. They can also be multidimensional.

This table calculates shipping rates. The formula, `=ARRAYFORMULA(B3:B6*C2:F2*$B$9)`

, is multiplying the range of `B3:B6`

containing the `Weight`

, the range of `C2:F2`

containing the `Distance`

, and the `Price for 1 ounce per 100 miles`

in cell `B9`

. Notice that the `$`

s in `B9`

are fixing the row and column references to stay the same in every cell. The array formula is multiplying the three values and writing its results below **and **to the right.

### Live Example in Sheets

Go to this spreadsheet for examples of the ARRAYFORMULA function shown above that you can study and use anywhere you want.