ARRAYFORMULA and LAMBDA for Array Calculations

If you have been using Google Sheets for any length of time, you have probably run into this situation: you write a formula in one cell, drag it down a few hundred rows, and end up with a column full of essentially identical formulas that all need to be maintained individually. Add a row, and the formula does not extend automatically. Delete a row, and a reference breaks. Share the workbook with a colleague, and they drag the formula to the wrong place and everything falls apart.

ARRAYFORMULA and LAMBDA are the two functions that eliminate this problem entirely. Together, they let you write a single formula that does the work of hundreds, handles custom logic, and scales automatically as your data grows. For Australian small businesses where spreadsheets are doing real operational work — tracking stock, managing client billing, calculating commissions, or building reporting summaries — these two functions are the difference between a workbook that requires constant maintenance and one that runs itself.

This tutorial covers both functions from the ground up, explains when to use each one, and walks through practical business examples you can adapt immediately.

What ARRAYFORMULA Does (and Why You Need It)

ARRAYFORMULA allows a single formula to perform calculations across an entire range of cells and return multiple results simultaneously. Instead of writing =B2*C2 in row 2, then =B3*C3 in row 3, and so on down the column, you write one formula in a single cell and it calculates the result for every row in the range.

The basic syntax is:

=ARRAYFORMULA(formula_or_expression)

Here is a simple example. Suppose column B contains unit prices and column C contains quantities. To calculate the line total for every row, instead of dragging a formula down the column, you write this in a single cell (say D2):

=ARRAYFORMULA(B2:B * C2:C)

That single formula populates D2 all the way down the column, automatically including any new rows added to your data. Delete row 50, and the formula adjusts. Add 200 new rows, and they are covered without touching the formula.

Where ARRAYFORMULA Shines

ARRAYFORMULA works with most standard Google Sheets functions. Here are the categories where it delivers the most value:

Arithmetic operations across columns

=ARRAYFORMULA(B2:B * C2:C)

Multiplies every value in column B by the corresponding value in column C. Perfect for calculating line totals, commission amounts, or tax on each transaction.

Conditional logic applied to an entire column

=ARRAYFORMULA(IF(E2:E > 5000, "High Value", "Standard"))

Labels every row as "High Value" or "Standard" based on the transaction amount in column E. The alternative — copying an IF formula down 500 rows — is fragile and slow.

Text manipulation across a dataset

=ARRAYFORMULA(TRIM(LOWER(A2:A)))

Cleans and lowercases every name or text entry in column A in one formula. Ideal for normalising data imported from forms, CRMs, or exported CSVs.

Concatenation across rows

=ARRAYFORMULA(A2:A & " " & B2:B)

Combines first and last name columns into a single full name column across every row simultaneously.

Counting and conditional aggregation

=ARRAYFORMULA(COUNTIF(D2:D, D2:D))

Returns, for every row, how many times that row's value appears in the entire column D. Useful for flagging duplicates or showing frequency counts inline.

The Important Caveat: Blank Row Handling

One practical issue with open-ended ARRAYFORMULA ranges (using B2:B rather than B2:B100) is that they will attempt to calculate for every row in the spreadsheet, which can produce unwanted blank results or zeros in empty rows. The standard fix is to wrap the formula in an IF that checks whether the row actually contains data:

=ARRAYFORMULA(IF(B2:B <> "", B2:B * C2:C, ""))

This tells the formula to calculate only where column B is not empty, and return a blank for all empty rows. Use this pattern consistently and your columns will stay clean regardless of how much data you add.

What LAMBDA Does (and Why It Changes Everything)

ARRAYFORMULA is powerful, but it has a limitation: you can only use the expressions and functions that Sheets already provides. If you want to apply a custom calculation — one that does not map neatly to a single built-in function — ARRAYFORMULA alone cannot help you.

LAMBDA fills this gap. It allows you to define your own custom function directly inside a formula, without needing Google Apps Script or any coding knowledge. You give your function a name, define its parameters, and specify what it should calculate. Then you can reuse that logic anywhere in your sheet, including inside ARRAYFORMULA.

The basic syntax is:

=LAMBDA(parameter1, parameter2, ..., formula)(value1, value2, ...)

A simple example: suppose you frequently need to calculate GST-inclusive price from a GST-exclusive amount. Instead of writing =A2 * 1.1 everywhere, you define a LAMBDA:

=LAMBDA(excl_gst, excl_gst * 1.1)(A2)

This reads: "Define a function that takes a parameter called excl_gst and multiplies it by 1.1. Now call that function with the value in A2." The result is exactly the same as =A2 * 1.1, but the intent is self-documenting and the logic is defined in one place.

