ARRAYFORMULA function in Google Sheets - Step-by-Step Guide

Introduction

ARRAYFORMULA lets you apply a formula to a whole range of cells at once. It saves time by avoiding the need to copy the same formula down many rows.

When you want to add two columns of numbers and get all results in one step.

When you need to multiply a list of prices by a tax rate without typing the formula repeatedly.

When you want to convert a list of names to uppercase all at once.

When you want to apply a condition to many rows and get results in a single formula.

When you want to avoid dragging formulas down manually in a large dataset.

Steps

Step 1: Click

- cell where you want the results to start

The cell is selected and ready for input

Step 2: Type

- the selected cell

The formula input cursor appears

💡 Start with =ARRAYFORMULA(

Step 3: Enter

- inside the ARRAYFORMULA parentheses

You write the formula that works on ranges, for example: A2:A10 + B2:B10

💡 Use ranges instead of single cells to apply the formula to many rows

Step 4: Type

- after the formula inside ARRAYFORMULA

Close the parentheses to complete the formula, for example: =ARRAYFORMULA(A2:A10 + B2:B10)

Step 5: Press

- Enter key

The formula runs and fills the cells below with results for each row

Before vs After

Before

Column C is empty and you have numbers in columns A and B from row 2 to 10

After

Column C shows the sum of columns A and B for each row from 2 to 10 using one ARRAYFORMULA

Settings Reference

Formula input

📍 Formula bar at the top of the sheet

To enter the ARRAYFORMULA with the desired calculation

Default: No default, user types formula

Common Mistakes

Using ARRAYFORMULA with single cells instead of ranges

It does not apply the formula to multiple rows and defeats the purpose

Use ranges like A2:A10 instead of single cells like A2

Not closing the parentheses of ARRAYFORMULA

The formula will show an error and not work

Always close the parentheses after your formula inside ARRAYFORMULA

Mixing ranges of different sizes inside ARRAYFORMULA

This causes errors because the formula expects ranges of the same length

Make sure all ranges inside ARRAYFORMULA cover the same number of rows

Summary

ARRAYFORMULA applies a formula to many rows at once using ranges.

It saves time by avoiding copying formulas down manually.

Remember to use ranges and close parentheses correctly.