How to Use the ARRAYFORMULA Function to Apply Calculations Across Entire Columns in Google Sheets

When working with large datasets in Google Sheets, copying and pasting a standard formula down thousands of rows is a common but highly inefficient practice. It drastically slows down the performance of your spreadsheet, increases the file size, and makes the document difficult to maintain—if you need to change the formula later, you have to drag it down the entire column again.

To solve this, Google Sheets provides a powerful function called ARRAYFORMULA. This single function allows you to write one formula in the top cell of a column that automatically processes and outputs results for every single row beneath it.

The Problem with Standard Formulas

Imagine you have a list of prices in Column A and quantities in Column B, and you want to calculate the total cost in Column C.

Normally, you would type =A2*B2 into cell C2, and then physically drag that formula down to C1000. If you add new data to row 1001, you must remember to copy the formula down again. If you forget, your calculations will be incomplete.

How to Use ARRAYFORMULA

Instead of placing a formula in every cell, you place one ARRAYFORMULA in the very first row of your data (e.g., C2). You then change the cell references from single cells to entire column ranges.

The Syntax:

=ARRAYFORMULA(A2:A * B2:B)

Here is what happens:

• You place this formula in cell C2 only.
• It looks at the entire range from A2 to the bottom of the sheet (A2:A) and multiplies it by the entire range of B (B2:B).
• It automatically outputs the result of A2*B2 into C2, A3*B3 into C3, A4*B4 into C4, and so on, all the way to the bottom.

The Keyboard Shortcut (Fastest Method)

Google Sheets includes a hidden keyboard shortcut that wraps any standard formula inside an ARRAYFORMULA instantly.

1. Select the top cell where you want your results to begin (e.g., C2).
2. Type your formula using ranges instead of single cells (e.g., type =A2:A * B2:B). Do not press Enter yet.
3. Press Ctrl + Shift + Enter (or Command + Shift + Enter on a Mac).
4. Google Sheets will automatically wrap your formula, changing it to =ArrayFormula(A2:A * B2:B).
5. Press Enter to execute.

Handling Blank Rows with IF and ISBLANK

One minor annoyance with ARRAYFORMULA is that it will continue calculating even on empty rows, which often results in a column filled with “0” or error messages at the bottom of your data.

To keep your spreadsheet clean, combine it with an IF and ISBLANK statement to tell the formula to do nothing if the reference cell is empty.

=ARRAYFORMULA(IF(ISBLANK(A2:A), "", A2:A * B2:B))

This tells Google Sheets: “If column A is blank, leave this cell completely blank (""). If it is not blank, perform the multiplication.”

By replacing thousands of individual formulas with a single ARRAYFORMULA, your Google Sheets will load significantly faster and become completely immune to missing calculations when new rows are added.