Copying a formula down hundreds of rows is tedious, easy to break, and often unnecessary. In Google Sheets, an array formula can do the same row-by-row work from one cell, then expand the results automatically.

The catch is that array formulas only work well when the output area is clear and the referenced ranges line up. Learn those basics first, and tasks such as calculating sales totals, cleaning text, pulling records from another sheet, and looking up product details become much easier to maintain.

What an array formula does in Google Sheets

An array is simply a set of values that Google Sheets can process or return together. Instead of working with one value in one cell, a formula can work with a range such as B2:B100 and produce a matching range of results.

ARRAYFORMULA applies a calculation across a range at once. For example, a normal formula in D2 might multiply the quantity in B2 by the unit price in C2. An array formula can perform that same multiplication for every populated row below it without manually filling the formula down.

Google Sheets supports array formulas, but not every spilling formula needs the ARRAYFORMULA wrapper. Many newer functions, including FILTER, QUERY, and often XLOOKUP, return multiple results automatically. You enter the formula once, and Sheets spills the output into adjacent cells or rows.

That distinction matters:

• Use ARRAYFORMULA when you want to repeat a calculation or transformation across matching rows.
• Use a naturally spilling function when you want to return several columns, a filtered list, or lookup results for many values.
• Use FILTER or QUERY when the goal is to create a subset of a table rather than calculate an output for every source row.

Set up your sheet so results have room to expand

Enter an array formula in the top-left cell where you want results to begin. If your formula should return values down column D starting at row 2, place it in D2, not in every cell in D2:D.

Then leave the expected spill area empty. Google Sheets needs to write results into every cell required by the formula. A value, formula, or even a stray space in one of those cells can stop the entire result from expanding.

Before entering the formula, check for these common problems:

• Existing content: Clear old values and copied-down formulas from the output range.
• Merged cells: Do not merge cells in an area where an array result needs to spill.
• Unnecessary full-column ranges: References such as B2:B are convenient, but can make a busy workbook recalculate more slowly. If you know the sheet will use no more than 1,000 rows, B2:B1000 is usually a better default.

There is no special ARRAYFORMULA keyboard shortcut in Google Sheets. Ctrl+Enter can fill a selected range with a conventional formula, but it is not a substitute for a spilling array formula. With ARRAYFORMULA, enter one formula in one starting cell and let Sheets populate the result range.

A common mistake is to copy the array formula down after entering it. Do not do that. Multiple copies create overlapping spill ranges and usually produce errors.

Create your first ARRAYFORMULA with a row-by-row calculation

Suppose column B contains Quantity and column C contains Unit Price. You want a line total in column D for every order row.

Put this formula in D2:

=ARRAYFORMULA(B2:B*C2:C)

Google Sheets matches the values row by row: B2 is multiplied by C2, B3 by C3, and so on. The formula returns a vertical array of results beginning in D2.

For a smaller or heavier workbook, use a bounded range instead:

=ARRAYFORMULA(B2:B1000*C2:C1000)

Both ranges must start and end on matching rows. If one range has 999 cells and the other has 1,000, Sheets cannot reliably pair every value.

Prevent blank rows from showing zero values

The first formula can display zeros for empty future rows, which makes a report look unfinished. Wrap the calculation in IF so Sheets only calculates when a row contains a record:

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

This says: if the Quantity cell is blank, return a blank result; otherwise, multiply Quantity by Unit Price. Use the column that consistently signals a real record. In an order sheet, that might be an order ID, date, or quantity column rather than a column that is occasionally empty.

Use array formulas for common text and date transformations

Array formulas are not only for math. They are especially useful for repetitive cleanup jobs that would otherwise require dragging a formula through an imported list.

For example, if column A contains customer names with inconsistent spacing and capitalization, place this in B2:

=ARRAYFORMULA(IF(A2:A="","",UPPER(TRIM(A2:A))))

TRIM removes extra spaces, UPPER converts the text to uppercase, and IF prevents the output column from filling every unused row with blank-derived results.

Dates work the same way. If A contains dates and you need the month number for each populated row, use:

=ARRAYFORMULA(IF(A2:A="","",MONTH(A2:A)))

If you need a readable month label instead, use TEXT(A2:A,"mmmm") inside the same pattern. The important part is not the specific date function; it is applying it to the range and guarding blank source rows.

Return matching rows with FILTER and QUERY

ARRAYFORMULA repeats a calculation for each row. FILTER and QUERY solve a different problem: returning only the rows you want to see.

Assume columns A through D contain a task list, and column D stores a status. To return only open tasks, enter this in an empty area:

=FILTER(A2:D, D2:D="Open")

FILTER automatically spills every matching row and its columns. You do not need to wrap it in ARRAYFORMULA. The condition range must align with the filtered data: if the source starts at row 2, the condition should also start at row 2.

Use FILTER when you need a live list of qualifying rows. Use QUERY when you need more report-like work, such as selecting certain columns, grouping results, or reshaping a table. Use ARRAYFORMULA when every source row should remain represented and receive its own calculation.

Build an array formula that pulls data from another sheet

You can reference ranges on another tab exactly as you would reference ranges on the current sheet. For example, suppose the Orders sheet has an order number in column A and customer name in column B. To create a combined label on another sheet, enter:

=ARRAYFORMULA(IF(Orders!A2:A="","",Orders!A2:A&" - "&Orders!B2:B))

