The ARRAYFORMULA function in Google Sheets is **useful to apply a formula to an entire column in Google Sheets. In other words, it converts a formula that returns one value into a formula that returns an array.**

##### Table of Contents

The use of `ARRAYFORMULA`

s sounds scary and difficult to understand at first. To see what their purpose is, let me explain what an `ARRAYFORMULA`

is.

When working with a large data set, it is common to repeat similar formulas across the length of a column. When using a regular formula (without `ARRAYFORMULA`

), you can put a formula in the first line of the data set:

And then drag down the formula in the next rows:

Google Sheets will automatically adjust the formula of the first cell in the following rows, and it will apply the same calculations with the respective row numbers until the row where you dragged it down. Therefore, the calculation** =B2+C2** in the second line will be changed to **=B3+C3** automatically in the third row, and so on.

It is a pretty easy way to apply a structurally similar formula to a list of data, and in many simple cases, it is enough to do it like this.

However, you can automate this process completely using an `ARRAYFORMULA`

which allows you more flexibility and dynamics with the data when working in batch.

As its name suggests, the `ARRAYFORMULA`

function applies a formula to a whole array (we call ranges of connected values arrays). You only need to enter the formula in a single cell and wrap it into an `ARRAYFORMULA`

. It expands automatically to all the rows down in the range.

## The Anatomy of the ARRAYFORMULA Function

The syntax of `ARRAYFORMULA`

isn’t too self-explanatory, but you will see examples of how to use it below. So the syntax (the way we write it) is:

=ARRAYFORMULA(array_formula)

Let’s break it down and understand what each of the components mean:

`=`

the equal sign is just how we start any function in Google Sheets.`ARRAYFORMULA()`

is our function. When using the`ARRAYFORMULA`

function We will have to provide the corresponding`array_formula`

attribute for it to work.`array_formula`

is the formula we want to apply to the whole array. By definition, it can either be a**range**, a**mathematical expression**using one cell range or multiple ranges of the same size. Additionally, it can also be a**function**that returns a result greater than one cell.

The difference from the drag-down copy and paste solution is that here all you need to change is the single cell references in your formula into references that refer to a column or range of cells. As aforementioned above, this will automate the entire process and make it quicker.

### How to Use ARRAYFORMULA with a Mathematical Expression

Say you would like to add the values of column **B** and **C** from the first to the tenth row and instead of doing it one by one, you use an `ARRAYFORMULA`

. You should add the two ranges of the same size, and as a result, it will return an array of the same size that contains the summarized values of each row from 1 to 10.

=ARRAYFORMULA(B1:B10 + C1:C10)

This formula has an `ARRAYFORMULA`

with a **mathematical expression** (addition) inside. You can create similar formulas using subtraction, multiplication, raising to powers, etc. and all the variety of their combinations.

Furthermore, the syntax of the `ARRAYFORMULA`

function defines that apart from mathematical expressions, you can also use single **ranges** or **functions** as well.

### How to Use ARRAYFORMULA with a Single Range

When you want to use single ranges in an `ARRAYFORMULA`

:

- You can put a single range into an array formula as well. It will copy the values from the selected column to your array. You can write the following expression to copy the content of column
**A**starting from**A1**:**=ARRAYFORMULA(A1:A)** - You can do the same horizontally as well when using an
`ARRAYFORMULA`

through a row. This expression will copy the content of the first row starting from**A1**:**=ARRAYFORMULA(A1:1)**

### How to Use ARRAYFORMULA with a Function

The most powerful and seemingly most complicated option is to wrap another **function** in an `ARRAYFORMULA`

. It enables you to use arrays in non-array functions. You can create repetitive calculations across a whole data range while writing the function only in the first cell of the array.

There are endless opportunities with `ARRAYFORMULA`

for beginner and advanced users as well, and it can make your sheets a lot more automated and flexible.

=ARRAYFORMULA(function())

The **function()** inside can be any function that is applied to the whole array.

