How to Use ARRAYFORMULA in Excel

Key Insights

Excel 365 and Excel 2021 use dynamic array formulas that automatically spill results to adjacent cells, eliminating the need for Ctrl+Shift+Enter array formulas and providing Google Sheets ARRAYFORMULA-like functionality
Functions like FILTER, SORT, UNIQUE, and SEQUENCE transform Excel into a powerful data manipulation tool that updates automatically when source data changes
Dynamic arrays work best for medium-sized datasets (under 100,000 rows) and should be combined strategically to avoid performance degradation and #SPILL! errors

Understanding Excel’s Dynamic Array Engine

Excel 365 and Excel 2021 introduced a fundamental shift in how formulas work. The new dynamic array engine allows formulas to return multiple values that automatically “spill” into adjacent cells. This is Excel’s answer to Google Sheets’ ARRAYFORMULA function, but with more sophisticated capabilities and better integration into Excel’s formula ecosystem.

Legacy array formulas required you to press Ctrl+Shift+Enter and manually select the output range. Dynamic arrays eliminate this friction entirely. Write one formula, and Excel handles the rest.

Here’s the difference:

// Legacy array formula (pre-365)
{=A1:A10*2}  // Required Ctrl+Shift+Enter, manual range selection

// Dynamic array formula (365/2021)
=A1:A10*2    // Automatically spills to 10 cells

The dynamic array version is cleaner, easier to maintain, and automatically adjusts when your source data changes size.

How Spilling Works

When you enter a formula that returns multiple values, Excel spills the results into adjacent cells automatically. The original cell containing the formula controls the entire spilled range. You cannot edit individual cells in the spilled range—you must modify the source formula.