The formula checks whether Orders column A is blank. For populated rows, it joins the order number, a separator, and the customer name into one result.

If a sheet name contains spaces, wrap the name in single quotes:

=ARRAYFORMULA(IF('Monthly Orders'!A2:A="","",'Monthly Orders'!A2:A&" - "&'Monthly Orders'!B2:B))

Cross-sheet formulas are useful, but whole-column references across several tabs can slow a large file. When practical, use defined limits such as Orders!A2:A2000, particularly if multiple array formulas depend on the same source data.

Use XLOOKUP across a list of lookup values

XLOOKUP can accept a range of lookup values and return a matching list. That makes it a practical option for filling a product name, category, or price beside a long list of IDs.

Suppose A2:A contains product IDs, Products column A contains the master IDs, and Products column C contains product names. Use:

=XLOOKUP(A2:A, Products!A2:A, Products!C2:C, "Not found")

In Google Sheets, this can spill results for the lookup values in A2:A, so ARRAYFORMULA is often unnecessary. Enter it once in the first output cell and keep the spill range clear.

If XLOOKUP is not available in the file you are working with, or the existing spreadsheet already relies on older formulas, use VLOOKUP or INDEX/MATCH instead. Do not rewrite a stable model just to use a newer function unless it solves a real limitation.

Calculate arrays with subtraction and sums

Array formulas are equally useful for differences between two columns. For an actual-versus-budget report, where C is Actual and D is Budget, enter this formula in E2:

=ARRAYFORMULA(IF(A2:A="","",C2:C-D2:D))

This returns an amount for every record: actual minus budget. The check uses column A because it is assumed to contain the identifying value for each row.

Do not confuse a per-row calculation with a grand total. This formula returns one result per row:

=ARRAYFORMULA(B2:B+C2:C)

To produce one total for all row-level sums, use:

=SUM(B2:B+C2:C)

Or, when needed for a more complex calculation, nest the array operation inside SUM:

=SUM(ARRAYFORMULA(B2:B+C2:C))

Keep paired ranges aligned. Adding B2:B1000 to C2:C999 is a setup error, not a formula feature. Matching row boundaries are essential for multiplication, subtraction, comparisons, and other row-by-row array work.

Troubleshoot an array formula that is not working

When an array formula fails, start with the first cell containing the formula. Google Sheets reports the problem there, even though the intended output may cover many cells.

If the issue is hard to spot, simplify the formula. Test the direct range operation first, such as =B2:B10*C2:C10, then add the IF condition, cross-sheet reference, lookup, or text function one layer at a time. This isolates the part that is actually failing.

Most problems fall into a few predictable categories:

• Spill blockage: A cell in the expected output range contains content.
• Mismatched ranges: Two ranges intended to work row by row start or end on different rows.
• Blank-row artifacts: The calculation runs on unused rows and produces zeros or unwanted text.
• Circular references: The formula refers to its own output column or spill area.
• Incorrect sheet references: A tab name is misspelled, or a name with spaces is missing single quotes.

ARRAYFORMULA cannot overwrite data. Clear the blockage or move the formula to a genuinely empty output area instead of trying to copy the formula down around existing values.

Fix the "Array result was not expanded" error

This error means the formula has results to return, but something is in the way. Click the formula cell, then inspect the intended spill range until you find the first nonempty cell blocking expansion.

Clear that cell if the content is no longer needed, or move the array formula to an empty column or section of the sheet. Check for invisible-looking issues too, including spaces, old formulas, and merged cells.

Avoid slow formulas in large workbooks

Full-column references are convenient, but repeated calculations over thousands of mostly empty rows add up. Limit ranges when you have a reasonable maximum, especially when an array formula references another sheet.

Also avoid stacking several volatile functions inside wide array formulas. If one deeply nested formula is slow or difficult to audit, separate the work into helper columns. A short, visible sequence of formulas is often more reliable than one clever formula nobody wants to touch later.

Write array formulas that stay readable and reliable

A good array formula should be understandable six months after you create it. Start with a clear header in the row above the formula, then place the formula in the first data row rather than mixing headers into its output.

• Use a reliable input column for blank checks, such as an ID, date, or required item name.
• Keep all row-by-row ranges aligned, including their starting row and ending row.
• Test the formula on a small set of records before applying it to a full import or report.
• Use bounded references for performance-sensitive files.
• Add a nearby note or sheet comment when the business logic is not obvious.

Do not use an array formula when each row intentionally needs a different formula, when users must edit individual output values, or when a pivot table or QUERY report would answer the question more directly. Array formulas are best when the same logic should apply consistently to every qualifying row.

Generate, explain, and correct an array formula faster

Array formulas replace repetitive spreadsheet work with one maintainable instruction. The formula still needs correct ranges, an empty spill area, and sensible blank handling, but once those pieces are in place, it can make a report far easier to update.

If you know what result you need but are unsure how to write or debug the formula, FormulaBerry can turn a plain-language request into a Google Sheets or Excel formula, explain a complicated formula, and help correct an existing one.

FormulaBerry

FormulaBerry is most useful when you can describe the spreadsheet task clearly but do not want to build the formula from scratch. For example, you can ask for a Google Sheets formula that multiplies quantity by price for every populated row while leaving blank rows empty, then compare the result with your column layout.

It is a formula assistant, not a replacement for checking the sheet's ranges, headers, and intended output location. Choose it when you need help drafting, interpreting, or fixing a formula and want a practical starting point without needing to know the exact function syntax.