Applying Formulas to Columns With ARRAYFORMULA

Normally if you want a calculation in every row, you write one formula and drag it down. That works, but it leaves hundreds of copied formulas that you must extend whenever new data arrives.

Google Sheets offers a better way: ARRAYFORMULA. One formula in one cell computes the entire column at once and grows automatically.

ARRAYFORMULA tells Sheets to apply a formula across whole ranges instead of single cells. The result spills down to fill as many rows as the input range has.

So instead of =A2*B2 repeated 100 times, you write one formula referencing A2:A and B2:B, and the answers fill themselves in.

Suppose column A has quantity and column B has price. To compute the total per row, place this single formula in C2.

It multiplies each A value by the matching B value all the way down, and you never touch C3 onward.

Notice A2:A instead of A2:A100. The open-ended form means all rows from 2 down, so newly added data is included automatically.

This is the real power of ARRAYFORMULA: it future-proofs your sheet. Add a new order, and the total appears with no extra work.

Open-ended ranges include thousands of empty rows below your data, and the formula tries to compute them too, often showing 0 or stray values.

Wrap the calculation in an IF that checks whether the row is empty. If A is blank, return an empty string; otherwise do the math.

A neat trick: include the column header inside the array formula so the whole column is driven by one cell. Place this in C1.

The first part outputs the header text; the rest computes each data row. The ; stacks them vertically into one spilled column.

ARRAYFORMULA is not just for math. It works with text functions too. To build a full name column from first and last name, concatenate the two ranges.

This joins each first name in A with each last name in B, with a space between, for every row at once.

Some functions handle arrays on their own and do not need ARRAYFORMULA. SUMIF, FILTER, UNIQUE, and QUERY already work over ranges.

You reach for ARRAYFORMULA mainly when a normally single-cell operation (like *, &, or LEFT) needs to run on every row.

Functions like LEFT, UPPER, and TRIM normally act on one cell. Inside ARRAYFORMULA they act on an entire range.

This uppercases every email in column A in one shot. Without the wrapper, you would only transform the first cell.

You do not always have to type the function name. In Google Sheets, type your formula with range references, then press Ctrl plus Shift plus Enter (Cmd plus Shift plus Enter on Mac).

Sheets wraps the formula in ARRAYFORMULA for you automatically. It is a fast way to convert a dragged formula into a single spilling one.

For element-by-element operations, the ranges should be the same height. Mixing A2:A with B2:B50 can cause errors or misaligned results.

Keep both sides open-ended (A2:A and B2:B) or both fixed to the same size. Consistency keeps the output aligned row for row.

Test your ARRAYFORMULA knowledge.

You learned to compute whole columns with one formula:

• ARRAYFORMULA applies an operation across entire ranges
• Open-ended ranges like A2:A auto-include new rows
• Wrap with IF(A2:A = "", "", ...) to skip blanks
• It works with math, text joins, and functions like UPPER
• Ctrl/Cmd plus Shift plus Enter adds the wrapper for you

One cell, a living column.