A Practical Guide to Dynamic Array Formulas in Excel

Dynamic array formulas allow you to extract more data from your spreadsheet quickly, and, once you learn how to do it, easily. We're walking you through how to understand UNIQUE, FILTER, SORT, XLOOKUP, and more, without having to write a single macro. 

What Is a Dynamic Array Formula?

A dynamic array formula is a special type of Excel formula that can return multiple values at once, automatically spilling those results into neighboring cells. You write the formula in a single cell, press Enter, and Excel fills in all the rows or columns needed to display every result.

Think of it like this: traditional formulas are like a faucet that delivers one drop at a time. A dynamic array formula is a hose, so with one turn of the handle, the water flows exactly as far as it needs to.

Dynamic array functions were introduced in Microsoft 365 (and Excel 2021). They don't need a Ctrl+Shift+Enter shortcut; they work with a simple Enter key like any other formula.

Key Term: Spill Range

When a dynamic array formula produces multiple results, Excel automatically fills the adjacent cells. This area is called the spill range. If any cell in the spill range is blocked by existing data, Excel will show a #SPILL! error. When this happens, simply clear the blocked cells and the formula works again.

The Core Dynamic Array Functions

Here are the seven functions accountants use most often:

What Problems Do Dynamic Array Formulas Solve?

Before dynamic arrays, accountants faced three recurring frustrations with Excel:

Every Report Had to Be Rebuilt by Hand

When the underlying data changed, like a new vendor was added, a period closed, or a department was restructured, the report broke. You had to manually extend ranges, copy formulas down, delete stale rows, and hope nothing was missed. This was especially difficult for an audit trail.

VLOOKUP Was Fragile and Error-Prone

VLOOKUP only searches left-to-right, crashes if a column is inserted, and returns only the first match. Accountants spent hours debugging #N/A errors and building workaround formulas with INDEX/MATCH combinations.

Unique-Value Lists Required Macros or Pivot Tables

Getting a clean list of unique GL accounts or cost centers required either a PivotTable refresh (which colleagues often forgot to do) or a VBA macro (which many accounting teams are not permitted to use due to IT security policies).

Dynamic array formulas solve all three problems by making your reports self-updating. Add a row of data to your source table and every formula downstream, including the unique lists, the filtered views, and the sorted rankings, refreshes instantly.

Step-by-Step Guide: Building a Dynamic Expense Report

The following walkthrough uses a typical accounts-payable scenario with a transaction log with columns for Date, Account, Category, Vendor, Department, Amount, and Status. The goal is to build a self-updating dashboard with no macros and no pivot tables.

Step 1: Structure Your Source Data as a Table

Dynamic array formulas work best when source data is formatted as an official Excel Table (Insert → Table, or Ctrl+T). Tables automatically expand as you add rows, so your formulas never need their ranges updated.

1. Select your data range including headers
2. Press CTRL+T and confirm that your table has headers
3. Name the table something descriptive. ON the Table Design tab, type Transactions in the Table Name box.
4. Your formula ranges can now reference the table by name, e.g. Transactions[Amount] instead of F2:F500. 

Step 2: Extract Unique Values with UNIQUE()

The most common starting point for any accounting dashboard is a clean list of unique values, including departments, vendors, GL accounts, and cost centers. UNIQUE() does this in one formula.

Let's say you want a list of every department that appears in the transaction log, with no duplicates. 

1. Click on an empty cell in your dashboard sheet (e.g., cell A5)
2. Type the following formula and press Enter: =UNIQUE('Transaction Data' !E2:E21)
3. Excel immediately fills the cells below A5 with each unique department name. 
4. Add a row to your source data with a new department name. The list updates automatically.

You can also use UNIQUE() to find combinations of two columns. For example, to list every unique Department + Category pair: =UNIQUE('Transaction Data'!C2:C21 & " | " & 'Transaction Data'!E2:E21)

Step 3: Filter Transactions with FILTER()

FILTER() is the dynamic replacement for the manual AutoFilter. It returns a live subset of your data based on any conditions, and unlike AutoFilter, it never hides rows that other users might accidentally clear. 

Scenario: Pull all transactions with a Status of Pending into a separate review area. 

1. Click an empty cell (e.g., A17 on your dashboard sheet). 
2. Enter this formula: =FILTER('Transaction Data'!A2:G21, 'Transaction Data'!G2:G21="Pending", "No pending items") 
3. Press Enter. Excel returns every row where the Status column equals Pending, spilling all seven columns automatically. 
4. The third argument — "No pending items" — is displayed if no rows match the filter. Always include this to prevent the #CALC! error. 

