Have you ever wished you could apply a formula to an entire column in Google Sheets—without copying it down row by row? That’s exactly what ARRAYFORMULA does! It’s one of the most powerful and time-saving functions in Google Sheets, especially for beginners who want to streamline their spreadsheets.
Whether you’re calculating totals, cleaning up data, or combining columns, ARRAYFORMULA helps you apply formulas across multiple rows automatically. In this guide, we’ll break it down step by step, using clear examples and real-life use cases.
What is ARRAYFORMULA?
ARRAYFORMULA is a Google Sheets function that allows you to perform calculations on entire ranges (arrays) of data at once, instead of writing a formula for each individual cell. It’s perfect for saving time, reducing repetition, and building dynamic spreadsheets.
Basic syntax:
=ARRAYFORMULA(array_formula)When Should You Use ARRAYFORMULA?
- To apply a formula across an entire column or row
- To combine or transform data sets automatically
- To eliminate the need for dragging formulas down manually
- To create cleaner and more scalable spreadsheets
Real-Life Example: Add Sales Tax to Product Prices
Let’s say you manage a small online store and have a list of product prices. You want to calculate the final price including 10% tax, but don’t want to type the formula for every row.
Sample Data:
| Product | Price | Final Price (w/ Tax) |
|---|---|---|
| Notebook | 5 | =ARRAYFORMULA(IF(B2:B=””, “”, B2:B * 1.1)) |
| Pen | 2 | |
| Bag | 20 |
With =ARRAYFORMULA(IF(B2:B="", "", B2:B * 1.1)), the tax-inclusive price is calculated for every row without needing to copy the formula manually!
Using ARRAYFORMULA with Other Functions
ARRAYFORMULA works well with other functions to automate your sheets even more:
1. Combine First and Last Names
=ARRAYFORMULA(A2:A & " " & B2:B)This merges two columns of names into one.
2. IF Conditions with Arrays
=ARRAYFORMULA(IF(C2:C="Yes", "Approved", "Pending"))This checks for a “Yes” and returns “Approved” or “Pending” across all rows.
3. DATE Calculations
=ARRAYFORMULA(DAYS(TODAY(), A2:A))Calculates the number of days since each date in column A.
Best Practices When Using ARRAYFORMULA
- Always make sure your ranges align (e.g., A2:A and B2:B are the same length)
- Wrap formulas inside
IF(A2:A="", "", ...)to prevent unwanted outputs on blank rows - Use headers and put the ARRAYFORMULA in row 1 or 2 to keep it structured
- Be careful with very large data sets—ARRAYFORMULA can slow things down if overused
Common Mistakes to Avoid
- Forgetting IF wrapper: Without it, the formula may return 0s or errors in empty rows
- Incorrect ranges: Make sure the columns match in length
- Using functions that don’t support arrays: Not all functions work natively with ARRAYFORMULA
Quick-Reference Cheat Sheet
| Task | Formula |
|---|---|
| Apply formula to entire column | =ARRAYFORMULA(B2:B * 1.1) |
| IF condition across rows | =ARRAYFORMULA(IF(B2:B=”Yes”, “Done”, “Pending”)) |
| Combine text columns | =ARRAYFORMULA(A2:A & ” – ” & B2:B) |
| Prevent blank outputs | =ARRAYFORMULA(IF(A2:A=””, “”, A2:A * 2)) |
| Count days since date | =ARRAYFORMULA(DAYS(TODAY(), A2:A)) |
ARRAYFORMULA is a game-changer for anyone who wants to make their Google Sheets more efficient, smarter, and less repetitive. Once you get the hang of it, you’ll wonder how you ever managed without it. It’s the perfect tool to elevate your spreadsheets with minimal effort—just one formula can replace hundreds of rows of manual input.
Next time you’re building a sheet, try ARRAYFORMULA and watch your data come to life!