If you’ve ever found yourself dragging formulas down hundreds of rows in Google Sheets, you’re going to love ARRAYFORMULA. It’s a powerful function that lets you apply a formula to an entire column (or array) of data — all in one go. It’s perfect for beginners looking to save time and reduce errors in large spreadsheets.
In this guide, you’ll learn exactly what ARRAYFORMULA does, why it’s so useful, and how to use it step-by-step — no coding or advanced skills required. By the end, you’ll be using it like a pro to automate your calculations and clean up your spreadsheet workflows.
What Is ARRAYFORMULA in Google Sheets?
ARRAYFORMULA lets you perform calculations over a range of cells at once, rather than one cell at a time. It replaces the need to copy and paste formulas down a column. Think of it as “bulk processing” for formulas.
Here’s the basic syntax:
=ARRAYFORMULA(expression)
The expression usually includes ranges instead of individual cells, like A2:A + B2:B instead of A2 + B2.
Real-Life Example: Calculating Total Price for an Online Store
Let’s say you’re managing a small online shop in Google Sheets. You have a list of products with quantities and unit prices, and you want to calculate the total price for each product — without writing a formula in every row.
Sample Data
| Product | Quantity | Price Per Unit | Total Price |
|---|---|---|---|
| Notebook | 2 | 5 | |
| Pen | 10 | 1 | |
| Mug | 3 | 8 |
In D2, you can enter this formula:
=ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C))
This formula will automatically calculate the total price for all rows — even if new ones are added later!
Step-by-Step Instructions: How to Use ARRAYFORMULA
- Start with your basic formula, e.g.
=B2 * C2. - Replace the individual cell references with ranges, like
B2:B * C2:C. - Wrap it in
ARRAYFORMULA():
=ARRAYFORMULA(B2:B * C2:C) - Use
IF()to prevent calculating empty rows:
=ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C))
Advanced Techniques Not Available in Excel
Google Sheets allows array outputs directly into the sheet — a flexibility that Excel still lacks unless you use Office 365 with dynamic arrays.
1. Combine ARRAYFORMULA with TEXT functions
=ARRAYFORMULA("Product: " & A2:A & " | Total: $" & B2:B * C2:C)
2. Use ARRAYFORMULA with IF and ISBLANK for smarter logic
=ARRAYFORMULA(IF(ISBLANK(A2:A), "", B2:B * C2:C))
3. Dynamic date calculations
=ARRAYFORMULA(IF(A2:A="", "", TODAY() - A2:A))
This calculates the number of days since each date in column A — perfect for tracking overdue tasks or orders.
Common Use Cases for Beginners
- Apply formulas across hundreds or thousands of rows
- Auto-calculate totals, dates, or statuses
- Clean data using TRIM, UPPER, LOWER functions
- Generate dynamic text strings or email templates
- Create conditional logic for data validation
Pro Tips
- Always include a condition like
IF(A2:A="", "", ...)to avoid extra zeros or errors. - Use
LEN()inside ARRAYFORMULA to filter out blank rows. - Combine with
FILTERandQUERYfor powerful data pipelines. - It’s case-sensitive — use
LOWER()orUPPER()to standardize.
Quick ARRAYFORMULA Cheat Sheet
| Use Case | Formula |
|---|---|
| Basic multiplication | =ARRAYFORMULA(B2:B * C2:C) |
| Conditional output | =ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C)) |
| Text + formula | =ARRAYFORMULA("Total: $" & B2:B * C2:C) |
| Date difference | =ARRAYFORMULA(TODAY() - A2:A) |
| Uppercase names | =ARRAYFORMULA(UPPER(A2:A)) |
ARRAYFORMULA is one of the most useful tools for beginners in Google Sheets. It lets you do more, faster — without copying formulas down row by row. Whether you’re running an online business, organizing classroom data, or managing tasks, it can transform the way you work with spreadsheets.
Now that you know the basics (and some powerful tricks), try applying ARRAYFORMULA in your next sheet. It’s an absolute game-changer!