Course / Modern Excel

Spilling arrays: UNIQUE and SORT

Write one formula that fills a whole list, remove duplicates with UNIQUE, sort with SORT, and refer to the results with D2#. Intermediate · 5 min · Dynamic arrays, UNIQUE, SORT, Spill references (#)

Most formulas return one value. Dynamic array formulas can return a whole list, which Excel spills into the cells below (or beside) the formula.

• You type the formula once, in the top cell. The cells under it fill themselves.
• If anything is in the way, the formula shows #SPILL! until you clear the space.
• D2#, the first cell plus a hash, refers to the whole spilled list, whatever its size.

The sheet is a plant shop's order log.

Steps

1. Which plants have people ordered? In D2, type =UNIQUE(B2:B13) and press Enter. You only type in one cell; the rest of the list spills down by itself.

2. Now the customers. In E2, sort the names A to Z with =SORT(A2:A13).

3. Edit E2 so it removes the duplicates first, then sorts: =SORT(UNIQUE(A2:A13)). Watch the spill range shrink to fit.

4. How many different plants are there? In H2, count the list in column D with =COUNTA(D2#). The # after D2 means the whole spill range that starts in D2.

5. Elena just changed her order. Replace Snake plant in B13 with Cactus, then watch the list in column D and the count in H2.

Remember

• A dynamic array formula is typed once and spills into as many cells as it needs.
• UNIQUE removes duplicates; SORT orders a list; nest them as SORT(UNIQUE(...)).
• D2# refers to the whole spill range that starts in D2.
• Keep the cells below a spilling formula empty, or it shows #SPILL!.

Previous: Compare investments with NPV and IRR · Next: FILTER: rows that match