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:

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:

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