How to Use ARRAYFORMULA in Google Sheets Easily

Use ARRAYFORMULA in Google Sheets to apply a formula to an entire range of cells at once instead of copying it down manually. Wrap your formula inside ARRAYFORMULA() and reference ranges instead of single cells to get results for all rows automatically.

Syntax

The ARRAYFORMULA function has a simple syntax:

• ARRAYFORMULA(array_formula)

Here, array_formula is the formula or expression you want to apply over a range of cells. Instead of calculating one cell, it calculates multiple cells at once.

You usually use ranges (like A2:A10) inside it instead of single cells (like A2).

plaintext

ARRAYFORMULA(array_formula)

Example

This example shows how to multiply values in column A by 2 and get all results in column B using ARRAYFORMULA. Instead of typing the formula in each row, you write it once:

google_sheets

=ARRAYFORMULA(A2:A6 * 2)

Output

If A2:A6 contains 1, 3, 5, 7, 9, then output will be 2, 6, 10, 14, 18 in the cells where the formula is entered.

Common Pitfalls

Common mistakes when using ARRAYFORMULA include:

• Not using ranges inside the formula, which causes it to return only one value.
• Placing the formula in a cell that overlaps the output range, causing a "#REF!" error.
• Mixing ARRAYFORMULA with functions that do not support arrays well.

Example of wrong and right usage:

google_sheets

Wrong: =ARRAYFORMULA(A2 * 2)
Right: =ARRAYFORMULA(A2:A10 * 2)

Quick Reference

Feature	Description
Purpose	Apply a formula to an entire range at once
Input	Ranges or arrays, not single cells
Output	Array of results matching input size
Common Use	Replace dragging formulas down
Error Cause	Output range overlap or wrong input

Key Takeaways

Use ARRAYFORMULA to apply formulas to whole ranges without copying down.

Always use ranges inside ARRAYFORMULA, not single cells.

Place the formula where it won't overwrite output cells to avoid errors.

ARRAYFORMULA works well with arithmetic and many functions that support arrays.

It saves time and keeps your sheet cleaner by reducing repeated formulas.