07.03 Dynamic Array Functions

07.03 Dynamic Array Functions

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.

Spilling

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.

image

A couple of important things to know about spill ranges:

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.

How DAFs Work with Excel Tables

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.

Step 1 — Create the Worksheet

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.

Step 2 — Open the Report

Click Open and select 02 - Gross Margin by Customer from Solutions → 03 Sales → 01 AR / SOP Sales Analysis.

Open Gross Margin Report

Step 3 — Update the Linked Filters

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

Gross Margin Report Linked Filters

In the demo data, we are not going to apply a date filter, so that we have a good data set.

Step 4 — Configure Options and Run the Report

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

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])

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.

image

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"))
SpillandVSTACK

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.

Gross Margin Report Summary Sales gtr 10000

Combining conditions:

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")
Gross Margin Report Summary Sales gtr 10000 and marging less than 80

SORT and SORTBY

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])

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)
Gross Margin Report Sort Example

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)
Gross Margin Report Summary customers by highest sales value

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)
Gross Margin Report Summary customers by highest sales value and name

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.

Combining Functions

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.

Gross Margin Report Summary Sales gtr 10000 and marging less than 80 updated with sort

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.

Excel Version Compatibility

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.

Further Reading

Dynamic array formulas in Excel — Microsoft Support

Exercises

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.

Powered By