The spill range operator (#) references the entire spilled array:

// In cell C1
=A1:A10*2

// In cell D1, reference the entire spilled range from C1
=SUM(C1#)

The most common issue you’ll encounter is the #SPILL! error, which occurs when Excel cannot spill results because cells in the target range contain data. Clear the obstructing cells or move your formula to resolve this.

Essential Dynamic Array Functions

Excel introduced six core dynamic array functions that transform how you manipulate data. Master these, and you’ll handle 90% of data processing tasks without VBA or Power Query.

FILTER: Conditional Data Extraction

FILTER extracts rows or columns that meet specified criteria. This replaces complex combinations of IF, INDEX, and MATCH formulas.

// Extract all sales over $1000
=FILTER(A2:C100, C2:C100>1000)

// Multiple conditions: Sales over $1000 in Region "West"
=FILTER(A2:C100, (C2:C100>1000)*(B2:B100="West"))

// With fallback for no results
=FILTER(A2:C100, C2:C100>1000, "No results found")

The syntax is straightforward: FILTER(array, include, [if_empty]). The include parameter uses Boolean logic—multiply conditions for AND, add for OR.

SORT and SORTBY: Dynamic Ordering

SORT arranges data by one or more columns without modifying the source data.

// Sort by column 3 descending
=SORT(A2:C100, 3, -1)

// Sort by multiple columns: Region ascending, then Sales descending
=SORT(A2:C100, {2,3}, {1,-1})

SORTBY provides more flexibility when you need to sort by criteria not in the result set:

// Sort product names by their corresponding sales values
=SORTBY(A2:A100, C2:C100, -1)

UNIQUE: Remove Duplicates Instantly

UNIQUE extracts distinct values from a range. Combine it with other functions for powerful data analysis.

// Get unique customer names
=UNIQUE(A2:A100)

// Unique combinations of Region and Product
=UNIQUE(A2:B100)

// Unique values that appear exactly once (true unique)
=UNIQUE(A2:A100, FALSE, TRUE)

SEQUENCE: Generate Number Series

SEQUENCE creates arrays of sequential numbers, dates, or patterns. It’s invaluable for creating dynamic models and test data.

// Generate numbers 1 to 10
=SEQUENCE(10)

// 5 rows, 3 columns starting at 100, incrementing by 5
=SEQUENCE(5, 3, 100, 5)

// Generate dates for the current month
=SEQUENCE(DAY(EOMONTH(TODAY(),0)), 1, DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

Combining Functions for Real Power

Dynamic array functions become transformative when you nest them. Each function’s output becomes another function’s input, creating sophisticated data pipelines in a single formula.

Filtered and Sorted Results

// Top 10 sales over $500, sorted by amount
=SORT(FILTER(A2:C100, C2:C100>500), 3, -1)
=TAKE(previous_formula, 10)

// Or in Excel 365's TAKE function (one formula):
=TAKE(SORT(FILTER(A2:C100, C2:C100>500), 3, -1), 10)

Dynamic Calculated Columns

Add calculations to filtered results without helper columns:

// Filter sales data and add a commission column (10%)
=HSTACK(
    FILTER(A2:C100, C2:C100>1000),
    FILTER(C2:C100, C2:C100>1000)*0.1
)

Unique Value Summary

Create frequency counts or summaries of unique values:

// Count occurrences of each unique product
=HSTACK(
    UNIQUE(A2:A100),
    COUNTIF(A2:A100, UNIQUE(A2:A100))
)

// Sum sales by unique region
=HSTACK(
    UNIQUE(B2:B100),
    SUMIF(B2:B100, UNIQUE(B2:B100), C2:C100)
)

Practical Application: Automated Sales Dashboard

Here’s a complete example that demonstrates the power of combining dynamic array functions. This single-formula dashboard extracts, filters, sorts, and summarizes sales data:

// In cell E2: Top 5 products by sales in the West region
=TAKE(
    SORT(
        FILTER(A2:C100, B2:B100="West"),
        3, -1
    ),
    5
)

// In cell H2: Summary by region with totals
=SORTBY(
    HSTACK(
        UNIQUE(B2:B100),
        SUMIF(B2:B100, UNIQUE(B2:B100), C2:C100)
    ),
    2, -1
)

// In cell K2: Dynamic month-over-month comparison
=LET(
    dates, A2:A100,
    sales, C2:C100,
    current_month, FILTER(sales, MONTH(dates)=MONTH(TODAY())),
    prior_month, FILTER(sales, MONTH(dates)=MONTH(TODAY())-1),
    HSTACK("Current", SUM(current_month), "Prior", SUM(prior_month))
)

The LET function (also introduced with dynamic arrays) assigns names to intermediate calculations, making complex formulas readable and efficient.

Troubleshooting and Best Practices

Resolving #SPILL! Errors

#SPILL! errors occur when Excel cannot output results. Common causes:

Obstructed cells: Clear cells in the spill path or move your formula
Merged cells: Unmerge cells in the target range
Table structures: Spilled arrays cannot overlap with Excel tables

Performance Considerations

Dynamic array formulas recalculate whenever source data changes. For large datasets:

Limit nested functions to 3-4 levels deep
Use FILTER early in the formula chain to reduce data volume
Consider Power Query for datasets exceeding 100,000 rows
Avoid volatile functions (NOW, TODAY, RAND) in array formulas when possible

When Not to Use Dynamic Arrays

Traditional formulas remain appropriate for:

Single-cell calculations
Formulas requiring cell-specific references
Workbooks shared with users on Excel 2019 or earlier
Scenarios where you need to manually override individual results

Compatibility Notes

Dynamic array formulas only work in Excel 365 and Excel 2021. If you share workbooks with users on older versions, they’ll see #NAME? errors. Consider these alternatives:

Use Power Query and save results as values
Create a compatibility version with traditional formulas
Export to CSV for universal compatibility

Moving Forward

Dynamic array formulas represent the most significant improvement to Excel’s formula engine in decades. They eliminate the need for helper columns, reduce formula complexity, and make Excel worksheets self-maintaining. Start by replacing your most complex INDEX-MATCH or SUMIF combinations with FILTER and SORT. You’ll quickly discover scenarios where a single dynamic array formula replaces dozens of traditional formulas.

The key is thinking in terms of arrays rather than individual cells. Once you make that mental shift, you’ll find yourself building more powerful, maintainable spreadsheets with less effort.