Array formulas let a single formula process multiple values at once and return multiple results. Before Excel 365, arrays were a niche power-user skill requiring a special key combo. Dynamic arrays in Excel 365 changed that — functions like FILTER, SORT, and UNIQUE work as arrays automatically, spilling results into neighboring cells without any extra steps.
What Are Arrays in Excel?
An array is a collection of values treated as a single unit inside a formula. Think of it as Excel doing a loop for you.
Example without arrays: To sum only values where a category matches "Sales", you'd write: =SUMIF(A2:A100,"Sales",B2:B100) How arrays work under the hood: SUMIF multiplies a logical test (is A = "Sales"?) by the corresponding B values and sums the results — essentially an array operation the function handles internally.When you write your own array formulas, you harness this same mechanism for tasks where no built-in function covers the need.
Legacy CSE Array Formulas (Excel 2019 and Earlier)
Before dynamic arrays, you entered array formulas by pressing Ctrl+Shift+Enter (CSE) instead of just Enter. Excel wrapped the formula in curly braces {} to signal it was an array formula.
Example: Count cells in B2:B100 that are greater than 100 AND in column A labeled "North": {=SUM((A2:A100="North")*(B2:B100>100))}The curly braces are added automatically by Excel when you press Ctrl+Shift+Enter — never type them manually.
Key limitation: A CSE array formula occupies exactly one cell. It cannot spill results across multiple cells (that's a dynamic array feature). When you still see CSE arrays: Older workbooks, templates, and tutorials written before 2020 use CSE arrays extensively. You need to recognize them. If you're on Excel 365, you can usually replace them with cleaner dynamic array formulas.Dynamic Arrays (Excel 365 and Excel 2021)
Dynamic arrays automatically spill their results into adjacent cells. No Ctrl+Shift+Enter required. If a formula returns 10 values, it fills 10 cells automatically.
Spill range: The blue border around cells where a dynamic array has spilled. If another cell is blocking the spill, you get a #SPILL! error — move or delete the blocking cell. Reference a spill range: Use the formula's cell address followed by #. For example, if your formula is in A1 and spills down, =A1# refers to the entire spill range dynamically.FILTER — Extract Rows That Match Conditions
=FILTER(array, include, [if_empty])FILTER returns only the rows from a range where the condition is TRUE.
Example: Show only rows where column C (Status) equals "Open": =FILTER(A2:D100, C2:C100="Open", "No results")The third argument ("No results") displays if no rows match — prevents a #CALC! error.
Multiple conditions (AND): =FILTER(A2:D100, (C2:C100="Open")*(B2:B100>1000), "No results")Multiply conditions together for AND logic. Use + for OR logic.
Multiple conditions (OR): =FILTER(A2:D100, (C2:C100="Open")+(C2:C100="Pending"), "No results")FILTER is the replacement for complex SUMIF/COUNTIF combinations and eliminates the need to manually maintain separate filtered tables.
SORT and SORTBY — Sort Data with a Formula
=SORT(array, [sortindex], [sortorder], [by_col])Sort_order: 1 = ascending (default), -1 = descending
Example: Sort range A2:D100 by column 3 (descending): =SORT(A2:D100, 3, -1) =SORTBY(array, byarray1, [sortorder1], [byarray2], [sortorder2], ...)SORTBY is more flexible — sort by an array that isn't even in the output.
Example: Sort a product list by a separate score column that you don't want in the output: =SORTBY(A2:B50, C2:C50, -1)This returns columns A and B sorted by the values in column C (descending), without including C in the output.
Combine with FILTER: =SORT(FILTER(A2:D100, C2:C100="Open"), 2, 1)Filter to "Open" rows, then sort that result by column 2 ascending — all in one formula.
UNIQUE — Extract a Distinct List
=UNIQUE(array, [bycol], [exactlyonce])Returns a deduplicated list of values. No helper columns, no Remove Duplicates, no pivot table.
Example: Get a unique list of customers from A2:A500: =UNIQUE(A2:A500) Unique combinations across columns: Set by_col to FALSE (default) and pass multiple columns: =UNIQUE(A2:B500) — returns unique Name + Region combinations Values that appear exactly once (not just distinct values): Set exactly_once to TRUE: =UNIQUE(A2:A500, FALSE, TRUE) — returns values with no duplicates at all Combined with SORT for a clean dropdown source: =SORT(UNIQUE(A2:A500))SEQUENCE — Generate Number Series
=SEQUENCE(rows, [cols], [start], [step])Generates a 2D array of sequential numbers.
Examples:- =SEQUENCE(10) → numbers 1 through 10 in a column
- =SEQUENCE(5,3) → a 5-row × 3-column grid of numbers 1–15
- =SEQUENCE(12,1,1,1) → months 1–12
- =SEQUENCE(10,1,0,5) → 0, 5, 10, 15... (10 values, step 5)
XLOOKUP with Arrays
XLOOKUP natively handles array lookups, replacing both VLOOKUP and HLOOKUP.
=XLOOKUP(lookupvalue, lookuparray, returnarray, [ifnotfound], [matchmode], [search_mode]) Return multiple columns at once: Set return_array to a multi-column range: =XLOOKUP(G2, A2:A100, B2:D100) — looks up G2 in column A and returns the entire B:D row Multiple lookups at once (array of lookup values): =XLOOKUP(G2:G10, A2:A100, B2:B100) — returns 10 results for 10 lookup values simultaneously Approximate match for ranges: The matchmode parameter (4th argument after ifnot_found):- 0 = exact match (default)
- -1 = exact or next smaller
- 1 = exact or next larger
Frequently Asked Questions
What is the #SPILL! error and how do I fix it?
A #SPILL! error means the formula is trying to spill into cells that aren't empty. Click the formula cell — blue dotted lines show where it wants to spill. Delete or move whatever is in those cells. Common cause: a value hiding in a cell that looks empty (a space character). Select each blocking cell and press Delete.
Do dynamic array formulas work in older versions of Excel?
No. FILTER, SORT, SORTBY, UNIQUE, and SEQUENCE require Excel 365 or Excel 2021. In Excel 2019 or earlier, these functions show #NAME? errors. If you share files with users on older versions, they cannot use or edit these formulas. For compatibility, CSE array formulas are the alternative, though less elegant.
Can I use FILTER to return data to a specific shape?
Yes. FILTER returns the same columns as the input array. To return specific columns only, wrap with CHOOSE or use multiple FILTER calls. In Excel 365, you can also pass a horizontal array of column indices to CHOOSECOLS:
=CHOOSECOLS(FILTER(A2:D100, C2:C100="Open"), 1, 3) — returns only columns 1 and 3 of the filtered result.
Is there a performance difference between CSE and dynamic arrays?
Dynamic array formulas are generally faster and more efficient than their CSE equivalents because they were designed from scratch for the modern Excel calculation engine. Avoid volatile functions like OFFSET or INDIRECT inside array formulas — they recalculate on every change, regardless of whether the input changed.
Can I use UNIQUE or FILTER as the source for a data validation dropdown?
Not directly — data validation's Source field doesn't accept spill range references (=A1#). Workaround: name your UNIQUE/FILTER spill range using OFFSET with COUNTA to create a dynamic named range, then reference that named range in validation. In Excel 365, creating a Table over the spill output and referencing the Table column in validation is a cleaner approach.