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.

ABCDEF
1RepRegionProductUnitsPriceSales
2AnaEastWidget12560
3BenWestGadget520100
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

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.

H2

2 Label every row Big if Sales are 100 or more, else Small, with one formula.

H2

3 Build a label like Ana - East for every row.

H2

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 pricing

Common ARRAYFORMULA mistakes

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.

Keep practicing