ARRAYFORMULA applies one expression across a range and returns multiple results. It is useful when you would otherwise copy the same formula down a column.
This blank-safe example doubles each price only when the item cell contains a value: =ARRAYFORMULA(IF(A2:A5="","",B2:B5*2)).
Apply One Formula to an Entire Column
Enter this sample data in A1:B5. The blank item row is intentional.
| Item | Price |
|---|---|
| Pen | 10 |
| 99 | |
| Pad | 15 |
| Clip | 20 |
=ARRAYFORMULA(IF(A2:A5="","",B2:B5*2))The formula entered in C2 returned 20, a blank, 30, and 40 across C2:C5.
Only C2 contains the formula. The cells below display the spilled results and update when a source value changes.
ARRAYFORMULA Function Syntax
=ARRAYFORMULA(array_formula)array_formula can be a range, a mathematical expression using ranges, or a function that returns more than one cell.
Google specifies equal-size ranges for expressions that combine multiple ranges. A shorter range can produce missing-value errors in unmatched rows.
Keep Blank Input Rows Blank
An open-ended calculation can fill unused rows with zeros, spaces, or labels. Test the key input column before running the calculation.
=ARRAYFORMULA(IF(A2:A="","",B2:B*2))The first IF branch returns an empty string for unused rows. The second branch performs the row-by-row calculation.
Multiply or Combine Two Columns
For a numeric example, put 1, 2, and 3 in J2:J4 and 10, 20, and 30 in K2:K4. Then use:
=ARRAYFORMULA(J2:J4*K2:K4)Each output row uses the source values from the same row. Both ranges should cover the same rows.
To join first and last names in E2:F4, the tested formula was:
=ARRAYFORMULA(E2:E4&" "&F2:F4)It returned Mina Shah, Omar Diaz, and Lena Park in G2:G4.
Use ARRAYFORMULA With IF and Text Functions
With the sample item table, this formula labels prices of 15 or more as Higher while leaving unused rows empty:
=ARRAYFORMULA(IF(A2:A5="","",IF(B2:B5>=15,"Higher","Lower")))The outer IF controls blank rows. The inner IF evaluates each populated price.
Text functions can work the same way. For example, =ARRAYFORMULA(LEN(A2:A5)) returns one character count for each source cell.
The same blank-safe pattern can calculate percentages. With the sample price table, =ARRAYFORMULA(IF(A2:A5="","",B2:B5*10%)) returns 1, a blank, 1.5, and 2.
Fix Spill and Range-Size Errors
Every spilled output cell must be empty. In the test, content in C8 blocked an ARRAYFORMULA entered in C7 and produced #REF!.
Clear the occupied cell or move the formula to an area with enough space.
Range-size mismatches behave differently. This tested formula combined four rows with three rows:
=ARRAYFORMULA(J2:J5+K2:K4)Sheets returned 11, 22, and 33 for the matched rows. The fourth result was #N/A because K5 had no paired value.
When You Do Not Need ARRAYFORMULA
Many modern functions return arrays by themselves. The tested formula =SEQUENCE(3) filled three rows with 1, 2, and 3.
Functions such as FILTER, UNIQUE, and VSTACK also spill their results without an ARRAYFORMULA wrapper.
Google’s ARRAYFORMULA reference also notes that Ctrl+Shift+Enter adds the wrapper while you edit a formula.
Other Google Sheets articles you may also like