Key takeaways
- One dynamic array formula can return many values into a spill range.
- Always supply an empty-result value to FILTER to avoid #CALC! errors.
- SORT column numbers refer to the array, not the worksheet.
- Leave space around a spill and reference it with the hash operator.
On this page
One formula, a block of results
A dynamic array formula returns a set of values and spills them into neighbouring cells. The formula lives in one cell; the result occupies a spill range. A reference to the whole result uses the hash operator, so D2# refers to everything produced by the formula in D2.
The grid must be clear. If any cell in the intended spill area contains a value, Excel returns #SPILL! and places nothing. Merged cells, a table boundary, or a protected cell can block the spill in the same way. Clear the area or move the formula; do not delete the error and hope.
FILTER: select rows with a rule
FILTER takes an array, a condition, and an optional value for an empty result. The condition must be the same height as the data. For two conditions, multiply the tests; for either condition, add them. Multiplication acts as AND because TRUE times TRUE is 1, while TRUE times FALSE is 0.
Always provide the third argument. Without it, a filter that matches nothing returns #CALC!, which looks like a broken workbook. A fallback held in a cell makes an empty result part of the design rather than an accident.
SORT and SORTBY: order the result
SORT reorders an array by a column index and direction. SORTBY sorts by one or more external arrays, which is useful when the sort key is not part of the result. Both can wrap a FILTER, so the visible list stays selected and ordered without changing the source.
Sort index numbers refer to columns in the array being sorted, not to the worksheet. That distinction causes most errors: after FILTER returns three columns, SORT sees those three, not the original layout from A to Z. Count the columns in the array in front of you.
| Pattern | Formula shape | Use |
|---|---|---|
| Filter then sort | SORT(FILTER(data, condition), 2, -1) | A shortlist in a chosen order. |
| Distinct sorted list | SORT(UNIQUE(range)) | Validation lists and summaries. |
| Distinct values that meet a rule | SORT(UNIQUE(FILTER(range, condition))) | Only active customers or open items. |
| Count distinct non-blank values | ROWS(UNIQUE(FILTER(range, LEN(range)>0))) | A true distinct count despite gaps. |
UNIQUE: lists without duplicates
UNIQUE returns the distinct values in an array. By default it compares rows; setting the second argument to TRUE compares columns. The third argument, exactly_once, returns only values that appear once, which is a quick way to isolate one-off records.
UNIQUE does not clean the data. Two spellings of the same customer remain two values, and a trailing space creates a third. Normalise the source first, then apply UNIQUE. For a count of distinct non-blank items, combine ROWS, UNIQUE, and FILTER rather than COUNTA alone.
- Trim text and standardise case before deduplicating.
- Decide whether blanks are a category or a defect.
- Use exactly_once for exceptions, not for a master list.
- Feed validation lists from the spill range, not a fixed copy.
Compose the functions
The functions are most useful in combination. A filtered, sorted, deduplicated list can drive a dashboard selector. A two-way summary can use UNIQUE for row and column labels and SUMIFS for the values. Each piece stays readable because it does one job.
Keep the source range bounded. FILTER over an entire column scans far more cells than necessary and slows every recalculation. Use a table column or a range that ends at the last record. If the workbook is shared, leave empty space to the right and below the spill and protect those cells.
Operational rules for spilled ranges
Treat a spill range as an output, not an input area. Do not type inside it, paste over it, or insert a row through the middle without checking the formula. When the source grows, the spill grows; when the source shrinks, the old cells clear.
A dynamic array inside an Excel Table is a different case. A calculated column expects one result per row, so a formula that spills across columns does not belong there. Keep spill formulas outside tables, or use a per-row formula that returns a single value.
- Leave at least one empty column beside a spill.
- Reference the spill with the hash operator rather than retyping the formula.
- Test an empty filter result and a single-row result.
- Document which cell owns each spill range.
Frequently asked questions
What does #SPILL! mean?+
The formula wants to return several values, but one or more cells in the target area are occupied or blocked. Clear the area, or move the formula to a place with enough room.
Can dynamic arrays live in an Excel Table?+
A table column expects one value per row. A formula that spills across several columns does not fit that structure, so keep spill formulas outside tables.
How are distinct values counted?+
Use ROWS(UNIQUE(FILTER(range, LEN(range)>0))) for non-blank distinct values, or COUNTA(UNIQUE(range)) when blanks are not present.
Need help applying this?
Improve your excel & google sheets.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
Excel data validation for reliable inputs
Data validation prevents many input errors, but only if the rules match the real work. Design lists, ranges, and custom checks that survive paste and handover.
Building a pricing calculator in Excel
A pricing calculator is a small model with large consequences. Separate cost, margin, discount, and quote logic so every price can be explained and checked.