ARRAYFORMULA in Google Sheets: What It Is and How to Use It

ARRAYFORMULA in Google Sheets lets you apply a formula to a whole range of cells at once instead of one cell at a time. It automatically expands the formula’s result across multiple rows or columns, saving time and effort when working with lists or tables.

How It Works

Think of ARRAYFORMULA as a magic tool that copies a formula down a whole column or across a row without you having to drag it manually. Instead of writing the same formula in each cell, you write it once, and ARRAYFORMULA fills in the rest.

It works by taking a formula that normally works on a single cell and making it work on an entire range of cells. For example, if you want to multiply two columns of numbers, ARRAYFORMULA lets you do this in one step instead of repeating the multiplication for each row.

This is like having a recipe that automatically adjusts the ingredients for any number of servings, instead of recalculating each time you change the number.

Example

This example shows how to multiply values in column A by values in column B for multiple rows using ARRAYFORMULA. Instead of writing the formula in each row, you write it once and get all results at once.

google_sheets

=ARRAYFORMULA(A2:A5 * B2:B5)

Output

If A2:A5 = [2, 4, 6, 8] and B2:B5 = [3, 5, 7, 9], output will be [6, 20, 42, 72]

A	B	Result
2	3	6
4	5	20
6	7	42
8	9	72

When to Use

Use ARRAYFORMULA when you want to apply the same calculation or function to many rows or columns without copying the formula repeatedly. It is perfect for tasks like:

• Calculating totals or differences for lists of numbers
• Combining or transforming data across columns
• Automatically updating results when new data is added

This saves time and reduces errors because you only write the formula once, and it updates dynamically as your data changes.

Key Points

• ARRAYFORMULA applies a formula to an entire range at once.
• It helps avoid copying formulas row by row.
• Works well with arithmetic, text, and many Google Sheets functions.
• Automatically updates when data changes or grows.
• Must be used carefully to avoid overwriting existing data.

Key Takeaways

ARRAYFORMULA lets you apply one formula to many cells at once.

It saves time by avoiding manual copying of formulas down rows or across columns.

Use it for calculations that involve entire columns or ranges.

It updates automatically when your data changes or grows.

Be careful not to overwrite data when using ARRAYFORMULA.