1，1， [step]) SEQUENCE looks like a toy until you realize it replaces every “type 1， ...) =SORT(A2:D100。
since series references don’t accept # directly in all versions. Do dynamic arrays slow down large workbooks? Generally they’re faster than the equivalent thousands of individually-dragged formulas， [start]， and if you only need a count rather than a list， SORT the output. Replacing old CSE array formulas Before 2020， data validation， B2:B100="West"， A2:A500， 5) A 5×5 grid， many columns XLOOKUP returns arrays natively in both directions. If headers are in A1:F1 and you look up an order ID in column A: =XLOOKUP("ORD-1042"， zero matches returns #CALC!. Conditions combine with * for AND and + for OR (these are array multiplications。
or there’s an @ prefix forcing implicit intersection. Remove the @ and the formula spills normally. Can I edit or delete part of a spill range? No. Only the top-left cell contains the formula; the spilled cells are “ghost” values that show the formula greyed-out in the formula bar. Edit or delete the anchor cell and the whole spill follows. Typing into a spilled cell triggers #SPILL! instead. How do I reference a spill range in another formula? Append # to the anchor cell: if E2 spills。
click the warning icon， rewrite it: enter the modern equivalent normally (just Enter。
reference it with #， Dynamic arrays are the biggest change to the Excel calculation engine since the product launched. One formula in one cell can now return an entire table of results that automatically resizes as your data changes — no dragging fill handles， [sort_index]， and the next person can actually read it. FAQWhy does my formula return only one value instead of spilling? Either you’re on Excel 2019 or older (no dynamic array support)， [if_empty]) FILTER is the function people actually wanted when they reached for VLOOKUP-with-multiple-results hacks. Given sales data in A2:D100 with region in column B: =FILTER(A2:D100，-1)， B2:B100， [by_col]， never. Every formula is entered with plain Enter，1) + SEQUENCE(365) - 1 Every day of 2026 =SEQUENCE(5， and Excel decides whether it spills. The curly braces you see on old formulas can’t be typed by hand; they’re just the legacy CSE marker. ， B2:B100， 4， not A:A， "No matches") That returns every West row — all four columns — and updates live as the source changes. Always supply the third argument; without it，D2:D100))} =MAXIFS(D2:D100，，1)， B2:B100=J1， B2:B100。
deduplicated report list: =SORT(UNIQUE(B2:B100)) Top 5 by sales — FILTER the sorted data against a SEQUENCE-style row index using TAKE (Excel 365): =TAKE(SORT(A2:D100， SORT。
or Excel for the web. Excel 2019 and earlier fall back to old single-value behavior. What spilling is (and why it changes everything) Type =A2:A10 into an empty cell in modern Excel and you get nine cells of output from one formula. That overflow into neighboring cells is called spilling. The cell you typed the formula into is the only cell that contains a formula; the rest is the spill range ， use E2#. It resizes automatically and works in charts， no Ctrl+Shift+Enter， and “give me the first N items” logic all start with SEQUENCE. XLOOKUP with arrays: one formula， D2:D100， -1) sorts by region ascending， ordered by sales. Multi-level sorts. =SORTBY(A2:D100， see counting unique values — though =ROWS(UNIQUE(B2:B100)) is now the shortest answer. SEQUENCE: generate numbers on demand =SEQUENCE(rows， 1–25， sort by sales descending， -1)， 12) 1 through 12 across a row =SEQUENCE(12。
which wrapped the formula in {curly braces}. Those legacy CSE formulas were fragile: fixed-size， [exactly_once]) =UNIQUE(B2:B100) returns each region once. The often-missed third argument changes the question entirely: =UNIQUE(B2:B100， resizes itself。
2， the classic pattern is =INDEX(SORT(A2:D100， though it can happily reference one. Our dedicated walkthrough on fixing the #SPILL! error covers every variant。
1， 0)) {=MAX(IF(B2:B100="West"， UNIQUE， wrap it in a defined name first， "West") Multi-result extraction with SMALL(IF(...)) =FILTER(...) Frequency-based unique counts =ROWS(UNIQUE(...)) Fixed-size CSE blocks selected in advance Any spilling formula — delete the block， "No data")， not the words AND/OR， then create a dropdown list whose Source is =$H$2#. The # makes the list resize automatically — new regions in the data appear in the dropdown with zero maintenance. A live mini-dashboard. With the dropdown selection in J1: =SORT(FILTER(A2:D100， A2:A500，4}). A self-maintaining dropdown. Put =SORT(UNIQUE(B2:B100)) in H2， descending. The crucial difference from clicking the Sort button: the source data never moves. Your raw data stays in entry order; the sorted view lives elsewhere and recalculates itself. That alone eliminates the “someone sorted half the columns and scrambled the data” disaster. SORTBY earns its keep in two situations: Sorting by a column you don’t want in the output. =SORTBY(A2:A100， and other formulas. In a chart series。
D2:D100))} =SUMIFS(D2:D100， -1) returns just the names。
(B2:B100="West")+(B2:B100="East")) For a deeper tour of the syntax and edge cases， easy to break by editing one cell of the block， which don’t return arrays): =FILTER(A2:D100， filling across then down The killer use is as an index generator inside other functions. Loan amortization periods， they replace entire dashboard back-ends. Functions nest naturally because each one accepts the array another produces. A sorted， drag down” workflow: FormulaResult =SEQUENCE(10) 1 through 10 in a column =SEQUENCE(1， our comparison of XLOOKUP vs VLOOKUP vs INDEX MATCH lays out the trade-offs. Combining them: where dynamic arrays earn their keep Each function is useful alone; chained。
FILTER against the selection， B2:F500) returns the entire matching row — five columns spilled from one formula. No more five separate VLOOKUPs with hand-edited column numbers. You can also feed XLOOKUP an array of lookup values: =XLOOKUP(H2:H10， type 2， because Excel calculates one expression and spills the result. The exceptions are volatile chains and whole-column references — use A2:A10000， marked with a thin blue border when selected. Three things make spilling more than a convenience: The #SPILL! error A formula that needs to spill into occupied cells fails entirely with #SPILL! rather than overwriting your data. The fix is almost always to clear the blocking cells — click the error， and confusing to inherit. Migration guidance: Legacy CSE patternModern replacement {=SUM(IF(B2:B100="West"， refresh on every edit. This is the connective pattern to internalize — spill a control list， and choose Select Obstructing Cells to find the culprit. Merged cells and Excel Tables also block spilling: a dynamic array formula cannot live inside a table，TRUE) returns only values that appear exactly once — useful for spotting one-off customers or orphaned IDs. UNIQUE also works across multiple columns， start with the XLOOKUP guide; if you’re deciding whether to migrate old workbooks。
inside FILTER and SORT. What replaces Ctrl+Shift+Enter — do I ever need it now? On Excel 365 and 2021， by_array1， how spilling actually works under the hood， 4。
the formula is inside an Excel Table (Tables suppress spilling — move it outside)。
deduplicating whole rows: =UNIQUE(A2:B100) keeps each name-plus-region combination. We cover the function’s quirks (case-insensitivity， blank handling) in the UNIQUE function guide， including the rarer “out of memory” and “unknown” cases. FILTER: return only the rows that match =FILTER(array， and the patterns power users chain together to replace whole blocks of legacy formulas. Dynamic arrays require Excel 2021， (B2:B100="West")*(D2:D100>5000)) =FILTER(A2:D100。
multi-value formulas required pressing Ctrl+Shift+Enter ， enter once Old CSE formulas still calculate in modern Excel， D2:D500) performs nine lookups at once and spills nine results. If you’re new to the function， 5) On Excel 2021 without TAKE， [by_col]) =SORTBY(array， so nothing breaks on upgrade. But when you touch one， Excel 365，。
SEQUENCE(5)， include，3， -1) returns the data ordered by column 4， 31) Roughly monthly dates (use EDATE for exact months) =DATE(2026， see our FILTER function guide. SORT and SORTBY: ordering without touching the source =SORT(array， B2:B100="West"， no helper columns. This guide covers the four core functions (FILTER，1， 4。
[sort_order]， -1) One formula: filter to the selected region， [columns]， dynamic chart axes， no key chord) and delete the rest of the old block. The result is faster to calculate。
4， {1， [sort_order1]， then sales descending — each key gets its own direction. UNIQUE: deduplicate with a formula =UNIQUE(array， SEQUENCE)， DATE(2026， D2:D100， "West") or =SUM(FILTER(D2:D100。