December 22, 2020

Guide to Using ARRAYFORMULA in Google Sheets (+ Examples!)

Google Sheet spreadsheet
SECTIONS
  1. Use ARRAYFORMULA to copy entire rows, columns, and arrays
  2. Use ARRAYFORMULA to apply functions to entire arrays
  3. Use ARRAYFORMULA to apply formulas to entire arrays

The Google Sheets’ ARRAYFORMULA function is a powerful way to apply formulas and functions to entire columns, rows, and arrays by modifying a single cell. This tutorial will explain how to use this function to copy the contents of entire rows, columns, and arrays; to apply a formula to entire arrays, and to apply functions to entire arrays. 

Use ARRAYFORMULA to copy entire rows, columns, and arrays

If you know the location of the original array, you can specify its range inside the ARRAYFORMULA, and it will be copied accordingly. For example, given our array below:

Original array with the range A2:C7
Original array with the range A2:C7

Let’s say we want to copy the first column. This is how can do so using ARRAYFORMULA:

=arrayformula(A2:A)

The A2:A range means that the array will begin at cell A2 and the A means that all the succeeding cells in the column A that have a value are covered. 

And the result looks like this:

The copied column using arrayformula function
The copied column using arrayformula function.

If we instead want to copy the first row, we can do so as follows:

=arrayformula(A2:2)

The A2:2 range means that the array will begin at cell A2 and the A means that all the succeeding cells in row 2 that have a value are covered. 

And the result looks like this:

The copied row using arrayformula function
The copied row using arrayformula function.

You can specify the entire array in another sheet:

=arrayformula(Sheet1!A2:C7)

And the result looks like this:

The entire array copied using arrayformula
The entire array copied using arrayformula.


Use ARRAYFORMULA to apply functions to entire arrays

When you use a formula as an argument of ARRAYFORMULA, that formula can be applied to a specified array. This is especially useful when you are using a formula that only admits a single cell as an argument. The argument of the formula, however, should be the array. 

For example, using the same array as above, we want to convert the numbers into integers. To do so, we implement the following formula:

=arrayformula(int(A2:C7))

And the result looks like this:

The int function applied to the entire array using arrayformula. 
The int function applied to the entire array using arrayformula. 


Note: we already have a tutorial that used ARRAYFORMULA to easily apply a function to an entire array! You can read more here

Use ARRAYFORMULA to apply formulas to entire arrays

You can go beyond applying a function to applying an entire formula to an entire array using ARRAYFORMULA. For our example, we want to apply the following formula to all the cells of the entire array:

=(int(A2)-32)

We can easily do so by using the ARRAYFORMULA function then changing the cell argument to include the entire array:

=arrayformula((int(A2:C7)-32))

The result will look like this:

The formula applied to an entire array using arrayformula. 
The formula applied to an entire array using arrayformula. 


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.