ARRAYFORMULA practice in Google Sheets

Run one formula over a whole column: multiply two columns, test every row with IF and join columns, with one formula in the top cell.

Start the first exercise

How ARRAYFORMULA works in Google Sheets

ARRAYFORMULA lets one formula work on a whole range at once, instead of one cell at a time. It takes a single argument: a formula that uses ranges where you would normally use one cell. Type it in the top cell and the results fill the cells below on their own, so there is nothing to copy down.

=ARRAYFORMULA(B2:B5*C2:C5) multiplies Units by Price for rows 2 to 5 and shows all four totals in D2:D5. IF works on one cell at a time, so it needs the wrapper too: =ARRAYFORMULA(IF(B2:B5>=50,"Pass","Fail")) tests every score in one go.

To make the formula keep up as rows are added, use an open range like B2:B, and wrap the formula in IF so empty rows stay empty. The results can't be overwritten: if a cell in the way is filled, you get an error until you clear it. Press Ctrl+Shift+Enter while typing and Sheets adds ARRAYFORMULA for you. The exercises below cover multiplying two columns, IF on every row and joining two columns.

  1. 1One formula for a whole columnIn D2, use ARRAYFORMULA to multiply Units by Price for every row with one formula.
  2. 2IF for every rowPremiumIn C2, use ARRAYFORMULA with IF to write "Pass" for every score of 50 or more and "Fail" for the rest.
  3. 3Join two columns for every rowPremiumIn C2, use ARRAYFORMULA to join each first and last name with a space between them.

Functions in this topic

Questions

How do I apply a formula to an entire column in Google Sheets?

Use ARRAYFORMULA with an open-ended range. Typed in D2, =ARRAYFORMULA(B2:B*C2:C) fills the column and grows with your data. Empty rows would show 0, so write =ARRAYFORMULA(IF(B2:B="","",B2:B*C2:C)) to keep them blank. You can also double-click the fill handle to copy a normal formula down.

Why does ARRAYFORMULA say the array result was not expanded?

Another cell is in the way. An array formula needs empty cells below it, and you get a #REF! error if even one of them holds a value. The message names the cell, for example D3. Clear that cell, or move the formula to a column that is empty below the first row.

Can I use ARRAYFORMULA with VLOOKUP?

Yes. =ARRAYFORMULA(VLOOKUP(A2:A10,F2:G20,2,FALSE)) looks up every value in A2:A10 at once and shows the matches in a column. Put the formula in the top cell only, and leave the cells below it empty. It is handy for filling a whole column of prices or names from a lookup table.