Column-wide IF
=ARRAYFORMULA(IF(LEN(A2:A)=0, "", A2:A*1.1)) applies a 10% markup down column B only where column A has data.
What is ARRAYFORMULA in Google Sheets?
ARRAYFORMULA tells Google Sheets to treat a formula as an array operation so one expression fills many cells at once. It is common with IF, VLOOKUP, and math on full columns before dynamic arrays made many patterns automatic. It still matters for legacy sheets and explicit control.
When to use it
Use ARRAYFORMULA when you need one formula copied down thousands of rows without drag-fill, or when combining with functions that do not auto-spill in older workbooks.
When to skip it
Skip ARRAYFORMULA when a native dynamic array function like FILTER, MAP, or BYROW already spills correctly. New sheets often need only the inner formula.
How it works
-
1
Write the logic once for a single row, using relative references where the row number should change down the column.
-
2
Wrap the expression in ARRAYFORMULA(...) so Google Sheets evaluates it across the whole target range.
-
3
Place the formula in the top cell of the output column and leave room for the spill.
-
4
Pair with IF or LEN to blank rows where source cells are empty so you do not show zeros on blank lines.
-
5
Avoid volatile references inside huge ARRAYFORMULA columns because every recalc hits the full range.
-
6
Test on a small slice before pointing at entire columns on 50k-row tabs.
Examples in Google Sheets
Lookup every row
=ARRAYFORMULA(IF(LEN(C2:C)=0, "", VLOOKUP(C2:C, Prices!A:B, 2, FALSE))) fills prices for a SKU list without copying.
Running flag
=ARRAYFORMULA(IF(ROW(A2:A)=ROW(A2), "Start", IF(A2:A>A1:A, "Up", "Down"))) compares each row to the row above across the column.
Build this without starting from a blank cell
Use a Better Sheets tool for ARRAYFORMULA, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Nesting ARRAYFORMULA twice, which often returns unexpected single-cell results or errors.
- Placing ARRAYFORMULA over cells that already contain values, blocking the spill.
- Using entire column references A:A inside heavy lookups on very large sheets, slowing every edit.
- Expecting ARRAYFORMULA to fix a #REF! from a bad range size in the inner function.
- Forgetting to wrap only the part that must expand, making debugging harder than needed.
Frequently asked questions
- Is ARRAYFORMULA still needed?
- Many newer functions spill automatically. ARRAYFORMULA remains useful for older patterns and explicit control over legacy formulas.
- Why do I see one result only?
- The inner function may not return an array, or another ARRAYFORMULA conflict sits in the spill path.
- ARRAYFORMULA vs drag-fill?
- ARRAYFORMULA keeps one formula to maintain. Drag-fill creates thousands of separate formulas that are harder to update.
- Can ARRAYFORMULA work across sheets?
- Yes. Reference another tab inside the wrapped expression the same way as a normal formula.
- Does ARRAYFORMULA work with QUERY?
- Rarely together. QUERY already returns a table. Use one or the other for the output block.
- How do I stop at the last row?
- Wrap with IF(LEN(source)=0, "", ...) or use FILTER and dynamic arrays instead of filling blanks manually.
- ARRAYFORMULA and blank cells?
- Math on blanks may become zero. Test with LEN or ISBLANK guards in the wrapped formula.
Related Tutorials
Watch how ARRAYFORMULA works
Replace 1,000 Formulas Instantly
Spreadsheet Automation 101 Lesson 2: Arrays
Don't Delete My Formulas
Member Asks: How do I calculate from a set of Form entries?
Your First Formula
How to Iterate Counting Blank Cells in Google Sheets
Related blog posts
Guides that explain ARRAYFORMULA in more depth.
How to create a formula for every row in a column in a Google Spreadsheet?
3 possible ways to create a formula for every row in a column in a Google Spreadsheet. And two bonus ways to make it look nicer. - Auto fill - Double Click - ArrayFormula
Read post →How to Gain Confidence in Google Sheets
Boost your confidence, master formula usage and overcome errors in this step-by-step guide. Join us to demystify spreadsheets.
Read post →Protect Your Formulas from Accidental Deletion in Google Sheets
Ever spent hours crafting the perfect formula in Google Sheets, only to accidentally delete it? It's a frustrating experience that can derail your productivity. Fear not! In this guide, we'll unveil some clever tricks to safeguard your hard work.
Read post →Related glossary terms
QUERY
QUERY runs a SQL-like statement against a range of cells and returns a new table of results. You pass a data range, a query string with SELECT, WHERE, ORDER BY, and optional headers. It is the fastest way to filter, sort, group, and pivot sheet data without helper columns.
Read guide →LAMBDA
LAMBDA defines an anonymous function inside a formula. You name parameters, write the calculation, then pass arguments when you call it. Combined with MAP, BYROW, or a named LAMBDA in the Name manager, it replaces fragile copy-paste logic across big tables.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →MAP
MAP walks aligned arrays element by element and applies a LAMBDA to each position. Pass one array for unary transforms or several arrays for element-wise combinations. It is the modern replacement for many ARRAYFORMULA column operations.
Read guide →Done reading about ARRAYFORMULA?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.