How to Use the ARRAYFORMULA Function in Google Sheets to Apply a Formula to an Entire Column

When you build a standard Google Sheet, you usually write a formula in cell C2 (e.g., =A2*B2) and then manually drag the small blue square down to row 1000 to copy the formula. This traditional method is incredibly inefficient for three reasons:

1. If a coworker inserts a brand new row in the middle of the sheet, the formula will not automatically apply to the new row.
2. Having 1,000 individual, discrete formulas running simultaneously consumes massive amounts of browser RAM and slows down the spreadsheet.
3. If you need to change the math later, you have to change it in cell C2 and re-drag it down 1,000 rows again.

To build a truly dynamic, enterprise-grade dashboard, you must use the ARRAYFORMULA. This god-tier function allows you to write one single formula in the very top cell of your column, and it will instantly and automatically beam the math down the entire column, infinitely, forever.

The Syntax of ARRAYFORMULA

The standard syntax is =ARRAYFORMULA(array_formula), but there is a much faster keyboard shortcut: Ctrl+Shift+Enter (or Cmd+Shift+Enter on Mac).

Step 1: Write the Master Formula

Assume Column A contains “Quantity” and Column B contains “Price”. We want Column C to calculate the “Total Revenue” (A * B).

1. Click on cell C2 (the very first cell in your output column).
2. Instead of typing =A2*B2, you must reference the entire columns. Type the following:

   =A2:A * B2:B

Step 2: Wrap it in the Array

If you press Enter right now, it will break. Google Sheets cannot multiply an entire column by another column unless you explicitly tell it to process the data as an array.

1. While your cursor is still inside the formula bar, press Ctrl + Shift + Enter (Windows) or Cmd + Shift + Enter (Mac).
2. Google Sheets will automatically wrap your math inside the master function, so it now looks exactly like this:

   =ArrayFormula(A2:A * B2:B)

3. Now, press Enter.

The Result

The math will instantly explode down Column C, calculating the revenue for every single row all the way down to the bottom of the sheet.

The true power of this method:

• Click on cell C500. Try to press the “Delete” key. You can’t. The cell is functionally empty; it is simply displaying a hologram of the math being beamed down from the master engine in C2.
• If you insert a brand new row at row 50, the math will instantly and automatically apply to it without you dragging anything.
• If you want to change the math from Multiplication to Addition, you only have to edit cell C2, and the entire sheet updates instantaneously. Your spreadsheet is now incredibly fast, virtually unbreakable, and mathematically pristine.