ARRAYFORMULA practice exercises
ARRAYFORMULA applies a calculation to whole ranges at once, so one formula in the top cell fills the entire column. No more dragging formulas down or forgetting to copy them to new rows.
Syntax
ARRAYFORMULA(expression_with_ranges)
expression- Any calculation that uses ranges instead of single cells, like
D2:D9*E2:E9.
The practice data
An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=ARRAYFORMULA(F2:F9*0.1)
Calculates a 10% commission on the Sales of all 8 rows at once. Result:
| 6 |
| 10 |
| 16 |
| 10 |
| 15 |
| 3.5 |
| 10 |
| 12 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Calculate Units × Price for every row with one formula.
2 Label every row Big if Sales are 100 or more, else Small, with one formula.
3 Build a label like Ana - East for every row.
Master ARRAYFORMULA in the full course
Lesson 13 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon ARRAYFORMULA mistakes
- Mixing a range with a single cell where you meant a range, like
D2:D9*E2. - Leaving data in the cells below, which blocks the result from spilling.
- Using functions that already return one value, such as SUM, and expecting a column back.
FAQ
How do I stop ARRAYFORMULA filling empty rows?
Test for blanks: =ARRAYFORMULA(IF(A2:A="",,D2:D*E2:E)).
Is there ARRAYFORMULA in Excel?
Excel 365 spills array results automatically, so the wrapper isn't needed there.