Excel Dynamic Array Formulas: A Complete Guide

Dynamic array formulas changed the way modern Excel formulas work. Instead of writing one formula, copying it down, and hoping every copied reference stays correct, you can write one formula that returns many results at once.

If you use Microsoft 365 or a newer Excel version with dynamic arrays, functions like FILTER, SORT, UNIQUE, SEQUENCE, and TEXTSPLIT can build live lists, reports, and helper tables without manual copy-paste work.

1. Excel Version Compatibility

Dynamic arrays are available in Microsoft 365 and newer perpetual versions of Excel, but individual functions do not all have the same version support. Before sharing a workbook with other people, check which Excel version they use.

If you need to support Excel 2016 or Excel 2019, avoid relying on dynamic array formulas in shared workbooks. For those versions, use copied formulas, helper columns, Power Query, or older lookup and filter patterns instead.

2. What Is a Dynamic Array Formula?

A dynamic array formula is a formula that can return more than one value. When the result has multiple values, Excel places them into neighboring cells. This automatic expansion is called spilling.

For example, this formula creates a list of five numbers:

=SEQUENCE(5)

Enter it in one cell, and Excel fills the cells below with 1, 2, 3, 4, and 5.

The important idea is simple: one formula can produce a range.

3. Dynamic Arrays vs Traditional Array Formulas

Older array formulas often required Ctrl + Shift + Enter, and many users avoided them because they felt fragile. Dynamic arrays are different: you type the formula normally and press Enter.

This matters most when your source data changes. If a filtered list has three matching rows today and twelve tomorrow, the dynamic array output can resize without rebuilding the formula.

4. Understand the Spill Range

When a formula spills, the full output range is called the spill range. Excel shows a border around the spilled result when you select the formula cell.

Suppose F2 contains:

=UNIQUE(B2:B12)

If the formula returns six unique departments, the spill range might be F2:F7.

You can refer to the entire spilled result by adding # after the first cell:

=COUNTA(F2#)

That formula counts every item currently returned by the dynamic array in F2. If the spilled list grows, F2# grows with it.

Spill references are useful when building dashboards, charts, validation lists, or formulas that depend on a dynamic result.

5. Create Number Lists with SEQUENCE

SEQUENCE creates a list or grid of numbers. It is useful for numbered rows, date schedules, month labels, test data, and repeatable helper arrays.

The syntax is:

=SEQUENCE(rows, [columns], [start], [step])

Examples:

For a simple project schedule, this formula creates the next seven dates:

=TODAY()+SEQUENCE(7)-1

Format the results as dates, and you have a live seven-day calendar that updates automatically.

6. Filter Rows with FILTER

FILTER returns only the rows or columns that meet a condition. It is one of the most useful dynamic array functions because it replaces many manual filter, copy, and paste workflows.

The syntax is:

=FILTER(array, include, [if_empty])

Example sales table:

To return orders where the amount is greater than 1000:

=FILTER(A2:D6, D2:D6>1000, "No matching orders")

Result:

The condition D2:D6>1000 creates a true-or-false test for each row. FILTER returns the rows where the test is true.

7. Filter with Multiple Conditions

For an AND condition, multiply the tests. This returns rows where both tests are true.

To return West region orders above 1000:

=FILTER(A2:D6, (B2:B6="West")*(D2:D6>1000), "No matching orders")

For an OR condition, add the tests. This returns rows where at least one test is true.

To return orders from East or West:

=FILTER(A2:D6, (B2:B6="East")+(B2:B6="West"), "No matching orders")

If FILTER returns more rows than expected, check each condition separately in a helper column. Most mistakes come from extra spaces, mixed data types, or a condition pointing at the wrong range.

8. Get Unique Lists with UNIQUE

UNIQUE returns distinct values from a range. It is perfect for creating a live list of departments, customers, regions, products, or categories.

Using the sales table above, this formula returns each region once:

=UNIQUE(B2:B6)

Result:

You can combine UNIQUE with SORT to create a cleaner list:

=SORT(UNIQUE(B2:B6))

That pattern is especially useful for dropdown source lists. If the source data gains a new region, the unique sorted list updates automatically.

For cleaner dropdown setup, see the Excel data validation guide.

9. Sort Formula Results with SORT and SORTBY

SORT sorts a range by column number. SORTBY sorts one range by another range. Both return dynamic arrays.

The syntax for SORT is:

=SORT(array, [sort_index], [sort_order], [by_col])

To sort the sales table by Amount from largest to smallest:

=SORT(A2:D6, 4, -1)

Use SORTBY when the sort range is easier to point at directly:

=SORTBY(A2:D6, D2:D6, -1)

Both formulas produce a sorted copy of the data. They do not rearrange the original table.

For manual sorting tools and multi-column sort rules, see Excel sorting.

10. Split Text with TEXTSPLIT

TEXTSPLIT splits text into separate cells using a delimiter. It is useful when imported data puts multiple values into one cell.

Suppose cell A2 contains:

This formula splits the text into columns:

=TEXTSPLIT(A2, " | ")

Result:

You can also split by row delimiter. If a cell contains comma-separated items, this formula returns one item per row:

=TEXTSPLIT(A2,, ", ")

When splitting messy imported text, clean extra spaces first with TRIM or normalize the delimiter before splitting.

11. Combine Dynamic Array Functions

Dynamic array formulas become most powerful when you combine them. Start with a clear goal, then stack functions from the inside out.

Goal: return a sorted unique list of reps who have orders above 1000.

=SORT(UNIQUE(FILTER(C2:C6, D2:D6>1000, "")))

Read it from the inside:

Another useful pattern is a top-N report in Excel versions that support TAKE:

=TAKE(SORTBY(A2:D20, D2:D20, -1), 5)

This sorts the table by Amount descending, then returns the top five rows.

If nested formulas become hard to read, use the LET function to name each piece. See the LET function guide for that workflow.

12. Common Dynamic Array Errors

Dynamic arrays are easier than old array formulas, but they introduce a few new things to watch.

Example of a safer FILTER formula:

=FILTER(A2:D100, D2:D100>1000, "No matching orders")

The third argument prevents an empty result from turning into #CALC!.

For a broader list of formula errors and fixes, see Excel formula errors.

13. Best Practices for Dynamic Array Formulas

Use these habits to keep dynamic array workbooks easy to maintain:

• Put source data in an Excel Table when possible so ranges expand cleanly
• Leave empty space below and to the right of formulas that may spill
• Use F2# style spill references when another formula needs the full result
• Add [if_empty] to FILTER so empty results are user-friendly
• Build complex formulas one function at a time before nesting them
• Avoid placing important manual data where a formula may need to spill later

If your source ranges contain many blank rows around the real data, review TRIMRANGE and trim references before building large dynamic array models.

Dynamic arrays are not only a new set of functions. They are a different way to think about worksheet design: one formula can generate the helper table, report, or list that used to require copied formulas and manual cleanup.

14. Summary

Start with these patterns:

=FILTER(data_range, condition_range=value, "No results")
=SORT(UNIQUE(list_range))
=SEQUENCE(number_of_rows)
=TEXTSPLIT(text_cell, delimiter)
=ROWS(spill_start_cell#)

Use FILTER when you need matching rows, UNIQUE when you need a clean list, SORT when the output should be ordered, SEQUENCE when you need generated numbers or dates, and TEXTSPLIT when imported text needs to become columns or rows. If you use TEXTSPLIT or TAKE, confirm that the workbook's audience has a compatible Excel version.

Once those feel natural, combine them. A single dynamic array formula can become a live report that updates as your worksheet changes.