Google Sheets lesson 6 of 11, Sheets power tools
ARRAYFORMULA: one formula for the whole column
Write the formula once at the top, and every row below gets its answer, including rows added later.
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
Most formulas work on one row and get filled down. ARRAYFORMULA lets a single formula work on whole ranges instead: =ARRAYFORMULA(B2:B6*C2:C6) multiplies each row's hours by its rate and fills the results down the column. There is only one formula to check and nothing to fill. In Sheets, press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) while editing a formula to wrap it in ARRAYFORMULA for you.
Pair it with an open-ended range to cover rows that don't exist yet: B2:B*C2:C. On its own, that returns 0 for every empty row down the sheet. Wrap the math in IF to leave those rows blank: =ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C)). When someone types a new task in column A, its cost appears.
The results need empty cells to land in. Type anything into the column below an ARRAYFORMULA and the whole result is replaced by an error until you clear it. Functions such as FILTER, SORT, UNIQUE and QUERY already return ranges, so they don't need the wrapper.
To keep the header and the formula together, build a small array with curly braces. A semicolon stacks items on top of each other: ={"Cost"; ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))} goes in the header cell and fills the column beneath it.
Syntax
=ARRAYFORMULA(B2:B6*C2:C6)One formula, one result per row=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))Every row, blank where column A is empty={"Cost";ARRAYFORMULA(B2:B6*C2:C6)}A header stacked on top of the resultsCtrl+Shift+EnterWraps the formula you are editing in ARRAYFORMULAWatch out for
- An open range without IF fills the rest of the column with zeros. Test column A for blanks first.
- Anything typed in the result area blocks the whole formula. Keep the column below it empty.
- Every range in the formula should start on the same row. B2:B with C3:C pairs each hours value with the next row's rate.
- This practice grid works out ranges even without the wrapper, so the exercises ask for ARRAYFORMULA by name. In Google Sheets, leave it off and you won't get the whole column of results.
The exercises
- Every cost in one formula. In D2, write one ARRAYFORMULA that multiplies the hours in B2:B6 by the rates in C2:C6, so every row from 2 to 6 gets its cost.
- Ready for new rows. In D2, write one ARRAYFORMULA with open-ended ranges that works out hours times rate for every row, and leaves the cost blank on rows where column A is empty.
- Header and formula together. Column D is already done. In E1, put the header "With tax" and, under it, each cost in column D plus the tax rate in G2. Use one formula in E1: an array that stacks the header on top of an ARRAYFORMULA, leaving empty rows blank.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Every cost in one formula
- Ready for new rows
- Header and formula together