Most Australian finance teams still build reports the way they did ten years ago, with a SUMIF here, a VLOOKUP there and a pivot table refreshed by hand every month. The work gets done, but it never quite ends. Every new month means re-extending ranges, re-checking totals and hoping nobody inserted a row in the wrong place.

Dynamic array functions change that. A single formula can filter a transaction list, rank the top customers, build a date series or summarise a month of sales, and the result updates itself when new data lands. For business reporting, they are the difference between a report you maintain and a report that maintains itself.

This guide walks through the functions that matter for management reporting, with examples you can adapt to your own data. It assumes you are on Microsoft 365 or Excel 2021 or later, where dynamic arrays are the default calculation engine.

What Dynamic Arrays Actually Do

Before Excel 365, a formula lived in one cell. Array formulas existed, but they required Ctrl+Shift+Enter and most people avoided them. Dynamic arrays remove that barrier. When a formula can return multiple values, Excel spills them into the cells below or beside the formula automatically.

That one change rewrites how reporting formulas are built. Instead of writing one formula per row and copying it down, you write a single formula in the top cell and let it fill the range. Instead of hard-coding a list of months, you generate it. Instead of maintaining a separate summary block, you point one formula at the source data.

The spill reference # is part of the same system. If A2# refers to a spilled array starting at A2, you can use that reference inside other formulas, and those formulas will resize whenever the source array changes.

FILTER: The Report That Builds Itself

FILTER returns every row in a range that meets a condition. It is the closest thing Excel has to a query that stays attached to its data.

A sales report that shows only the current month, drawn from a transactions table:

=FILTER(tblSales, tblSales[Month] = "July")

The result spills down as many rows as July has, and when July data is replaced by August, the report updates. Add a second condition by multiplying the tests together:

=FILTER(tblSales, (tblSales[Month] = "July") * (tblSales[Region] = "NSW"))

FILTER is the workhorse for exception reports such as overdue invoices, variances beyond a threshold and stock below reorder point. Instead of asking someone to re-sort a spreadsheet every week, you give them a tab that always shows the exceptions.

A common Australian reporting task is the monthly debtor listing for the board pack. The old way is to export from Xero or MYOB, delete the paid rows by hand and reformat. The formula version keeps the export as a staging table and points a FILTER at it:

=FILTER(tblDebtors, tblDebtors[Days Overdue] >= 30)

Only invoices 30 days or more past due appear, and the moment a payment clears in the export, the row drops out of the listing. Nobody re-sorts, nobody deletes rows, and the pack always matches the source of truth at the time it was opened.

SORT and UNIQUE: Rank and De-Duplicate on the Fly

SORT orders a range by one or more columns, and UNIQUE returns the distinct values in a range. Used together they replace the manual sort and remove-duplicates routine.

A list of customers ranked by revenue:

=SORT(tblSales, 3, -1)

Sorts the sales table by its third column in descending order. UNIQUE gives you the distinct list first when you need one row per customer:

=SORT(UNIQUE(tblSales[Customer]), 1, 1)

Returns the customer list, de-duplicated and alphabetised. The practical gain is that a dashboard source tab can be one formula instead of a macro or a manual routine.

UNIQUE also solves the dropdown problem. A data validation list that needs to show only the regions that actually appear in the data can point at a spilled UNIQUE range:

=UNIQUE(tblSales[Region])

When a new region appears in the source, the dropdown grows. When one disappears, the dropdown shrinks. Compare that with the classic approach of typing a static list and watching it go stale.

TAKE and DROP: Top-N Lists That Stay Top-N

TAKE returns the first or last N rows of a range; DROP removes them. Combined with SORT, they produce the classic top-N report without any manual trimming.

Top five customers by sales:

=TAKE(SORT(tblSales, 3, -1), 5)

Bottom five by the same measure:

=TAKE(SORT(tblSales, 3, -1), -5)

The result always shows five rows, and the content changes as the source data changes. The same pattern gives you top products, top debtors, top cost centres, anything where leadership wants the ranking and nothing else.

CHOOSECOLS earns a mention in the same family. It pulls specific columns out of a range, which matters when the raw export has twenty columns and the report needs four:

=CHOOSECOLS(tblSales, 1, 3, 7)

That single formula replaces the old routine of copying a sheet, deleting columns and hoping the formulas still referenced the right cells. Used inside a LET, it lets you name each stage of a report calculation and keep the logic readable.

SEQUENCE: Date Series and Period Grids

SEQUENCE generates a list of numbers, which makes it the cleanest way to build date series. A vertical list of the next twelve month-ends:

=EDATE(DATE(2026,7,1), SEQUENCE(12, 1, 0, 1))

Returns twelve month-end dates starting July 2026. A row of calendar months for a report header:

=TEXT(EDATE(DATE(2026,7,1), SEQUENCE(1, 12, 0, 1)), "mmm yy")

The header row builds itself, and the report no longer carries a hidden assumptions row of typed month names that someone forgets to extend.

SEQUENCE also handles the numbering tasks that used to break when rows were inserted or deleted. A stable transaction numbering column:

=SEQUENCE(COUNTA(tblSales[Date]))

Financial year reporting is where this earns its keep in Australia. Rather than a hard-coded list that starts in July and needs editing every June, the period grid can derive from the financial year start:

=EDATE(DATE(FYStartYear, 7, 1), SEQUENCE(12, 1, 0, 1))

