Google Sheets ARRAYFORMULA: Full Column Calculation Without Dragging
ARRAYFORMULA enables single spreadsheet formulas to output an entire matrix or column of calculated results automatically. When new rows are entered, ARRAYFORMULA calculates them instantly without requiring manual formula dragging.
=ARRAYFORMULA(array_formula)Technical Overview & Engine Mechanics
Formula Comparison: ARRAYFORMULA vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| array_formula | Expression | Required | A range or mathematical expression using range coordinates (e.g., A2:A * B2:B). |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Calculate Line Item Totals Down Entire Column
=ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C * (1 - D2:D)))Example 2: Concatenate First and Last Names with Title Case
=ARRAYFORMULA(IF(A2:A="", "", PROPER(TRIM(A2:A) & " " & TRIM(B2:B))))Example 3: Dynamic Age Calculation from Birthdates
=ARRAYFORMULA(IF(A2:A="", "", DATEDIF(A2:A, TODAY(), "Y")))Example 4: Automated Line Item Subtotal with Tax and Discount
=ARRAYFORMULA(IF(A2:A="", "", (B2:B * C2:C) * (1 - D2:D) * 1.08))Example 5: Batch Full Name Concatenation with Proper Formatting
=ARRAYFORMULA(IF(A2:A="", "", PROPER(TRIM(A2:A)) & ", " & PROPER(TRIM(B2:B))))Common Errors & Solutions
Performance Pro Tips
- Use Ctrl+Shift+Enter (Cmd+Shift+Enter on Mac) while editing any formula to wrap it in ARRAYFORMULA instantly.
- Certain aggregation functions like AND, OR, and SUM do not work inside ARRAYFORMULA; use multiplication `*` for AND, addition `+` for OR.
Frequently Asked Questions: ARRAYFORMULA
What shortcut wraps an existing formula in ARRAYFORMULA?
Select the formula in the formula bar and press Ctrl + Shift + Enter (Windows) or Cmd + Shift + Enter (Mac).
Why does my ARRAYFORMULA fill down to row 50,000 with zeros?
Always wrap your calculation in an IF check: IF(A2:A="", "", calculation). This prevents formulas from evaluating empty trailing rows.
Can ARRAYFORMULA be used with VLOOKUP?
Yes. Passing a range as the lookup key (e.g., VLOOKUP(A2:A, Table, 2, FALSE)) returns lookups for every row automatically.
Which functions are incompatible with ARRAYFORMULA?
Aggregate functions like SUM, AVERAGE, COUNT, AND, and OR do not iterate row-by-row inside ARRAYFORMULA. Use BYROW or MAP instead for these operations.
Does ARRAYFORMULA improve sheet performance?
Yes. Having one master ARRAYFORMULA uses far less browser memory than copying 20,000 individual formulas into separate cells.
Related Google Sheets Formulas
QUERY
The QUERY function is Google Sheets' most versatile formula, executing a SQL-like Google Visualization API Query Language string across spreadsheet tables to filter, aggregate, group, pivot, and sort data in a single formula.
Data TransformationFILTER
FILTER dynamically outputs only the rows or columns from a source range that satisfy one or more logical conditions. As source data updates, filtered results re-calculate automatically.
Data TransformationCHOOSEROWS
CHOOSEROWS extracts specific rows from an array or range by their index numbers. Supports negative indexing to pull rows from the bottom of a dataset.