Study smart | Excel superstars
Dynamic arrays
How one formula can replace repetitive work and help you analyse data faster and more accurately
Words Traci Williams, excelace.co.uk
Key Stats
- Dynamic arrays can reduce formula duplication by up to 90% in structured reports
- A single dynamic array formula can replace dozens or even hundreds of copied formulas
In accounting, we’re used to building spreadsheets that rely on formulas copied down rows or across columns. While this works, it can be time-consuming and prone to error, especially when dealing with large datasets.
In modern versions of Microsoft Excel (Microsoft 365), there is a more efficient approach: dynamic arrays.
With dynamic arrays, you write one formula in a single cell, press Enter, and Excel automatically returns multiple results into neighbouring cells. This is known as spilling.
Even better, the results automatically expand or contract when your data changes – no need to copy formulas, no manual updates.
For anyone working with financial data, this is a significant improvement in both efficiency and accuracy.
Dynamic array functions
Here are five of the key dynamic arrays and how they apply to accounting scenarios:
1) UNIQUE()
What it does: Returns distinct values from a dataset
Accounting use case: Extract a list of unique customers, suppliers or GL codes
2) SORT()
What it does: Sorts data dynamically
Accounting use case: Keep transaction listings or ledgers automatically sorted by date or value
3) FILTER()
What it does: Returns only data that meets criteria
Accounting use case: Extract transactions for a specific period, department or account
4) SEQUENCE()
What it does: Generates number or date sequences
Accounting use case: Create accounting periods, invoice numbers or reporting timelines
5) (COMBINATIONS)
What it does: Allows multiple steps in one formula
Accounting use case: Build dynamic reports without pivot tables or helper columns
Example 1: cleaning transaction data (UNIQUE and SORT)
With this list of customer names, there are duplicates and inconsistent formatting:
Before dynamic arrays:
- Copy data to another column
- Remove duplicates manually (or use Remove Duplicates function)
- Manually amend formatting
- Sort the list
After dynamic arrays:
=UNIQUE(A2:A10)
This instantly produces a clean list of unique customers:
In cell C2, you can see the single formula and the list of unique values it returns.
We can also use the SORT formula to turn the result into an alphabetical list. The existing formula would literally be wrapped with SORT: =SORT(UNIQUE(A2:A10))
The real beauty of this formula is that it automatically spills to row five, as that is how many unique values there are. However, if we were to change the data, the UNIQUE formula would automatically adjust to number of rows it spills into, like this:
We could also take this a step further and standardise the text using PROPER().
The existing formula would be wrapped with PROPER: =PROPER(SORT(UNIQUE(A2:A10)))
Example 2: filtering financial data
You have a transaction dataset and need to extract all transactions relating to marketing expenses.
Before dynamic arrays: We could apply a filter to the data, then filter column C to only show ‘Marketing’
After dynamic arrays: We can apply just one formula to achieve this:
=FILTER(A1:E16,C1:C16=“Marketing”)
This returns only the rows that meet the criteria, so there’s no need to apply manual filters, or copy and paste. This also leaves our original data untouched.
To SORT by Customer (alphabetically): =SORT(FILTER(A1:E16,C1:C16=“Marketing”),2,1)
This sorts by the second column (i.e. Customer) in ascending order (1).
Bonus: The department can also be linked to a cell (containing a pick list), e.g. G1:
Now the filter formula will automatically update as the value in cell G1 is edited.
Why this is important and useful in accounting:
- Quickly analyse categories
- Extract audit samples
- Build dynamic management reports
Why this is important and useful in accounting:
- Quickly analyse categories
- Extract audit samples
- Build dynamic management reports
Example 3: creating accounting periods (SEQUENCE)
Need a list of monthly reporting dates?
=SEQUENCE(12,1,DATE(2026,1,1),30)
A list of 12 Dates, in one column, starting from 01/01/2026 and each increasing by 30 days (approximate monthly intervals). No need for dragging or Fill Series.
Want dates from 1–31 January 2026?
=SEQUENCE(31,1,DATE(2026,1,1),1)
31 rows, one column, starting from 01/01/2026, increasing by one (day)
Or dates for Q1 2026, every other week?
=SEQUENCE(7,1,DATE(2026,1,1),14)
Seven rows, one column, starting from 01/01/2026, increasing by 14 (days)
Why this is important and useful in accounting:
- Automate reporting timelines
- Standardise period structures
- Avoid manual date errors (number of days in a month or leap years)
Example 4: dynamic financial reports
You can combine functions to create powerful, automated outputs.
For example, show a sorted list of unique suppliers for a selected cost centre:
=SORT(UNIQUE(FILTER(A2:A16, B2:B16=F1)))
This single formula:
- Filters relevant transactions
- Removes duplicates
- Sorts the results
- Updates automatically
Why this is important and useful in accounting:
This replaces multiple steps typically done manually or via pivot tables.
We can also take this a step further by filtering for invoices over a specific value (linked to cell I1) as well as cost centre:
=SORT(UNIQUE(FILTER(A2:D16, (B2:B16=H1)*(D2:D16>=I1))))
Here, users can select the cost centre in H1 (B2:B16=H1) AND invoice value in I1 (D2:D16>=I1), and the relevant data will automatically appear in the table below.
Why accountants will love dynamic arrays
- Efficiency: Reduce time spent preparing data
- Accuracy: Eliminate errors from copying formulas
- Auditability: Clear, single-point logic instead of scattered formulas
- Scalability: Works just as well on 100 rows as 100,000
Quick tips
- Dynamic arrays are available in Microsoft 365 and Excel Online
- Use Excel Tables where possible to make the formulas easier: =UNIQUE(Table1[Customer])
- If results ‘spill’ into existing data in your sheet, Excel will show a #SPILL! error. This means there is not enough space for Excel to ‘spill’ the results, so it won’t show any data at all. Clear the data in its way and the formula will work as normal.
Conclusion
Dynamic arrays are one of the most impactful improvements in Excel for anyone working with data – particularly in accounting, where accuracy and efficiency are critical.
Start with a simple formula like: =UNIQUE(A:A) in your own data today. Then you can combine functions as confidence grows.
Whether you’re analysing transactions, reviewing spend or preparing reports, dynamic arrays can significantly reduce manual effort.
Once you see the benefit, you’ll quickly realise how many manual processes can be replaced with a single, dynamic solution.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is the main benefit of dynamic arrays?
2
Which function would you use to extract transactions for a specific account?
3
What does the #SPILL! error mean?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).