Google Sheets ARRAYFORMULA: Apply One Formula to an Entire Column
Learn how ARRAYFORMULA in Google Sheets lets a single formula process an entire column or range automatically, eliminating the need to drag formulas down and keeping data clean when new rows are added.
In a standard Google Sheet, if you want to apply a formula to 500 rows, you type it in the first cell and drag it down — or copy-paste it. This works, but it creates 500 separate formula cells. When new rows are added (from a Google Form submission, for example), the new rows have no formula.
ARRAYFORMULA solves this by processing an entire range with a single formula in a single cell.
Basic Syntax
Where array_formula is any formula that should apply row-by-row across a range.
Simple Example: Multiplication Across a Column
Without ARRAYFORMULA, you would put this in cell D2 and drag down to D100:
With ARRAYFORMULA, place this only in cell D2:
The result fills D2:D automatically for every row that has data in column B and C. As new rows are added, the result column updates immediately.
Keyboard shortcut: In a formula, press Ctrl+Shift+Enter (Windows) or Cmd+Shift+Enter (Mac) to automatically wrap the formula in
ARRAYFORMULA().
Why Open-Ended Ranges Work Here
Notice B2:B instead of B2:B100. Open-ended ranges are safe inside ARRAYFORMULA because the formula only computes values for rows with actual data — blank rows produce blank outputs, not errors or zeros.
However, avoid open-ended ranges outside ARRAYFORMULA, as they force the entire column to calculate.
Applying Text Functions to a Whole Column
Trim whitespace from an entire column of imported names:
Combine first and last name columns:
Convert all entries to uppercase:
Using IF Inside ARRAYFORMULA
You can combine IF with ARRAYFORMULA to add conditional logic across a whole column. Flag any revenue below 1000 as "Low":
Add a blank check to avoid filling empty rows with "OK":
This pattern is essential — without the blank check, ARRAYFORMULA writes "OK" in every empty row down to the sheet's last row (row 1000 by default).
Lookup Functions Inside ARRAYFORMULA
Use VLOOKUP across an entire column with ARRAYFORMULA:
This looks up every value in column A against the Products sheet and returns the matching result — all in one formula.
Note:
XLOOKUPdoes not work inside ARRAYFORMULA. UseVLOOKUPorINDEX/MATCHfor array-based lookups in Google Sheets.
The Google Forms Automation Use Case
When a Google Form submits responses to a Sheet, new rows are added at the bottom. Any formulas in adjacent columns stop one row above the new data — unless you use ARRAYFORMULA.
Best practices for Google Forms response sheets:
- Put all calculation formulas at the top of their column (row 2) as ARRAYFORMULA formulas.
- Lock the header row.
- Do not put any formulas in individual response rows.
Example: Auto-calculate response age in days:
Column A contains the timestamp from Form submissions. This formula shows how many days ago each response arrived.
ARRAYFORMULA with SUMIF and COUNTIF for Row-Level Summaries
ARRAYFORMULA doesn't work with functions that already return single values (like SUM of a range). But it works well with SUMIF and COUNTIF where the criteria changes per row.
Count how many times each name in column A appears in the full list:
Sum all revenue where the region matches the value in column B for each row:
Compatibility Matrix
Some functions handle arrays natively and do not need ARRAYFORMULA, while others are incompatible:
| Works with ARRAYFORMULA | Does NOT work |
|---|---|
IF, VLOOKUP, TRIM, LEN | XLOOKUP |
COUNTIF, SUMIF | SORT, UNIQUE |
LEFT, RIGHT, MID | FILTER |
UPPER, LOWER, TEXT | TRANSPOSE |
For SORT, UNIQUE, FILTER — these are already array-native functions in Google Sheets and return dynamic ranges on their own.
Performance Notes
ARRAYFORMULA with open-ended ranges (A2:A) calculates for the entire available sheet height. In sheets with many ARRAYFORMULA columns and large datasets (10,000+ rows), this can slow down the sheet. To improve performance:
- Bound the range to the actual data length:
A2:A1000 - Use QUERY for complex aggregations instead of multiple nested ARRAYFORMULAs
- Avoid multiple nested ARRAYFORMULA inside each other — they recalculate the entire range at each level