ARRAYFORMULA in Google Sheets

Google Sheets has many hidden gems, and one of them is ARRAYFORMULA(). If you are tired of dragging formulas down again and again, this function is for you. It helps you apply a formula to an entire column or range at once, saving time and avoiding manual work.

In this article, we will learn what ARRAYFORMULA does, how to use it, and see practical MIS examples.

---

What is ARRAYFORMULA?

ARRAYFORMULA() allows you to perform calculations or apply formulas to an entire column/range instead of writing the formula in every row.

👉 Without ARRAYFORMULA – You type formula in one cell and then drag it down.
👉 With ARRAYFORMULA – You write formula only once, and it automatically works for all rows.

---

Syntax

=ARRAYFORMULA(array_formula)

Where array_formula can be:

• A range (like A2:A10)
• A mathematical operation (like A2:A10 * 2)
• Another function (like IF, VLOOKUP, IMPORTRANGE etc.)

---

Example 1: Simple Calculation

You have numbers in column A and want to multiply them by 10.

Normal way:

=B2*10

(then drag down)

Using ARRAYFORMULA:

=ARRAYFORMULA(A2:A * 10)

👉 This will multiply all values in column A by 10 at once.

---

Example 2: Adding Two Columns

You have Sales in column A and Expenses in column B. You want Profit in column C.

=ARRAYFORMULA(A2:A + B2:B)

👉 Automatically adds sales and expenses row by row.

---

Example 3: Use with IF Function

Suppose you want to check if Sales (column A) are greater than 5000 and return “Target Achieved” or “Pending”.

=ARRAYFORMULA(IF(A2:A>5000, "Target Achieved", "Pending"))

👉 It will apply the IF condition to all rows automatically.

---

Example 4: Auto Serial Numbers

Instead of typing numbers manually, you can use:

=ARRAYFORMULA(ROW(A2:A)-1)

👉 This gives you auto serial numbers starting from 1.

(Here ROW(A2:A) returns row numbers, and -1 adjusts it.)

---

Example 5: ARRAYFORMULA with IMPORTRANGE

If you are importing data from another sheet and want to apply logic on the entire dataset:

=ARRAYFORMULA(IMPORTRANGE("sheet_url", "Sales!A2:A") * 1.18)

👉 Example: applying 18% GST on imported sales data.

Read More: IMPORTRANGE in Google Sheets

---

Example 6: ARRAYFORMULA with VLOOKUP

If you want to fetch values for multiple rows instead of writing multiple VLOOKUPs:

=ARRAYFORMULA(VLOOKUP(A2:A, Sheet2!A:B, 2, FALSE))

👉 Looks up all values in column A and returns corresponding data from Sheet2.

---

Example 7: ARRAYFORMULA with DATE Functions

Suppose you have dates in column A and want to extract year or month for each row:

=ARRAYFORMULA(YEAR(A2:A))
=ARRAYFORMULA(TEXT(A2:A, "MMMM"))

👉 This pulls Year or Month from every row automatically.

---

Example 8: ARRAYFORMULA with LEN (Counting Characters)

If column A has names, and you want to count characters:

=ARRAYFORMULA(LEN(A2:A))

👉 Counts characters in all rows without dragging formula.

---

Example 9: ARRAYFORMULA with SPLIT + TRIM

Split full names into first and last names automatically:

=ARRAYFORMULA(SPLIT(TRIM(A2:A), " "))

👉 Works on multiple rows together, not just one.

Example 10: Auto Serial Numbers with IF, LEN, and ROW

Problem: You want serial numbers (1,2,3…) in column A only if there is data in column B. If the row is empty, serial number should not appear.

Formula:

=ARRAYFORMULA(IF(LEN(B2:B), ROW(B2:B)-ROW(B2)+1, ""))

👉 How it works:

• LEN(B2:B) → checks if column B has text/number (non-empty).
• IF(LEN(B2:B), … , "") → puts serial number if not empty, otherwise leaves blank.
• ROW(B2:B)-ROW(B2)+1 → generates running numbers starting from 1.

---

Limitations of ARRAYFORMULA

• Cannot be combined with every function (some need manual ranges).
• May slow down your sheet if you apply on thousands of rows.
• Needs careful handling, otherwise it fills blank rows unnecessarily.

---

Best Practices for MIS Users

• Always apply ARRAYFORMULA on specific ranges instead of entire columns like A:A. (Use A2:A1000 instead.)
• Combine with IFERROR() to handle blanks or missing values. Example: =ARRAYFORMULA(IFERROR(A2:A / B2:B, ""))
• Use it with IMPORTRANGE, QUERY, and FILTER for more powerful reporting.

Conclusion

ARRAYFORMULA is a real time-saver for anyone who works in MIS or handles reports in Google Sheets. Once you understand it, you don’t need to drag formulas ever again. Combine it with functions like IF, VLOOKUP, IMPORTRANGE, QUERY, and FILTER to make powerful, automated reports.

FAQs – ARRAYFORMULA in Google Sheets

What’s Next?

In the next post, we’ll learn about the QUERY Function in Google Sheets