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:
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:
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:
You can specify the entire array in another sheet:
=arrayformula(Sheet1!A2:C7)
And the result looks like this:
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:
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:
Recommended articles
More resources
