Two Generations of Array Capability in Excel
Array formulas have existed in Excel since the 1990s, but they were always the province of advanced users — requiring an arcane Ctrl + Shift + Enter confirmation and behaving in ways that confused anyone who encountered them without explanation. Dynamic arrays, introduced in Microsoft 365 and now available in Office 2024 Professional Plus, fundamentally changed how arrays work in Excel, making them accessible, intuitive, and dramatically more powerful.
This guide covers both generations: the legacy Ctrl+Shift+Enter (CSE) array formulas you may encounter in older workbooks, and the modern dynamic array functions that should be the default for any new work in Office 2024. Understanding both helps you work with existing spreadsheets and build new ones using the most capable tools available.
Legacy Array Formulas (CSE Arrays)
A traditional array formula performs a calculation on multiple values simultaneously, where a regular formula would return an error or only handle a single value. The classic use case is a SUMPRODUCT-style calculation without SUMPRODUCT:
{=SUM(B2:B100*C2:C100)}The curly braces indicate a CSE array formula — not typed manually but applied by pressing Ctrl + Shift + Enter instead of just Enter when confirming the formula. Excel adds the braces automatically. This formula multiplies each value in B2:B100 by the corresponding value in C2:C100 (creating an array of products), then sums the results — effectively a sum of products without needing the SUMPRODUCT function.
Multi-Cell CSE Arrays
A CSE array formula can also occupy multiple cells simultaneously. Select a range of cells, type the formula, and press Ctrl + Shift + Enter. The same formula is entered in all selected cells, each returning a different element of the array result. This produces results similar to what dynamic arrays now do automatically — but requires pre-selecting the exact output range, which is impractical when you do not know how many results to expect.
Working with CSE Arrays
To edit a CSE array formula, select any cell in the array and press Ctrl + Shift + Enter after editing. You cannot delete individual cells within a multi-cell CSE array — you must select the entire array and delete it as a unit, or use Ctrl+Shift+Enter to re-enter a modified version.
Dynamic Arrays: The Modern Approach
Dynamic arrays in Office 2024 change everything. When a formula returns multiple values, those values automatically “spill” into adjacent cells — filling as many cells as needed without any pre-selection or Ctrl+Shift+Enter. The formula sits in a single cell (the “spill anchor”) and the spilled results appear in the cells to its right or below, highlighted with a blue border when the anchor cell is selected.
The SPILL Error
The only requirement for a dynamic array formula to work is that all the cells it needs to spill into must be empty. If any cell in the spill range contains content, the formula returns a #SPILL! error. Clear the blocking cells and the formula resolves immediately. This makes dynamic array results inherently self-documenting about their size requirements.
The Core Dynamic Array Functions in Office 2024
SORT
Returns a sorted version of an array:
=SORT(A2:C100, 3, -1)Sorts the range A2:C100 by the third column in descending order (-1). The results spill into adjacent cells. Any change to the source data automatically updates the sorted output — no pivot table or manual sort required.
SORTBY
Sorts by one or more external arrays not included in the output:
=SORTBY(A2:A100, B2:B100, 1, C2:C100, -1)Returns the values in A sorted first by B ascending, then by C descending. The sort keys (B and C) do not appear in the output — they merely determine the order.
UNIQUE
Returns unique values from a range:
=UNIQUE(A2:A100)Returns each distinct value in A2:A100 exactly once. Pass TRUE as the third argument to return only values that appear exactly once (removing all duplicates rather than deduplicating):
=UNIQUE(A2:A100, FALSE, TRUE)FILTER
Returns rows matching a condition:
=FILTER(A2:D100, C2:C100="London", "No results")Returns all rows from A2:D100 where column C contains “London”. The third argument specifies what to return if no rows match. Multiple conditions:
=FILTER(A2:D100, (C2:C100="London")*(D2:D100>1000), "No results")The multiplication of condition arrays is an AND operation — both must be true. Use addition (+) for OR logic.
SEQUENCE
Generates a sequence of numbers:
=SEQUENCE(10, 1, 1, 1)Creates a vertical sequence of 10 rows, 1 column, starting at 1, incrementing by 1. For a horizontal sequence: =SEQUENCE(1, 12, 1, 1). For month numbers: the results can be wrapped in DATE to generate a calendar axis.
RANDARRAY
Generates an array of random numbers:
=RANDARRAY(5, 3, 1, 100, TRUE)Returns a 5×3 array of random integers between 1 and 100. Useful for creating test datasets without manually entering values.
LAMBDA: Creating Custom Functions Without VBA
LAMBDA (Office 2024 only among perpetual versions) allows you to define reusable custom functions using only Excel formula syntax — no VBA required. This is one of the most significant additions to the perpetual version in Office 2024.
Basic LAMBDA Syntax
=LAMBDA(parameter1, [parameter2, ...], calculation)A LAMBDA function used directly in a cell does not provide much benefit over a regular formula — the power comes from naming a LAMBDA as a defined name and calling it like a built-in function:
- Go to Formulas > Name Manager > New.
- Name it (e.g.,
TaxAmount). - In the Refers to field, enter the LAMBDA:
=LAMBDA(gross, rate, gross*rate/100) - Click OK.
Now =TaxAmount(B2, 20) returns 20% of the value in B2 — a named, reusable calculation that can be used anywhere in the workbook.
Recursive LAMBDA with LET
LAMBDA supports recursion through a pattern called a recursive named LAMBDA. Combined with LET (which assigns names to intermediate values), this enables genuinely sophisticated custom functions that would previously have required VBA:
=LET(factorial, LAMBDA(n, IF(n<=1, 1, n*factorial(n-1))), factorial(A2))This calculates the factorial of the value in A2 purely in formula syntax. While factorials are a textbook example, the same recursive pattern applies to practical calculations like compound interest, iterative convergence, or hierarchical aggregation.
Combining Dynamic Array Functions
The real power of dynamic arrays emerges when functions are chained together. Each function accepts arrays as inputs, so the output of one becomes the input of the next:
=SORT(UNIQUE(FILTER(A2:A100, B2:B100="Active")))This single formula: filters column A to rows where column B is "Active", removes duplicates from the filtered results, and sorts them alphabetically — the equivalent of several steps of manual data manipulation, updating instantly as the source data changes.
Using the Spill Reference Operator (#)
When referencing a dynamic array's output range in another formula, use the spill operator (#) to refer to the entire spilled range regardless of its current size:
=COUNTA(E2#)If E2 contains a UNIQUE formula that currently spills 15 results, E2# refers to all 15 cells. If the source data changes and UNIQUE now returns 23 results, E2# automatically expands to reference all 23. This eliminates the need to define fixed ranges for downstream calculations on dynamic outputs.
Practical Applications
Dynamic Unique List for Dropdown Validation
=SORT(UNIQUE(DataSheet!$B$2:$B$500))Place this in a reference sheet cell. Use the spill range (e.g., =RefSheet!$A$2#) as the source for a data validation dropdown. The dropdown automatically includes new values added to the source data without any maintenance of the validation rule.
Automated Exception Report
=FILTER(SalesTable, (SalesTable[Value]>10000)+(SalesTable[Status]="Overdue"), "No exceptions")Returns all rows where the value exceeds £10,000 OR the status is Overdue — a live exception report that updates as data changes.
Pivot-Style Summary Without a Pivot Table
=SUMIFS(SalesTable[Value], SalesTable[Region], UNIQUE(SalesTable[Region]))When SUMIFS receives an array (the UNIQUE result) as its criteria, it returns an array of sums — one per unique region. Combined with SORT to order by value, this replicates a basic pivot table as a live formula.
Compatibility Considerations
Dynamic arrays are a major consideration for workbook sharing. If a workbook containing dynamic array formulas is opened in Excel 2019 or earlier, or in Google Sheets, the spilled results are not generated — the anchor cell shows the formula but spilled cells show a #N/A error or are blank. For workbooks shared with users on older Office versions, use legacy approaches (SUMPRODUCT, INDEX/MATCH, manual lists) rather than dynamic array functions.
For workbooks used exclusively within your organisation on Office 2024 or Microsoft 365, dynamic arrays should be the default approach for any task involving multiple values, sorting, filtering, or deduplication. They produce more readable, maintainable formulas than the CSE alternatives, and the automatic spilling behaviour eliminates a significant source of formula errors.
Performance Considerations for Large Dynamic Array Formulas
Dynamic array formulas that spill large result sets can affect workbook calculation performance. Excel recalculates spilled arrays whenever the source data changes, which is generally instantaneous for datasets of typical business size (thousands to low hundreds of thousands of rows). For very large datasets:
- Use Table references — Structured table references recalculate more efficiently than absolute range references.
- Avoid volatile functions in arrays — Functions like TODAY(), NOW(), RAND(), and INDIRECT() recalculate on every keystroke. If a dynamic array formula uses these, the entire spilled range recalculates constantly. Extract the volatile function to a single helper cell and reference that cell in the array formula instead.
- Consider XLOOKUP over FILTER for single-value lookups — FILTER is more flexible but slightly heavier than XLOOKUP for scenarios where a single matching row is needed. Use the right tool for each specific task.
Migrating Existing CSE Array Formulas to Dynamic Arrays
If you have existing workbooks built with legacy CSE array formulas, gradual migration to dynamic arrays is worthwhile when the workbook is maintained. The most common migrations:
{=SUM(A:A*B:B)}→=SUMPRODUCT(A:A,B:B)(works in all versions) or retain as-is for compatibility- Multi-cell CSE arrays returning lists → Replace with SORT, FILTER, or UNIQUE as appropriate
- Complex INDEX/MATCH lookups → Replace with XLOOKUP for readability and performance parity
Do not migrate all CSE formulas immediately — test each replacement thoroughly, as dynamic array formulas may produce differently shaped results in edge cases (empty source ranges, single-row sources) that require additional handling with IFERROR or IF(ISBLANK()) wrappers.
Dynamic Arrays vs Power Query: Choosing the Right Tool
Both dynamic array formulas and Power Query (available via Data > Get & Transform) can transform and reshape data in Excel. Understanding when each is appropriate prevents using the wrong tool for the task:
Use Dynamic Arrays When
- The data is already in Excel and does not need external sourcing or refreshing
- You need formula-driven calculations that update instantly as source data changes
- The transformation is relatively simple: filtering, sorting, deduplicating, or generating reference lists
- The output needs to be embedded in the same sheet as the source data with formula-level integration
Use Power Query When
- Data is imported from external sources: CSV files, databases, web pages, other workbooks
- Transformations are complex: unpivoting, merging multiple tables, type conversions, column splitting
- The data needs regular refresh from a changing source
- The transformation logic should be documented and repeatable across multiple similar datasets
For the most powerful data workflows, Power Query and dynamic arrays complement each other: Power Query cleans and imports the data, and dynamic array formulas drive the analysis and reporting layer on top of the Power Query output tables. Office 2024 Professional Plus includes both tools fully, making it the most capable perpetual Office version for data work at any price point.
