Dynamic array foundations
Let one formula return a complete list while keeping its output space clear.
By the end, you’ll be able to:
- Recognize a formula that returns more than one value
- Predict a spill range and diagnose blocked output
- Separate the source table from a formula-driven view
One formula can return many cells
Traditional scalar formulas return one value to one cell. A dynamic array formula can return a column, row or rectangle. Excel places the results in neighboring cells; this is called spilling. You enter the formula once in the top-left output cell.
If A2:A5 contains West, East, West and North, =UNIQUE(A2:A5) produces West, East and North in three rows. It updates when the referenced values change. It does not create new input records or permanently delete duplicate source rows.
Worked example: a live sorted list
With numeric sales 120, 80 and 200 in B2:B4, enter =SORT(B2:B4,1,-1) at D2. The results 200, 120 and 80 occupy D2:D4. The formula lives in D2; D3 and D4 belong to its output.
In Excel, select the anchor cell to edit the formula. A reference such as D2# refers to its current spill range. That range may grow or shrink as the formula's result changes. The browser tasks return arrays directly and do not simulate editing a worksheet spill range or the # reference operator.
Diagnose #SPILL! before changing the formula
- Select the formula cell and inspect the intended output boundary.
- Check for existing values, formulas that look blank, merged cells or other occupied output cells.
- Move the output to an empty area or deliberately clear the obstruction after checking its contents.
- Check that the formula is outside an Excel table and has room before the worksheet edge.
Spilled formulas can refer to source tables but are not supported inside the table itself. Put the view alongside the table or on a separate sheet. Leave space for the largest reasonable result.
A blocked spill is different from no matching FILTER records, a missing lookup or a formula calculation error. Read the specific message before taking action.
Keep inputs and views separate
Use a table for records that people enter or refresh. Use a spill formula for a view derived from those records. Label the view's selection criteria and tell readers when no records qualify.
Fixed references such as A2:A5 do not automatically include row 6. In a real workbook, an Excel table with structured references can expand the source when records are added. Our browser exercises use bounded fixed ranges so the data being checked is explicit.
Linked dynamic arrays between workbooks have limitations when the source workbook is closed. A self-contained workbook is easier for learners to test and share.
Check the recipient’s Excel version
Excel versions: Dynamic arrays, FILTER, SORT, UNIQUE, XLOOKUP and LET are available in Microsoft 365, Excel 2021 and Excel 2024, including supported Mac versions. LAMBDA requires Microsoft 365 or Excel 2024. The browser exercises work without installing Excel; they implement the lesson's stated subset rather than the complete Excel calculation engine.
Opening a workbook in an older version can change which formulas calculate correctly. Do not assume a working formula on your screen proves the recipient can use it. If their version lacks these functions, provide a compatible calculated copy or rebuild the required workflow with tools they have.
Use Enter to confirm these dynamic array formulas in a supported version. Legacy Ctrl+Shift+Enter array formulas follow different editing rules.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
A spill is one formula’s complete result area. Keep that area clear and separate from source records.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.