Named Functions: The Real Power of LAMBDA

Used as a one-off inline expression, LAMBDA is useful but not transformative. The real power comes from Named Functions, which let you save a LAMBDA as a reusable named formula in your spreadsheet.

To create a Named Function:

1. Go to Data > Named functions (or Insert > Named functions, depending on your Sheets version).
2. Click Add new function.
3. Give your function a name (e.g., GST_INCLUSIVE).
4. Define the argument placeholders (e.g., excl_gst).
5. Enter the formula body: excl_gst * 1.1.
6. Click Save.

Now you can use =GST_INCLUSIVE(A2) anywhere in your spreadsheet, exactly like a built-in function. It works across every tab. If the GST rate ever changes, you update the Named Function definition once, and every cell using it updates automatically.

LAMBDA with ARRAYFORMULA: The Combination That Scales

Where LAMBDA becomes genuinely powerful for data work is when you combine it with helper functions designed to apply it across a range. The three most important are:

• MAP — applies a LAMBDA to each element in a range and returns the results
• REDUCE — accumulates a running total or result across a range using LAMBDA logic
• SCAN — similar to REDUCE but returns intermediate results for every step

MAP: Apply Custom Logic to Every Row

MAP is the ARRAYFORMULA equivalent for LAMBDA expressions. It applies your custom function to every element in a range.

Suppose you have a column of revenue figures (column B) and a column of cost figures (column C), and you want to calculate a custom margin percentage that clips at 0% (i.e., never shows a negative margin in the output):

=MAP(B2:B10, C2:C10, LAMBDA(rev, cost, MAX(0, (rev - cost) / rev)))

This applies the margin formula to every row pair in columns B and C. If the margin would be negative, it returns 0 instead. You could not achieve the MAX capping cleanly with standard ARRAYFORMULA because the logic requires evaluating two parameters per row with a nested function.

REDUCE: Running Totals and Accumulations

REDUCE processes a range sequentially, carrying a running value from row to row. A practical use case is a running balance for a transaction ledger:

=REDUCE(0, C2:C20, LAMBDA(acc, val, acc + val))

This starts at 0 and adds each value in C2:C20 one by one, returning the final cumulative total. For a ledger, you would more typically use SCAN to see the running balance at each step.

SCAN: Running Balance Column

SCAN behaves like REDUCE but returns the intermediate result after each step, making it perfect for running balance columns:

=SCAN(0, C2:C20, LAMBDA(acc, val, acc + val))

Place this formula in D2 and it outputs a running balance for every row — the balance after transaction 1, after transaction 2, and so on. This replaces a dragged-down =D1 + C2 formula with a single, self-contained expression.

Practical Business Examples

Here are four complete examples built for common Australian SMB workflows.

Example 1: Invoice Line Totals with Tax

You have an invoice sheet with columns for quantity (B), unit price (C), and GST applicable (D, which is either "Y" or "N"). You want column E to calculate the GST-inclusive line total.

=ARRAYFORMULA(
  IF(B2:B <> "",
    IF(D2:D = "Y",
      B2:B * C2:C * 1.1,
      B2:B * C2:C
    ),
  "")
)

This single formula calculates the correct line total for every row — applying 10% GST where applicable and leaving non-GST items unchanged. New rows added to the invoice are calculated automatically.

Example 2: Commission Tier Calculation

Your sales team earns different commission rates depending on their monthly revenue: 5% up to $10,000, 8% from $10,001 to $25,000, and 12% above $25,000. Rather than copy an IFS formula down 50 rows, use a Named Function.

Create a Named Function called COMMISSION_RATE with one argument revenue:

IFS(revenue <= 10000, revenue * 0.05, revenue <= 25000, revenue * 0.08, TRUE, revenue * 0.12)

Then in your sheet, apply it across the whole column with MAP:

=MAP(B2:B50, LAMBDA(rev, COMMISSION_RATE(rev)))

Every salesperson's commission is calculated correctly from a single definition. When management changes the tier thresholds, you update one Named Function and every calculation updates instantly.

Example 3: Categorising Expense Entries by Description

Your expense column (A) contains free-text descriptions imported from a card system. You want to auto-categorise them into "Travel", "Software", "Office", or "Other" based on keywords.

=ARRAYFORMULA(
  IF(ISNUMBER(SEARCH("flight|hotel|uber|taxi", A2:A)), "Travel",
  IF(ISNUMBER(SEARCH("adobe|slack|zoom|xero|licence", A2:A)), "Software",
  IF(ISNUMBER(SEARCH("stationery|printer|desk|chair", A2:A)), "Office",
  "Other")))
)

