Excel Spreadsheets: Dynamic Arrays

Traci Williams explains how one formula can do the work of dozens – and make you look like an Excel ACE

We are used to writing formulas, pressing enter, and then copying them down rows or across columns. But in the latest versions of Excel (Microsoft 365), there’s a new way to manage this: Dynamic Arrays. 

With these, you write one single formula in one cell, press Enter… and Excel instantly spills the results into multiple cells. No dragging. No copying. Just instant results. 

AND it will automatically expand or reduce the range as and when the data changes, too.  

Dynamic Arrays are one of the biggest Excel upgrades in 30 years, but many users don’t even know they exist. 

Dynamic Array functions 

Here are the 5 most powerful ones to start with – and how they can transform everyday tasks: 

1. UNIQUE()  

What it does: Removes duplicates from a list 

EA use case: Create a clean attendee list from a messy sign-up sheet 

2. SORT() 

What it does: Automatically sorts data as it changes 

EA use case: Keep names sorted in alphabetical order 

3. FILTER() 

What it does: Returns only rows that meet specified criteria 

EA use case: Pull out all bookings for a specific event / location 

4. SEQUENCE() 

What it does: Creates number / date sequences instantly 

EA use case: Generate seating numbers, day lists for conferences, dates for follow-up 

5. (Combinations) 

What it does: Combine functions for magic 

EA use case: SORT(UNIQUE(FILTER(…))) to build dynamic reports 

Real-world example 1: Cleaning an attendee list 

With this list of names, those highlighted in Pink appear more than once, and there are issues with the capitalisation: 

Before: 

You copy names into a new column, remove duplicates manually, tidy up capitalisation, then sort them. 

After:  

=UNIQUE(A2:A21)  

This instantly creates a deduplicated list that updates automatically when new names are added: 

In cell C2, you can see the single formula and the list of unique values it returns. 

The real beauty of this formula is that it automatically spills to row 15, as that is how many unique values there are. However, if we were to change the data, the UNIQUE formula would automatically adjust the number of rows it spills into, like this: 

We could also take this formula a step further and make the list SORTED into alphabetical order. The existing formula would literally be wrapped with SORT:  

=SORT(UNIQUE(A2:A21)) 

SUBHEAD: Real-world example 2: Instant reports with FILTER 

The data below represents all company bookings, and you are asked to give just the bookings for London. 

Before: We could apply a filter to the data and filter column C to only show London. 

After: We can literally apply just ONE formula to achieve this:  

=FILTER(A1:E21,C1:C21=”London”) 

This formula instantly pulls only the matching rows into a neat table. No filters, no copying. This also leaves our original data untouched too. 

We could SORT the list too. 

=SORT(FILTER(A1:E21,C1:C21=”London”),4,1) 

This sorts by the 4th column (i.e., Date) in ascending order (1). 

Real-World Example 3: Auto-Numbering and Scheduling 

Need a quick numbered list for event seating? 

=SEQUENCE(100) 

This creates a list of 1 to 100 instantly… No need for dragging or Fill Series. 

Want dates from 1–31 January 2026? 

=SEQUENCE(31,1,DATE(2026,1,1),1) 

Or dates for Q1 2026, every other week? 

=SEQUENCE(7,1,DATE(2026,1,1),14) 

7 represents 7 rows of data (i.e., 7 time periods in a quarter). 

14 represents the 14-day intervals. 

DATE(2026,1,1) represents the start date (amend this if you want a specific day). 

Real-World Example 3: Combining Functions = Real Magic 

Dynamic Arrays have a combined power when you stack the functions together. 

For example, to get a sorted list of all unique London attendees: 

=SORT(UNIQUE(FILTER(A1:C21, B1:B21=”London”))) 

This single formula: 

• Filters for London  

• Removes duplicates  

• Sorts alphabetically  

• Updates automatically as data changes  

No helper columns. No manual steps. No filters. One cell. 

We can also take this a step further by filtering for a specific event as well as location: 

Here, users can select the location AND event from the pick lists in the green cells, and the relevant data will automatically appear in the table below. 

The formula has been updated to: 

=SORT(UNIQUE(FILTER(A1:C21, (B1:B21=F1)*(C1:C21=F2)))) 

B1:B21 = F1 This will filter items in column B that equal the selection in cell F1 (Location). 

C1:C21 = F2 This will filter items in column c that equal the selection in cell F2 (Event). 

Why Executive Assistants will love this 

• Huge time-saver – less manual copying and filtering of data and formulas. 

• Reduces errors – no forgetting to ‘fill down’ a formula. 

• Impressive – looks advanced, but it is simple to use once you know how. 

• Reusable – build once, reuse for multiple reports / events. 

You’ll look like you’ve built a complex report when it’s literally just one formula. 

Quick tips 

• Dynamic Arrays work in Microsoft 365 and Excel Online (not older versions). 

• Where possible, make sure your source data is in a table; it makes formulas easier. 

For example, =UNIQUE(Names[Name]) instead of =UNIQUE(A2:A21).  

• 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. 

• Start simple (e.g., UNIQUE()), then layer functions as you grow confident. 

Conclusion: Start with one ‘wow’ 

You don’t have to learn everything at once. Pick one task you do every week and see if a Dynamic Array can automate it. Once you see that first ‘wow’, you’ll never go back to copy-pasting again. 

Try This Today: 
If column A has a list of names, in a blank cell, type: 

=UNIQUE(A:A) 

Congratulations on your first Dynamic Array!!