In this article:

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

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. 


Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started