SEARCH is case-insensitive, and wrapping it in ISNUMBER converts its output to TRUE/FALSE so the IF logic works correctly. ARRAYFORMULA applies the entire nested IF structure to every row in column A from a single cell. This is the kind of data enrichment that would otherwise require a macro or manual review.

Example 4: Days Overdue with Automatic Status Flag

You have an accounts receivable sheet with invoice dates in column B and payment dates in column C (blank if unpaid). You want column D to show days overdue for unpaid invoices and "Paid" for settled ones.

=ARRAYFORMULA(
  IF(B2:B = "", "",
    IF(C2:C <> "", "Paid",
      IF(TODAY() - B2:B > 30, "Overdue " & (TODAY() - B2:B) & " days",
        "Due in " & (B2:B + 30 - TODAY()) & " days"
      )
    )
  )
)

This formula checks whether there is an invoice date, whether payment has been made, and whether the invoice is past 30-day terms. It labels each row appropriately and updates automatically every day when the spreadsheet is opened.

Tips for Working Effectively with ARRAYFORMULA and LAMBDA

A few practical guidelines that save time and prevent common problems.

Never mix ARRAYFORMULA with manually dragged formulas in the same column. If you put an ARRAYFORMULA in D2, delete any existing formulas in D3 onwards. ARRAYFORMULA will fill those cells automatically, and having both causes conflicts and errors.

Use the blank-row guard pattern consistently. Always wrap open-ended ARRAYFORMULA ranges with IF(A2:A <> "", ..., "") to prevent calculations from spilling into blank rows and producing zeros or errors throughout your column.

Name your LAMBDA functions descriptively. A Named Function called COMMISSION_RATE is self-documenting. One called CALC1 is a maintenance liability. Treat Named Functions like you would name columns in a database.

Test LAMBDA logic on a single value before applying MAP. Write your LAMBDA as a standalone formula with a single test value first. Once it produces the correct result for one input, wrap it in MAP to apply it across the range.

Document complex ARRAYFORMULA expressions with cell notes. Right-click the cell and select Insert note to explain what the formula does and why. Complex nested ARRAYFORMULAs can be difficult to parse later, and a brief note ("Calculates GST-inclusive line total, applies only to rows with a non-empty quantity") pays dividends during audits, handovers, and formula debugging.

Combine with IFERROR for production sheets. When your ARRAYFORMULA references data that might be missing or malformed, wrapping with IFERROR prevents error values from appearing in shared reports:

=ARRAYFORMULA(IFERROR(B2:B / C2:C, 0))

Upgrade Your Google Workspace Subscription

These advanced Sheets capabilities are available on every Google Workspace plan. If your Australian business is still running on a free Google account or an older G Suite plan, upgrading to a current Google Workspace subscription gives your team access to the full suite of productivity tools alongside the Sheets functions covered in this tutorial.

Get Google Workspace for your business: Google Workspace Referral — Cloud Geeks

Workspace Business Starter plans are priced per user per month and include Google Sheets, Docs, Drive, Meet, Gmail, and all the productivity apps your team already relies on — with Australian data residency options, admin controls, and support included.

Wrapping Up

ARRAYFORMULA and LAMBDA represent a genuine step up in what Google Sheets can do for your business. They are not just formula tricks — they are a different way of thinking about how a spreadsheet should be structured.

The key takeaways from this tutorial:

• ARRAYFORMULA replaces columns of dragged-down formulas with a single expression that scales automatically and is far easier to maintain.
• The blank-row guard pattern (IF(A2:A <> "", ..., "")) keeps your columns clean and prevents unwanted output in empty rows.
• LAMBDA lets you define reusable custom logic without coding, and Named Functions make that logic available throughout your workbook just like a built-in formula.
• MAP, REDUCE, and SCAN apply LAMBDA expressions across ranges, giving you the ability to run custom calculations on every row, accumulate running totals, and build running balances from a single formula.
• Combining both functions unlocks patterns that simply are not possible with standard formulas: auto-categorisation, tiered calculations, and conditional logic applied across thousands of rows without maintenance overhead.

For Australian SMBs where team members are maintaining spreadsheets manually — copying formulas, updating individual cells, or rebuilding reports every week — these two functions are the fastest path to a workbook that handles itself. Start with ARRAYFORMULA on your next formula column, save your first Named Function this week, and you will immediately see the difference in how much time your spreadsheets give back.

Need help with your Google Workspace setup or migration? Contact the Cloud Geeks team for a free consultation.