If you have a Google Sheet with 5,000 rows of data and you write a formula in cell C2 (e.g., =A2*B2), standard spreadsheet behavior requires you to click the small blue square in the corner of C2 and physically drag it down 4,999 rows. Not only is this tedious, but if a coworker later adds a new row of data at the very bottom, they have to remember to copy the formula down manually. If they forget, your dashboard breaks.
You can completely eliminate this manual dragging by using the ARRAYFORMULA function. It allows you to write one single “Master Formula” at the top of a column that automatically calculates and spills the results down the entire column infinitely, even as new data is added.
The Scenario
Imagine you have Column A (Quantity) and Column B (Price). You want Column C to calculate the Total (Quantity * Price) for every single row.
Step 1: Write the ArrayFormula
Instead of writing a formula targeting a single cell (like A2), you write the formula targeting the entire range of the column (A2:A).
- Click on cell C2 (the very first empty cell in your Totals column).
- Paste the following formula exactly as written:
=ARRAYFORMULA(A2:A * B2:B) - Press Enter.
The Immediate Result
Google Sheets will instantly take every number in Column A, multiply it by its corresponding number in Column B, and output the answers all the way down Column C automatically. You only wrote one formula in C2, but it populated the entire sheet.
Step 2: Fixing the “Empty Row” Problem (The IF Trick)
While the basic ArrayFormula works perfectly, you will quickly notice a cosmetic issue. If you have 50 rows of data, but your spreadsheet extends down to row 1,000, the ArrayFormula will keep multiplying blank cells by blank cells, filling the bottom of your sheet with ugly zeros (0).
To fix this, you must wrap your ArrayFormula in a logical IF statement that tells it to stop calculating if the row is empty.
Update your formula in C2 to this:
=ARRAYFORMULA(IF(A2:A = "", "", A2:A * B2:B))How this works:
IF(A2:A = "",checks if the current cell in Column A is totally blank."",says “If it is blank, output absolutely nothing (a blank space).”A2:A * B2:Bsays “But if there is data in Column A, go ahead and do the math.”
Why You Should Always Use ArrayFormulas
Aside from saving you the hassle of dragging boxes, ArrayFormulas protect your spreadsheet from human error. If a user tries to accidentally delete the math in cell C50, they can’t. The cell is technically empty; the math is being forcefully projected downward from the Master Formula in C2. The only way to break the column is to delete C2, making your spreadsheet significantly more robust and foolproof.