If you have a massive spreadsheet tracking thousands of sales transactions, you likely need a column that multiplies the “Quantity” by the “Unit Price” to get the “Total”. The traditional way to do this is to write the formula =A2*B2 in cell C2, and then click and drag the little blue square down to copy the formula to cell C1000.
This traditional method is incredibly flawed. If someone inserts a new row in the middle of your data, or if a Google Form automatically injects a new row at the bottom, that new row will lack the formula. Your totals will be permanently broken unless you remember to manually drag the formula down again. Furthermore, having 10,000 individual formulas slows down the entire spreadsheet.
Google Sheets solves this entirely with the ARRAYFORMULA function. It allows you to write one single formula in the header row that automatically projects its calculation all the way down the column, infinitely, even as new data is added.
The Standard Formula vs. The Array Formula
Let’s assume Column A is Quantity, Column B is Price, and Column C is the Total.
- Standard method (in cell C2):
=A2*B2 - Array method (in cell C2):
=ARRAYFORMULA(A2:A * B2:B)
Notice that we changed the single cell references (A2) to infinite column ranges (A2:A). By wrapping it in ARRAYFORMULA, Google Sheets understands that it should take A2 and multiply it by B2, then automatically move to row 3, then row 4, all the way down to the bottom of the sheet.
Step 1: Implementing the Basic Array
- Delete all the individual formulas in Column C. (You only need one formula at the very top).
- Click on cell C2.
- Type:
=ARRAYFORMULA(A2:A * B2:B) - Press Enter. Watch as the entire column instantly fills with the correct totals.
Step 2: The Pro Method (Handling Blank Rows)
If you scroll down your sheet past your actual data, you will notice a problem. Because the formula looks at infinite columns (A2:A), it is multiplying empty cells at the bottom of the sheet, resulting in hundreds of ugly $0.00 or #VALUE! errors filling the bottom of your screen.
To fix this, we must wrap our ARRAYFORMULA inside an IF statement. We will tell Google Sheets: “If Column A is blank, do nothing. Otherwise, do the math.”
- Go back to cell C2.
- Update the formula to this:
=ARRAYFORMULA(IF(A2:A = "", "", A2:A * B2:B)) - Press Enter.
The column will instantly clean itself up. Now, your spreadsheet is completely bulletproof. If a Google Form injects a new entry on row 500, the ARRAYFORMULA sitting safely in cell C2 will instantly recognize the new data in Column A and automatically generate the total in Column C without any human intervention.