How to use ARRAYFORMULA in Google Sheets
Learn how to use ARRAYFORMULA in Google Sheets to apply one formula across an entire range without copying it into every cell.
Quick answer
To use ARRAYFORMULA in Google Sheets, type =ARRAYFORMULA( followed by your formula, then close the parenthesis and press Enter. You can also press Ctrl+Shift+Enter (Cmd+Shift+Enter on Mac) while editing a formula to add ARRAYFORMULA automatically, letting one formula calculate results across an entire range instead of copying it down.
Steps at a glance
- Select the cell where results should start
- Type =ARRAYFORMULA( then your formula or calculation
- Reference full ranges instead of single cells
- Close the parenthesis and press Enter
- Or press Ctrl+Shift+Enter to auto-wrap ARRAYFORMULA
- Check that results fill down the range correctly
- Adjust ranges if you see a #REF! error
Summary
ARRAYFORMULA lets you apply a single formula across an entire range of cells instead of writing and copying a separate formula into each row. It's especially useful for calculations, lookups, and conditional logic that would otherwise require dragging a fill handle down hundreds of rows.
Step-by-step guide
Step 1
Understand what ARRAYFORMULA does
ARRAYFORMULA takes a calculation that normally works on one cell and expands it to output results across a whole range of cells at once. Instead of writing =A2*B2 and copying it down 500 rows, you write one formula that calculates all 500 results simultaneously.
Step 2
Select the cell where results should start
Click the top-left cell of the range where you want your results to appear. This is usually the first row of data, since ARRAYFORMULA will automatically fill downward and across based on the ranges you reference.
Step 3
Write a basic ARRAYFORMULA
Type =ARRAYFORMULA( and then enter a mathematical expression or function using full ranges instead of single cell references, such as A2:A100*B2:B100. Close the formula with a matching parenthesis and press Enter to see results populate the entire range instantly.
Step 4
Use the keyboard shortcut to auto-insert ARRAYFORMULA
While typing a formula in the formula bar, press Ctrl+Shift+Enter on Windows or Cmd+Shift+Enter on a Mac. Google Sheets automatically wraps your formula in ARRAYFORMULA( ) for you, saving you from typing it manually every time.
Step 5
Combine ARRAYFORMULA with other functions
ARRAYFORMULA works well with SUM, IF, and lookup functions like the one described in the guide to /guides/google-sheets/use-vlookup, letting you return calculated results across many rows in one step. For conditional logic across a range, pair it with the approach in /guides/google-sheets/use-if-formula.
Step 6
Confirm the formula fills the range
After pressing Enter, check that values appear in every row of your intended output range, not just the first cell. If a range is highlighted with a blue border showing the array's expanded output, the formula is working correctly.
Step 7
Save and adjust as your data grows
Google Sheets saves automatically, but revisit your ARRAYFORMULA if you add new rows below the original range, since it won't extend on its own. Update the range references to cover new data, or build the formula around a larger buffer range from the start.
Why this matters
You're building a spreadsheet that needs the same calculation repeated down hundreds of rows, and manually copying formulas keeps breaking references or missing new rows. Learning ARRAYFORMULA lets you write the logic once, apply it instantly across a full column, and keep your sheet accurate as data grows.
Frequently asked questions
What's the difference between ARRAYFORMULA and a regular formula?
A regular formula like =A2*B2 calculates one result for one cell, and you'd normally copy it down manually. ARRAYFORMULA calculates results for an entire range in a single formula, so you don't need to duplicate it row by row.
Can I edit individual cells within an ARRAYFORMULA result range?
No, only the top-left cell contains the actual formula, and the rest of the range shows spilled results. To change the output, edit the original ARRAYFORMULA cell rather than the cells it fills.
Does ARRAYFORMULA work with functions like SUMIF or VLOOKUP?
Yes, ARRAYFORMULA can wrap around most functions, including SUMIF and VLOOKUP, to return multiple results at once instead of one value per formula. This is especially useful when applying a lookup across an entire column of data.
Can I use ARRAYFORMULA on a whole column like A:A?
Yes, but referencing entire columns can slow down large spreadsheets since Sheets calculates every row, including empty ones. It's usually faster to reference a defined range like A2:A1000 instead.
Create interactive demos like this one — free, no coding required.
Start for free →