Google Sheets

How to Use ARRAYFORMULA in Google Sheets

Use ARRAYFORMULA in Google Sheets to apply one formula across an entire column, fill calculated fields, and avoid fragile copy-down formulas.

June 16, 2026/5 min read

ARRAYFORMULA in Google Sheets lets one formula return results for many rows or columns at once. It is useful when a calculated field should keep working as new rows are added to a tracker, budget, CRM, or calendar.

Use it when the logic is the same for every row. If each row needs a different exception, a normal formula copied down may be easier to audit.

The syntax

=ARRAYFORMULA(array_formula)

array_formula - the expression you want Sheets to calculate across a range

The most common pattern is wrapping a row-level formula so it fills a whole column.

Basic example

Say quantity is in B2:B, price is in C2:C, and total should appear in column D. Instead of copying a formula down every row, use:

=ARRAYFORMULA(B2:B*C2:C)

That returns a total for every row where the source values exist.

In a real operating sheet, you usually want blank source rows to stay blank:

=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))

This works well in an expense tracker or invoice template where the calculated amount should appear only when a row has been started.

Add a status label across a column

ARRAYFORMULA is also useful for review labels. If due dates are in E2:E, this formula marks overdue rows:

=ARRAYFORMULA(IF(E2:E="","",IF(E2:E<TODAY(),"Overdue","OK")))

That pattern fits a project tracker, content calendar, or CRM follow-up sheet because the review label updates as dates change.

Include the header in the formula

If you want one formula in the header row to create both the label and the calculated values, use an array literal:

={"Status";ARRAYFORMULA(IF(E2:E="","",IF(E2:E<TODAY(),"Overdue","OK")))}

The first value creates the header. The semicolon stacks the calculated column underneath it.

TIP

Put ARRAYFORMULA in one cell at the top of the output range. Do not also type values into the cells it needs to fill.

Common ARRAYFORMULA mistakes

Problem	Why it happens	Fix
Formula returns too many blank rows	Full-column range has no blank check	Wrap the logic in `IF(A2:A="","",...)`
`#REF!` spill error	Output cells already contain values	Clear the cells below the formula
Mixed row counts	Source ranges have different sizes	Use ranges with matching start and end rows
Formula is hard to debug	Too much logic is packed into one expression	Build the row formula first, then wrap it

When not to use ARRAYFORMULA

Avoid ARRAYFORMULA when every row has custom logic, manual overrides, or formulas that are easier for the team to inspect one row at a time. A sheet should be easier to maintain after using ARRAYFORMULA, not just shorter.

The Griddy way

ARRAYFORMULA saves copy-down work, but it is easy to break the output range or forget blank-row handling.

"Turn these copied formulas into one ARRAYFORMULA column, keep blank rows blank, and make sure the overdue status still works"

Griddy can convert the repeated formula, test the row logic, and place the array formula where it will not collide with existing data.

Skip the manual work

Describe it. Griddy does it.

Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.

Excel Plugin Sheets Plugin

Use this on real templates

Keep calculated fields working as rows grow

ARRAYFORMULA is useful when templates need status labels, totals, or review fields that keep filling automatically as new rows are added.

Project Management

All posts