How to Use ARRAYFORMULA in Google Sheets

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

• 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.

Google notes that many modern array-returning functions already expand automatically, so FILTER, UNIQUE, and similar formulas often do not need an extra ARRAYFORMULA wrapper.

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:

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:

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:

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:

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

Clean text without deleting meaningful spaces

To normalize ordinary leading, trailing, and repeated spaces in a helper column:

This keeps one space between words. Avoid a global replacement that removes every space from names, addresses, or labels.

Common ARRAYFORMULA mistakes

Inspect the intended spill area before clearing anything. A blocking cell may contain a manual input or another formula that needs to move rather than be deleted.

Performance and compatibility

Whole-column references are convenient but make a large sheet evaluate many unused rows. Use a realistic boundary when the data has a practical maximum:

Google's performance guidance recommends simplifying large array calculations and sometimes moving complex array logic into helper columns. Google also notes that array formulas cannot be exported in every context, so verify important behavior after moving the workbook to another format.

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.

Do this with AI

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.

Related guides: FILTER in Google Sheets and Google Sheets date formulas.

Sources

• Google Docs Editors Help: ARRAYFORMULA
• Google Docs Editors Help: Using arrays in Google Sheets
• Google Docs Editors Help: Improve Sheets performance