Exercises / Modern Excel: dynamic arrays · 1 of 12
One formula, a whole column of totals
In Excel 365 a formula can return many values. Give it whole ranges instead of single cells, as in =A2:A4*2, and it works row by row: the results spill down from the cell you typed in. Nothing to copy. If something blocks the spill area, Excel shows #SPILL!.
Your taskIn D2, multiply all the prices in B2:B7 by all the quantities in C2:C7 in one formula, and let the results spill down to D7.
Pro
Unlock this exercise and every module after the basics.
Pro opens every exercise in every module, your review schedule, and the final test for the course certificate.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Total |
| 2 | Notebook | $3.50 | 20 | |
| 3 | Pen set | $6.00 | 15 | |
| 4 | Stapler | $12.90 | 4 | |
| 5 | Desk lamp | $24.00 | 6 | |
| 6 | Backpack | $39.99 | 2 | |
| 7 | Monitor arm | $58.00 | 3 |