How to Use ARRAYFORMULA Function in Google Sheets

ARRAYFORMULA Function in Google Sheets
How to Use ARRAYFORMULA Function in Google Sheets – Sheetaki

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.

The use of ARRAYFORMULAs 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:

 

Expand Function Without ARRAYFORMULA Function in Google Sheets

 

And then drag down the formula in the next rows:

 

Expand Function Without ARRAYFORMULA Function in Google Sheets

 

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:

  1. 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)
  2. 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

  1. Each array has to be of the same size to operate between arrays.
  2. Pressing Ctrl+Shift+Enter while editing a formula will automatically add ARRAYFORMULA( to the beginning of the formula.
  3. You can make changes in just one cell, and the effect takes place across the data range of the ARRAYFORMULA.
  4. 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:

 

ARRAYFORMULA Function in Google Sheets

 

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.

 

ARRAYFORMULA Function in Google Sheets

 

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.

 

ARRAYFORMULA Function in Google Sheets

 

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.

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

ARRAYFORMULA Function in Google Sheets

 

  1. 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").

ARRAYFORMULA Function in Google Sheets

 

  1. 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”).

ARRAYFORMULA Function in Google Sheets

 

  1. 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"))

 

  1. 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"))

ARRAYFORMULA Function in Google Sheets

 

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

ARRAYFORMULA Function in Google Sheets

 

  1. 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!

 

  1. 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!

ARRAYFORMULA Function in Google Sheets

 

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. 🙂

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like