If you've been using Excel for a while, you'll be familiar with the idea that a formula goes in a cell and produces a result in that cell. One formula, one result. That's been the rule for decades.
Dynamic Array Functions break that rule — in the best possible way.
Introduced in Excel 365, Dynamic Array Functions (DAFs) can return multiple results from a single formula, automatically filling as many rows and columns as needed. They're one of the most significant additions to Excel in years, and they work particularly well alongside FastClose data, especially when that data is returned as an Excel Table.
This is a complex subject, so this tutorial is intended simply as an introduction.
Here is a video, or read below — don't forget the exercises at the bottom.
Before looking at specific functions, it's worth understanding the concept that underpins all of them: spilling.
When a Dynamic Array Function produces more than one result, Excel automatically places those results into the cells below (or beside) the formula cell. This is called a spill. You write the formula once, in one cell, and Excel works out how much space it needs and fills it accordingly.
The range that Excel fills is called the spill range. You'll notice a blue border appears around it when the formula cell is selected — this is Excel showing you the full extent of the output.

A couple of important things to know about spill ranges:
They are dynamic. If the underlying data changes and the output grows or shrinks, the spill range adjusts automatically.
They must be clear. If another value or formula already occupies a cell in the spill range, Excel can't fill it and will show a #SPILL! error. Simply clear the blocking cell and the formula resolves immediately.
To reference the full spill range from another formula, use the spill operator — a hash symbol after the formula cell address. For example, if your DAF is in cell D2, typing D2# in another formula refers to the entire spill range, however large it currently is.
In the previous session we looked at how returning FastClose data as an Excel Table makes it easier to reference data by name rather than by cell position. DAFs take that a step further.
Because a spill range is dynamic, it naturally adapts when a FastClose table is refreshed and the number of rows changes. A FILTER formula pulling specific rows from your table, for example, will automatically return more or fewer rows the next time the report is run, no manual adjustment needed.
The combination of a named Excel Table (stable, named column references) and Dynamic Array Functions (flexible, auto-sizing output) is a powerful pattern for building summary views and analytical layers on top of FastClose data.
Using the management report from the previous session, let's add some sales data and use it, combined with DAFs, to generate some sales metrics.
Create a new worksheet and rename it Gross Margin by Customer.
Click Open and select 02 - Gross Margin by Customer from Solutions → 03 Sales → 01 AR / SOP Sales Analysis.

Update the Company linked filter group you created in 07.02 Building An Executive Summary with Excel Tables to include this report.

In the demo data, we are not going to apply a date filter, so that we have a good data set.
Go to worksheet Options and ensure Display results as a table is checked and Show subtotals and grand totals is unchecked.
Click Run the Report.
Now that we have this data set, we can use Dynamic Array Functions to generate some insights on the summary worksheet.
FILTER is probably the most immediately useful DAF for FastClose users. It extracts rows from a dataset that meet one or more conditions — think of it as a formula-driven version of applying a table filter, but one that outputs the results to a separate location.
Syntax:
=FILTER(array, include, [if_empty])
array — the data you want to filter (a table column, a range, or an entire table)
include — the condition that must be true for a row to be included
if_empty — optional; what to show if no rows match (e.g. "No results")
Example — filter by Sales Value
Working with the Gross Margin by Customer table, add the heading Customers with sales > 10,000 on the summary worksheet, then enter this formula underneath:
=FILTER(GrossMarginbyCustomerTable,GrossMarginbyCustomerTable[Sales Value]>10000,"no results")
The results spill downwards automatically, returning every matching row. Change the threshold and the output updates instantly.

Because this references a table, you can also pull in the column headers by combining FILTER with VSTACK.
=VSTACK(GrossMarginbyCustomerTable[#Headers], FILTER(GrossMarginbyCustomerTable, GrossMarginbyCustomerTable[Sales Value]>10000, "no results"))

For our executive summary, however, we only want to see the customers, so specify the Customer column rather than the entire table:
=FILTER(GrossMarginbyCustomerTable[Customer],GrossMarginbyCustomerTable[Sales Value]>10000,"no results")
This returns a list of customers where sales exceed 10,000.

Combining conditions:
Use * between conditions for AND logic — both must be true
Use + between conditions for OR logic — either can be true
Example — combine two conditions
Edit the heading to Customers with sales > 10,000 and Margin less than 80%, then edit the formula to add the additional condition:
=FILTER(GrossMarginbyCustomerTable[Customer],(GrossMarginbyCustomerTable[Sales Value]>10000) * (GrossMarginbyCustomerTable[Margin Percent]<0.8),"no results")

SORT returns a sorted copy of a range or array. SORTBY does the same thing but lets you sort based on a separate column — useful when you want to sort by a value that isn't included in the output.
SORT syntax:
=SORT(array, [sort_index], [sort_order], [by_col])
sort_index — which column to sort by (1 = first column, 2 = second, etc.)
sort_order — 1 for ascending (default), -1 for descending
by_col — TRUE to sort by column instead of row; almost always left as default (FALSE)
Example — sort the Gross Margin table
Sort the table by Sales Value, highest first, using column index 2 (the Sales Value column):
=SORT(GrossMarginbyCustomerTable,2,-1)

SORT spills the entire table, which isn't always useful. Add CHOOSECOLS to choose which columns to return, or use SORTBY instead (see below).
=CHOOSECOLS(SORT(GrossMarginbyCustomerTable,2,-1),1)

SORTBY syntax:
=SORTBY(array, by_array1, [sort_order1], ...)
SORTBY is more flexible because the column you're sorting by doesn't need to be in the output. You can also sort by multiple columns by adding further by_array / sort_order pairs.
Example — sort by Sales Value descending, then by Customer Name ascending:
=SORTBY(GrossMarginbyCustomerTable[Customer], GrossMarginbyCustomerTable[Sales Value], -1, GrossMarginbyCustomerTable[Customer], 1)

These examples don't necessarily produce a meaningful sales metric on their own — try them out in a separate worksheet if you like. They come into their own when combined with other functions, as described below.
The real power of DAFs emerges when they are combined. Because each function returns an array, the output of one can be passed directly into another.
Example — building on the earlier filter (customers with sales greater than 10,000 and margin less than 80%), sort the results by Sales Value, descending, and bring Sales Value into the output:
=SORT(FILTER(GrossMarginbyCustomerTable[Customer]:GrossMarginbyCustomerTable[Sales Value],(GrossMarginbyCustomerTable[Sales Value]>10000)*(GrossMarginbyCustomerTable[Margin Percent]<0.8),"no results"),2,-1)
The FILTER runs first and returns the matching rows, then SORT takes that output and orders it by the second column, descending.
Update your executive summary with this formula, add two column headers, Customer Name and Sales Value, and format the Sales Value column as currency.

This approach — building up logic by nesting DAFs — is clean, maintainable, and far less error-prone than the multi-step workarounds that were needed before DAFs existed.
Dynamic Array Functions require Excel 365 or Excel 2021. They are not available in Excel 2019 or earlier. If a workbook containing DAFs is opened in an older version of Excel, the formulas will display as legacy array formulas or return errors.
If you are sharing workbooks with colleagues who may be on older versions, it is worth checking their Excel version before building a DAF-heavy workbook.
Dynamic array formulas in Excel — Microsoft Support
Now try the downloadable exercises below:
07.03 Dynamic Array Functions - Exercises.pdf
Your feedback helps us do better, to fill in a short survey on this tutorial, click here.