One formula for a whole column
Step 1 of 3
ARRAYFORMULA makes a formula work on whole ranges instead of single cells. One formula in the top cell fills the whole column.
Click Continue when you're ready.
Already know it? Type the formula straight into D2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Units | Price | Total | ||
| 2 | Pens | 4 | 2.5 | |||
| 3 | Paper | 2 | 6 | |||
| 4 | Tape | 5 | 1.5 | |||
| 5 | Glue | 3 | 2 | |||
| 6 | ||||||
| 7 | ||||||
| 8 |
Need help?
Why this works
ARRAYFORMULA lets B2:B5*C2:C5 work row by row: 4×2.5, 2×6, 5×1.5 and 3×2. The four results spill down from D2, so there is one formula to keep up instead of four.
Common mistake: Writing =B2*C2 and filling it down. That works, but a new row needs the formula copied again.
The answer
- D2
=ARRAYFORMULA(B2:B5*C2:C5)
When a formula should work on a whole range at once, such as B2:B5*C2:C5 or IF over a column. Wrap it in ARRAYFORMULA and one formula in the top cell fills every row below.
+10 points