# How to apply a formula to an entire column in Google Sheets

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.

**Google Sheets:** `=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))`

**Excel:** Excel has no ARRAYFORMULA and needs none: in Excel 2021 and later, `=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.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Item | Qty | Unit price | Total |
| Notebook | 12 | 2.5 |  |
| Stapler | 3 | 14 |  |
| Pens | 40 | 0.75 |  |
| Folder | 8 | 1.2 |  |
| Tape | 15 | 2.1 |  |
| Markers | 6 | 4.25 |  |

Formula in D2: `=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))`

Result (spills 46 × 1): $30.00; $42.00; $30.00; $9.60; $31.50; $25.50; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; 

## 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.

## Other ways

- `=ARRAYFORMULA(B2:B*C2:C)`: Without the IF, every empty row shows 0. (Zeros in every empty row below the data.)
- `=ARRAYFORMULA(B2:B7*C2:C7)`: A closed range covers only the rows it names; new rows below row 7 get nothing. (Stops at row 7.)
- `=B2:B7*C2:C7`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/apply-a-formula-to-an-entire-column