How to apply a formula to an entire column in Google Sheets
Google Sheets formula
=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))Write it once in the top row with open ranges and ARRAYFORMULA: =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)) fills every row, including rows added later.
- Result on the example
- a 46-row list that spills from D2 in
D2 - Excel
- See below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
An open range like B2:B runs from row 2 to the bottom of the sheet, so the formula covers every row there is and every row added later. ARRAYFORMULA makes the multiplication run row by row and fill the column below. The IF test returns an empty string where column A is empty, so the column doesn't fill up with zeros.
=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))Result{46 rows × 1 col} 30, 42, 30, 9.6, …
A2:A46 rows × 1 col: "Notebook", "Stapler", "Pens", "Folder", …A range: 46 cells in one column.
A2:A=""{46 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
B2:B46 rows × 1 col: 12, 3, 40, 8, …A range: 46 cells in one column.
C2:C46 rows × 1 col: $2.50, $14.00, $0.75, $1.20, …A range: 46 cells in one column.
B2:B*C2:C{46 rows × 1 col} 30, 42, 30, 9.6, …The * operator multiplies.
IF(A2:A="","",B2:B*C2:C){46 rows × 1 col} 30, 42, 30, 9.6, …IF: Returns one value if a test is true, another if false.- logical_test
A2:A=""{46 rows × 1 col} FALSE, FALSE, FALSE, FALSE, … - value_if_true
"" - value_if_false
B2:B*C2:C{46 rows × 1 col} 30, 42, 30, 9.6, …
- logical_test
ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)){46 rows × 1 col} 30, 42, 30, 9.6, …ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.- array_formula
IF(A2:A="","",B2:B*C2:C){46 rows × 1 col} 30, 42, 30, 9.6, …
- array_formula
In Excel
=B2:B7*C2:C7 spills on its own. To cover new rows, format the data as a Table (Ctrl+T), where a formula in one row fills the whole column.Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =ARRAYFORMULA(B2:B*C2:C) | spills 46 × 1Different result | Without the IF, every empty row shows 0. Zeros in every empty row below the data. |
| =ARRAYFORMULA(B2:B7*C2:C7) | spills 6 × 1Different result | A closed range covers only the rows it names; new rows below row 7 get nothing. Stops at row 7. |
| =B2:B7*C2:C7Excel only | spills 6 × 1Different result | The Excel version: dynamic arrays spill without a wrapper. Covers rows 2 to 7 only. |
Common mistakes
- Anything typed below the formula, even an old fill-down formula, blocks it with #REF! "Array result was not expanded". Clear the column first.
- Don't type over a single result cell in the filled column; that also breaks the whole array.
- Functions that already return arrays, like FILTER, SORT and QUERY, don't need ARRAYFORMULA.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Apply a formula to an entire column
D2, write one formula that multiplies Qty by Unit price for every row of the sheet, including rows added later, and leaves empty rows blank.Questions
Is there a shortcut for ARRAYFORMULA?
Press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) while editing the formula and Sheets wraps it for you.
Does this work with Google Forms responses?
Yes. Open ranges pick up new response rows. Put the formula in a separate column, or on another tab that refers to the responses.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.