Unit 12 of 12 Lesson 1 free · Plus
Dynamic arrays
One formula, whole tables of answers.
Functions in this unit: UNIQUE, COUNTA, SORT, FILTER, SUM, SEQUENCE
Key ideas
Formulas can spill
=UNIQUE(B2:B7)One formula fills as many cells as it needs. Edit only the first cell.
FILTER keeps matching rows
=FILTER(A2:C7,C2:C7>100)Give it the data and a TRUE/FALSE test. It returns every row that passes.
#SPILL! means blocked
#SPILL!Something is in the way. Clear the cells where the results need to go.
Lesson 1: Spill basics
What does it mean when a formula "spills"?
- It fills neighboring cells with its results
- It has an error
- It deletes nearby data
- It copies itself down
Show answer
It fills neighboring cells with its results. Modern Excel lets one formula return a whole list.
List each region once, starting in F2.
A B C D E F 1 Product Region Units Price Regions 2 Notebook East 120 3.5 3 Pen West 340 1.25 4 Stapler East 45 12 5 Binder North 80 6.75 6 Marker West 210 2.4 7 Folder North 150 1.9 Hint:
=UNIQUE(B2:B7)Show answer
=UNIQUE(B2:B7)in F2. One formula, three answers. Add a new region and it updates.What will F1 show?
A B C D E F 1 Product Region Units Price 2 Notebook East 120 3.5 3 Pen West 340 1.25 4 Stapler East 45 12 5 Binder North 80 6.75 6 Marker West 210 2.4 7 Folder North 150 1.9 F1 contains
=COUNTA(UNIQUE(B2:B7))Show answer
3. East, West and North.
List the unit counts from highest to lowest, starting in F2.
A B C D E F 1 Product Region Units Price Top units 2 Notebook East 120 3.5 3 Pen West 340 1.25 4 Stapler East 45 12 5 Binder North 80 6.75 6 Marker West 210 2.4 7 Folder North 150 1.9 Hint:
=SORT(C2:C7,1,-1). The -1 means descending.Show answer
=SORT(C2:C7,1,-1)in F2. Your original data stays untouched.What does #SPILL! mean?
- Cells where results need to go are not empty
- The formula is misspelled
- Division by zero
- The lookup failed
Show answer
Cells where results need to go are not empty. Clear the blocking cells and the results appear.
Lesson 2: FILTER
Lesson 2 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.
See what Plus includesLesson 3: Array math
Lesson 3 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.
See what Plus includes