For decades a spreadsheet formula returned a single value in a single cell. Dynamic arrays changed that rule: a formula written in one cell can return an entire list, which automatically "spills" into the cells below and alongside. They are available in Excel for Microsoft 365 and the most recent versions, in Excel for the web, and similar functions exist in Google Sheets and recent versions of LibreOffice Calc. They change the way reports and lists are built.
The idea of spilling
If you type =A2:A10 in an empty cell, in a version with dynamic arrays you do not get one value but nine: the formula fills the cells starting from where you wrote it with a copy of A2:A10. Only the first cell contains the formula; the others show the result, with a thin border marking the spill area.
Two practical consequences:
- if any cell in the destination area is not empty, the #SPILL! error (#ESPANSIONE!) appears: just clear it;
- to refer to the whole result, whatever its length, you use the
#operator: if the formula is in F2,F2#means the whole spill area.
UNIQUE: the list of distinct values
=UNIQUE(B2:B5000)
returns the list of agents without repetitions, in the order they appear. When a new agent turns up in the data, the list grows by itself.
Two optional arguments deserve attention: the second lets you compare columns instead of rows, the third, if set to TRUE, returns only the values that appear exactly once. That last option is handy for finding codes that are not repeated in a list.
SORT and SORTBY
=SORT(UNIQUE(B2:B5000))
returns the agents in alphabetical order. SORT also accepts the index of the column to sort by and the direction: =SORT(A2:D5000, 4, -1) sorts the whole list by the fourth column in descending order.
SORTBY (ORDINA.PER) sorts one range based on another, even if the latter is not part of the result. For example, listing product names sorted by revenue without showing the revenue:
=SORTBY(A2:A200, D2:D200, -1)
FILTER: extracting the rows you need
FILTER is probably the most useful of the three:
=FILTER(A2:D5000, C2:C5000="North", "No results")
returns all rows where the area is North. The third argument is the value shown when no row meets the condition; without it you get an error.
Several conditions
Conditions are combined with multiplication (for AND) and addition (for OR), wrapping each one in brackets:
=FILTER(A2:D5000, (C2:C5000="North")*(D2:D5000>1000))
=FILTER(A2:D5000, (C2:C5000="North")+(C2:C5000="Centre"))
The logic is simple: each condition produces a series of TRUE and FALSE values, that is 1s and 0s. The product is 1 only if both are true; the sum is at least 1 if at least one is. The AND and OR functions do not work in this context, because they return a single result for the whole range.
Combining the functions
The real power lies in nesting them. A few examples:
| Goal | Formula |
|---|---|
| Distinct customers in the North area, alphabetically | =SORT(UNIQUE(FILTER(B2:B5000, C2:C5000="North"))) |
| The five largest orders | =TAKE(SORT(A2:D5000, 4, -1), 5) |
| Number of distinct customers | =COUNTA(UNIQUE(B2:B5000)) |
TAKE (PRENDI) is available in the most recent versions of Excel; where it is missing, you can get the same result with INDEX and SEQUENCE.
A report that updates itself
A very practical layout: in F2 type =SORT(UNIQUE(B2:B5000)) for the list of agents; in G2 type
=SUMIFS(D2:D5000, B2:B5000, F2#)
Thanks to F2#, SUMIFS calculates a total for each agent in the list and column G spills by itself too. Add an agent to the data and the summary grows without you having to copy any formulas.
Precautions
- Compatibility. A file using FILTER opened in an Excel version without dynamic arrays shows errors or converted formulas. If the file travels outside your group, check the versions in use.
- Do not type inside the spill area. Any value you enter blocks the whole result.
- Excel tables do not accept spilling formulas inside them: put dynamic formulas outside the table, referring to its columns.
- Whole columns with care. Formulas like
FILTER(A:D, ...)on entire columns work, but on large files they slow recalculation.
In my work dynamic arrays have almost completely replaced the helper columns that used to be needed to extract lists: sheets are shorter and it is clearer where each result comes from.
In short
- A dynamic array formula returns several values that spill into nearby cells.
- UNIQUE removes repetitions, SORT and SORTBY sort, FILTER extracts rows.
- In FILTER, conditions are combined with
*for AND and+for OR. - The
#operator refers to the whole spilled result. - Clear the destination area to avoid #SPILL! and check compatibility.
