How to Use ARRAYFORMULA in Google Sheets

Write one formula that fills an entire column — and keeps filling as new rows arrive — instead of dragging the same formula down forever. This is one of the most useful habits in Google Sheets.

By the Quiriz Team · Published July 25, 2026 · 4 min read

The quick version: instead of dragging =A2*B2 down a thousand rows, write =ARRAYFORMULA(A2:A * B2:B) once. It fills the whole column and keeps filling as rows arrive. Here's how it works.

From one cell to a whole column

Start with the single-row formula — say revenue = quantity × price: =A2*B2. Now swap the single cells for column ranges and wrap it:

=ARRAYFORMULA(A2:A * B2:B)

Put that in the first data row and it computes every row below at once. Add new rows and they fill automatically — nothing to re-drag.

The shortcut

Type the range version (=A2:A*B2:B) and press Ctrl+Shift+Enter. Sheets wraps it in ARRAYFORMULA for you.

Don't fill the blank rows

By default ARRAYFORMULA shows 0 (or errors) on empty rows. Guard it with IF so blanks stay blank:

=ARRAYFORMULA(IF(A2:A="", "", A2:A * B2:B))

Where it shines

Any per-row calculation that would otherwise be dragged: totals, concatenating first + last name (=ARRAYFORMULA(A2:A&" "&B2:B)), tax columns, status flags. One formula, always current.

Analyze your Sheets data with AI

Ask questions about your Google Sheets in plain English and get answers in the cell with =QUIRIZ(). Free to start.

Try Quiriz free →

Frequently asked questions

What does ARRAYFORMULA do in Google Sheets?

ARRAYFORMULA applies a formula across an entire range at once, so a single formula in one cell fills a whole column. It also expands automatically as new rows are added, so you never re-drag.

How do I apply a formula to an entire column without dragging?

Wrap a range-based formula in ARRAYFORMULA — for example =ARRAYFORMULA(A2:A * B2:B) in the first data cell fills the whole column. As new rows are added below, the result extends on its own.

What is the ARRAYFORMULA keyboard shortcut?

Type your formula using ranges (like A2:A*B2:B) and press Ctrl+Shift+Enter. Google Sheets automatically wraps it in ARRAYFORMULA.

How do I stop ARRAYFORMULA filling blank rows?

Add an IF that checks the input: =ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B)) leaves the result blank on empty rows instead of showing 0.