You can see a step-by-step guide in the last section (“How to Use ARRAYFORMULA Function in Google Sheets”) down below this post where we use `ARRAYFORMULA`

with an `IF`

expression inside.

### ⚠️ Notes to Use ARRAYFORMULA Function Perfectly

- Each array has to be of the same size to operate between arrays.
- Pressing
**Ctrl+Shift+Enter**while editing a formula will automatically add`ARRAYFORMULA(`

to the beginning of the formula. - You can make changes in just one cell, and the effect takes place across the data range of the
`ARRAYFORMULA`

. - Advanced users can combine the
`ARRAYFORMULA`

with a lot of other functions, but it does not work with`FILTER`

or`QUERY`

.

## A Real Example of Using ARRAYFORMULA Function

Let’s see how to use ARRAYFORMULA function in Google Sheets with the previous example of the number of pets. We want to apply the same addition formula as before which is a mathematical expression. But instead of doing in it every single cell, we wrap it in an `ARRAYFORMULA`

.

Therefore we have to change the single cell references into range references in the following way to apply the addition to the whole column.

=ARRAYFORMULA(B2:B + C2:C)

We put this formula in the cell **E2**:

If a single cell reference was **B2** before, now we change it into the whole column starting from **B2**, so we write **B2:B**. After hitting Enter, Google Sheets will automatically apply the same calculation to the entire array.

You can see that the `ARRAYFORMULA`

is applied to the whole column, and there is an infinite number of zeros at the end. If you don’t want these infinite columns, you can set a limit to your range in the `ARRAYFORMULA`

as well, so, for example, instead of applying it to the whole column (**B2:B+C2:C**), only apply it until the 10th row with the formula below:

=ARRAYFORMULA(B2:B10 + C2:C10)

And with this solution, the array only runs until the 10th row.

You may make a copy of the spreadsheet using the link I have attached below and try it for yourself:

## How to Use ARRAYFORMULA Function in Google Sheets

Let’s see how to create an `ARRAYFORMULA`

formula containing a function inside step-by-step.

In this example, we want to write a formula to automatically calculate which students are underage in our list.

- Select the first cell where you want your array to start! For this guide, I will be selecting
**C2**.

- To count who is underage, we will use an
`IF`

formula that returns “YES” if the age is under 18 and returns “NO” if it is over 18. The`IF`

formula we would use in a single cell:`=IF(B2<18, "YES", "NO")`

.

- If we hit
**Enter**to this formula, and it returns the result for the first student. It says “NO” which means that this student is not underage. (To make it more visible, I added conditional formatting to the whole column**C**to mark the cells red when the result is “YES” and mark them green when the result is “NO”).

- Now we want to change this formula into an
`ARRAYFORMULA`

to apply the calculation to the whole column. We wrap the formula into an`ARRAYFORMULA`

. You can write it manually or press**Ctrl+Shift+Enter**to do it:`=ARRAYFORMULA(IF(B2<18, "YES", "NO"))`

- Now we must change the single cell reference into a range reference where we want to extend the calculation. So we replace the
**B2**reference into a column reference**B2:B**to apply it to the whole column**B**. We have to change the reference in the following way:`=ARRAYFORMULA(IF(B2:B<18, "YES", "NO"))`

- Hit
**Enter,**and you can see the entire column filled with the result.

- Great! We have filled only one cell and returned the result for the whole column! You can experiment with how automated it works. Add a new student to the table, and the
**Underage**column will be filled automatically!

- Now let’s change our condition and consider people under 21 years underage. We only need to change the condition (
**18**to**21**) in the first cell:`=ARRAYFORMULA(IF(B2:B<21, "YES", "NO"))`

. Thus the formula recalculates the whole range. It is very flexible!

That’s it, well done! You can now use the `ARRAYFORMULA`

function together with the **other numerous Google Sheets formulas** to create even more powerful formulas that can make your life much easier. 🙂