Excel exercises
Excel exercises: Dynamic arrays
A current version of Excel lets one formula return a block of cells, and this group uses that for three jobs.
UNIQUE returns one copy of each distinct value in the order it met them, and an optional third argument keeps only values that appear once. FILTER returns the rows that meet a condition and stays live as the data changes. SORT returns a new block in the order you ask for and leaves the source alone. Typing inside a spilled block raises #SPILL!.
Three exercises at level 3, all graded on the block a formula returns.
The spill needs the room below it. Leave one row of data in the way and the whole result becomes an error rather than a short list. That is why the group checks where the answer lands and not only what it says. A spilled range holds no formula of its own, so typing over a cell inside it destroys the block.
A stale exercise is one you finished two weeks ago or longer. One that needs practice took a hint, or more than one try.
0 of 3 complete
- UNIQUE: one row per distinct value Paid Count the distinct couriers in a column without retyping the list.
- FILTER: the rows that meet a condition Paid Add the rows that meet a condition without filtering the sheet first.
- SORT and spilled ranges Paid Rank a table without touching it, then read one cell out of the ranked block.