Apply One Formula to a Whole Column With ARRAYFORMULA

Excel & Google Sheets

How it works

You put one formula in the top cell and the results spill down for every row of the range — one calculation, nine answers. Google Sheets needs the ARRAYFORMULA wrapper to switch the math into array mode; pressing Ctrl+Shift+Enter after typing the plain version wraps it for you. Excel 365 doesn't need a wrapper at all: dynamic arrays are the default, so =B2:B10*C2:C10 spills on its own. Either way, the cells below the formula must be empty — the spill needs the room, and anything in the way turns the whole result into an error.

ARRAYFORMULA(…)

Tells Sheets to run the calculation on whole ranges at once. Excel 365 does this automatically.

B2:B10*C2:C10

Multiplies each row's quantity by its unit price — row 2 with row 2, row 3 with row 3, and so on.

When to use it

Use it for any per-row calculation on a growing table: order line totals, invoice amounts, commission as deal value × rate. One formula at the top means new rows are covered without anyone remembering to fill down.

Common mistakes

• ✕Something already sits in the spill path.

  ✓A stray value in D5 blocks the array — Sheets shows #REF!, Excel shows #SPILL!. Clear the cells below the formula; only the top cell should contain anything.

• ✕Copying the ARRAYFORMULA down the column anyway.

  ✓Each copy tries to spill over the one above and everything errors. It lives in exactly one cell — the top of the column.

• ✕Typing the plain array version in older Excel.

  ✓=B2:B10*C2:C10 only spills in Excel 365/2021. Older Excel needs the formula copied to each row (=B2*C2, filled down) or a legacy Ctrl+Shift+Enter array.