FormulaCraft

How to use ARRAYFORMULA to apply a formula to a whole column in Excel and Google Sheets

Topic:ARRAYFORMULA (Google Sheets)

Heads up: Excel and Google Sheets do this differently.

Excel
=B2:B6*C2:C6
Google Sheets
=ARRAYFORMULA(B2:B6*C2:C6)

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1In Google Sheets, enter your formula once in the first result cell wrapped in ARRAYFORMULA: =ARRAYFORMULA(B2:B6*C2:C6).
  2. 2The result spills into all rows of the range automatically — do not enter anything in the rows below.
  3. 3In Excel 365+, just enter =B2:B6*C2:C6 in the first cell and the result spills automatically (no wrapper needed).
  4. 4In older Excel versions, enter the formula and press Ctrl+Shift+Enter to create a traditional array formula.

Tips

Need the Google Sheets version instead? Open Sheets variant in workspace →

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

Why does ARRAYFORMULA not exist in Excel?

Excel 365 introduced dynamic arrays that spill natively, making an ARRAYFORMULA wrapper unnecessary. Older Excel used Ctrl+Shift+Enter for legacy array formulas.

Can ARRAYFORMULA work with IF and COUNTIF?

Yes. For example =ARRAYFORMULA(IF(B2:B6>100,"High","Low")) applies the IF across all rows simultaneously in Google Sheets.

More on ARRAYFORMULA (Google Sheets)

See all →
Reference

ARRAYFORMULA (Google Sheets)

Apply a formula down an entire column at once in Google Sheets.

How-to

How to split text down a whole column with ARRAYFORMULA

Use ARRAYFORMULA with SPLIT to break delimited text across an entire column in one formula in Google Sheets.

Error fix

Fix ARRAYFORMULA

ARRAYFORMULA not working in Google Sheets? Learn why it stops expanding, returns one row, or shows an error — and how to fix it.

Formulas used

ARRAYFORMULA (Google Sheets)FILTERMAPINDEXABSORCHOOSEROWSCHOOSECOLS

Related tasks

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: