Unit 12 of 12
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
List every product sold in the West, starting in F2.
A B C D E F 1 Product Region Units Price West products 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: =FILTER(A2:A7,B2:B7="West")
Show answer
=FILTER(A2:A7,B2:B7="West")in F2. Like an AutoFilter that never needs refreshing.Return the Product, Region and Units of every row with more than 100 units, starting in F2.
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 Hint: =FILTER(A2:C7,C2:C7>100)
Show answer
=FILTER(A2:C7,C2:C7>100)in F2. FILTER can return whole rows, not just one column.Build: products under $5, or "None" if there are none
Show answer
=FILTER(A2:A7,D2:D7<5,"None"). The third argument is what to show when nothing matches.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
=SUM((B2:B7="East")*C2:C7)Show answer
165. The test makes 1s and 0s, so only East units survive: 120 + 45.
When does FILTER use its third argument?
- When no rows match
- When the data has blanks
- Always
- When there is an error in the data
Show answer
When no rows match. Without it, an empty result shows #CALC!.
Lesson 3: Array math
Total the East units without using SUMIF. Put it in F2.
A B C D E F 1 Product Region Units Price East 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: =SUM((B2:B7="East")*C2:C7)
Show answer
=SUM((B2:B7="East")*C2:C7)in F2. TRUE times a number counts it, FALSE times a number zeroes it.Fill the month numbers 1 to 12 with one formula in A2.
A 1 Month 2 Hint: =SEQUENCE(12)
Show answer
=SEQUENCE(12)in A2. SEQUENCE builds numbered lists instantly.Calculate revenue for every row at once with a single formula in E2.
A B C D E 1 Product Region Units Price Revenue 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: Multiply the two whole columns: =C2:C7*D2:D7
Show answer
=C2:C7*D2:D7in E2. No fill down needed. The formula spills.Why are spilling formulas safer than filling down?
- There is only one formula to check and it resizes itself
- They calculate faster
- They cannot have errors
- They use less color
Show answer
There is only one formula to check and it resizes itself. Filled formulas can drift when someone edits one row.
List each region once, sorted A to Z, 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: =SORT(UNIQUE(B2:B7))