You can combine conditions using multiplication (for AND logic) or addition (for OR logic): =FILTER(A2:G21, (G2:G21="Pending") * (F2:F21>5000)) 

This returns only transactions that are both Pending and over $5,000 — ideal for a payables exception report.

Step 4: Rank Results with SORT() and SORTBY()

Once you have a filtered or full dataset, SORT() lets you present it in a meaningful order without touching the source data. 

Scenario: Show the top expenses in your filtered Pending list, with the largest amounts first. 

1. Wrap your FILTER() formula inside SORT(): =SORT(FILTER('Transaction Data'!A2:G21, 'Transaction Data'!G2:G21="Pending"), 6, -1) 
2. The second argument (6) is the column to sort by — column 6 is Amount. 
3. The third argument (-1) means descending order. Use 1 for ascending. 
4. As new pending transactions are added to the source data, this sorted list updates automatically.

Use SORT when your sort column is within the array being sorted. Use SORTBY when you want to sort one range by a different, external range. For example, sorting a vendor list by their total spend calculated in another column, you would use SORTBY.

Step 5: Look Up Values with XLOOKUP ()

XLOOKUP() is the modern replacement for VLOOKUP() and INDEX/MATCH(). It is simpler to write, works in any direction (left or right), and handles missing values gracefully without nested IFERROR wrapping. 

Scenario: Given a vendor name typed in cell A27, return the most recent transaction amount for that vendor. 

1. Click on cell B27. 
2. Enter: =XLOOKUP(A27, 'Transaction Data'!D:D, 'Transaction Data'!F:F, "Not found") 
3. The arguments are: lookup value, where to search, what to return, and what to show if not found. 
4. Unlike VLOOKUP, you can search in any column and return any column, even one to the left of the search column. 

XLOOKUP also supports approximate matching, wildcard searches, and returning the last match rather than the first without additional nesting.

Step 6: Combine Functions for a Full Dashboard

The real power of dynamic array formulas emerges when they are combined. Here is a production-ready formula that produces a sorted, filtered summary of approved transactions grouped by department: =SORT(FILTER(Transactions, Transactions[Status]="Approved"), 6, -1) 

Breaking it down: 

• FILTER() selects only Approved rows from the full table. 
• SORT() orders those results by the 6th column (Amount) in descending order. 
• The entire output spills automatically without need for helper columns or manual copying.

If you see #SPILL!, it means another value is already sitting in the spill range below or to the right of your formula. Clear those cells (even blank-looking cells can contain spaces) and the formula will resolve immediately.

Step 7: Reference a Spill Range in Other Formulas

Once a dynamic array formula has spilled its results, you can reference the entire spill range in downstream formulas using the # operator. For example, if your UNIQUE() formula is in cell A5 and it spills department names into A5:A10, you can count those departments with: =COUNTA(A5#) Or sum the spend for all departments dynamically: =SUMIF('Transaction Data'!E:E, A5#, 'Transaction Data'!F:F) The # symbol always refers to the complete spill range, even if new rows are added and the range grows. This is how you build formulas that chain together into a fully self-updating report.

Quick Reference: Errors and Fixes for Dynamic Array Formulas

#SPILL!

• Cause: Cells in the spill range contain data or spaces. 
• Fix: Clear all cells in the expected output range

#CALC!

• Cause: FILTER() found no matching rows and no if_empty argument was supplied 
• Fix: Add a third argument: =FILTER(…, …, "No results")

#N/A

• Cause: XLOOKUP could not find the lookup value 
• Fix: Add a fourth argument: =XLOOKUP(…,…,…,"Not found")

#VALUE!

• Cause: A text value appeared where a number was expected 
• Fix: Check that your criteria columns are consistently formatted (text vs. number)

#NAME?

• Cause: Excel does not recognize the function name 
• Fix: Verify you are using Microsoft 365 or Excel 2021; dynamic arrays are not available in Excel 2019 or earlier

Take the Next Step with CPE Courses from Becker

If you'd like to build your Excel skills, Becker offers a wide variety of CPE courses that help you work more efficiently and extract more insights from your data. Check out these courses to get started. 

• Excel: Transform and Analyze Data
• Excel: Cool Tricks
• Transforming Accounting and Auditing Practices with Copilot

Unlock Unlimited CPE with a Prime Subscription

Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry! 

With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits. 

Choose Prime CPE