Change one assumption cell and every month header across the pack updates. That is the dynamic array philosophy in miniature: the structure of the report responds to its assumptions instead of being typed out by hand.

GROUPBY: Summaries Without a Pivot Table

GROUPBY, available in Microsoft 365, aggregates a range by one or more fields and returns the summary as a spilled range. It is the closest dynamic-array equivalent to the core pivot table operation.

Sales by region and product category:

=GROUPBY(tblSales[Region], tblSales[Amount], SUM, 3, 0)

The function groups by region, sums the amount, shows row headers and hides subtotals. Add a second grouping field by passing both columns:

=GROUPBY(HSTACK(tblSales[Region], tblSales[Category]), tblSales[Amount], SUM)

The summary is live. When the source table grows, the grouped result grows with it. For a fixed management pack that must show the same shape every month, GROUPBY is often simpler than maintaining a pivot table and its refresh step.

TEXTSPLIT and TEXTBEFORE: Cleaning Messy Exports

System exports arrive in awkward shapes. One column holds "Customer Name / Project Code", another holds dates with the time attached, and a third has values with currency symbols baked in. TEXTSPLIT splits text around a delimiter into separate cells:

=TEXTSPLIT(A2, "/")

TEXTBEFORE and TEXTAFTER pull the part of a string you actually want:

=TEXTBEFORE(A2, "/")

=TEXTAFTER(A2, "/")

Used inside a LET or as helper columns in a staging tab, these functions turn a raw bank feed or CRM export into a clean report source in minutes, with no Text to Columns step to redo every month.

Building a Self-Updating Management Report

The value shows up when the pieces go together. A monthly sales pack needs four things, and all four can be formulas. The period filter, the ranking, the summary and the dates each come from one function or one small formula chain.

1. A staging tab holds the raw export, pasted in as a table.
2. The report tab filters it by the current period with FILTER.
3. The summary block uses GROUPBY on the filtered range.
4. The top-N block uses TAKE with SORT.
5. The header row uses SEQUENCE for the months.

When next month's export replaces the staging data, every block updates. The person who owns the report stops editing it and starts checking it, which is where the real quality gain sits. A report that regenerates itself is also easier to audit, because there is no manual step where a number can be typed over a formula.

Where Legacy Formulas Still Earn Their Keep

Dynamic arrays are not a universal replacement. Some reporting jobs still suit older tools.

VLOOKUP and XLOOKUP still belong in a lookup column where you want one value back, though XLOOKUP is the better choice and the lookup guide covers the difference. Pivot tables and Power Query still win for very large data sets and for ad hoc analysis where the question changes every week. And a model that must run on Excel 2016 or 2019 for client distribution cannot lean on spill behaviour at all.

The practical rule is to match the tool to the task. Dynamic arrays shine where the report shape is stable, the data changes regularly and the layout must stay predictable. Power Query shines where the transformation is heavy and multi-step. Pivot tables shine where the user wants to explore. Most finance functions end up using all three, and the reports improve where each is used for its strength.

The same logic applies within the dynamic array family. GROUPBY suits a summary with a fixed shape. A pivot table suits a summary the user will re-arrange. FILTER suits an exception list. Power Query suits a transformation pipeline that feeds several reports at once. Choosing between them is a question of who uses the output and how often the shape changes, not of which tool is newer.

Common Pitfalls and How to Avoid Them

Dynamic arrays behave differently from legacy formulas in a few ways that catch people out.

The first is the #SPILL! error. A spilled result needs its target cells to be empty. If a stray value, a merged cell or a leftover entry sits in the spill area, the formula returns #SPILL! instead of its results. The fix is usually to clear the blocking cells, or to check for a table that has grown over the spill range. When a report suddenly shows #SPILL! after months of working, a user has almost certainly typed something into the spill area.

The second is the implicit intersection trap. Legacy formulas that reference a whole column return a single value through implicit intersection. A dynamic array formula that references a whole column returns the whole column, which is rarely what a SUM expects. If a formula returns unexpected results after an upgrade to Microsoft 365, check whether a range reference is now spilling where the old engine would have collapsed it.

The third is version risk in distributed files. The newer functions are not available in every Excel release, and a workbook that uses them will not behave as intended on a version that lacks them. Before you hand a pack to a client or a board member on an older licence, confirm that their Excel includes the functions you have used. The safe pattern is to keep the dynamic array logic in the working file and export a static pack for distribution, or to confirm the audience is on Microsoft 365.

The fourth is performance. One FILTER over 50,000 rows is fine. Fifty FILTERs over the same 50,000 rows, each recalculating on every change, will slow a workbook down noticeably. Where a report needs many views of the same data, filter once into a staging range and point the downstream formulas at that spill, rather than repeating the same scan in every block.

Conclusion

Excel dynamic array functions move reporting from a monthly maintenance task to a set of live formulas that respond to the underlying data. FILTER keeps the period view current, SORT and TAKE produce rankings that never go stale, SEQUENCE builds the date grid, GROUPBY replaces the refresh-the-pivot routine and TEXTSPLIT cleans the exports that feed the whole chain.

The shift is not about learning eight new functions. It is about changing the default. When a report is needed, build it as a formula that reads the source data, and the maintenance disappears. Teams that make that shift spend their month-end time checking the story in the numbers instead of rebuilding the spreadsheet that shows them.

Related reading

• 10 Time-Saving Excel Formulas for Managers
• Excel VLOOKUP vs XLOOKUP vs INDEX-MATCH
• Excel Data Analysis for Business Decisions