Excel Dynamic Array Functions: Unlocking a New Dimension

Microsoft Excel has entered a new era with dynamic array functions – a set of powerful functions in Excel for Microsoft 365 (and Excel 2021+) that fundamentally change how formulas work. Instead of returning a single value, these functions can automatically return an array of values that spill into adjacent cells. This means one formula can populate multiple cells simultaneously, eliminating the need for Ctrl+Shift+Enter legacy array formulas and making complex tasks much easier.

This guide will explore the most useful dynamic array functions: SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, RANDARRAY, and LET. We’ll show you how they work through practical examples and neat tricks.

Dynamic array functions in Excel - the foundation

What are Dynamic Array Functions?

Dynamic array functions automatically spill their results into adjacent cells, called a spill range. If the spill range changes, Excel adjusts dynamically, saving you the trouble of dragging formulas or using CTRL+SHIFT+ENTER. If you reference a spill range, add the hash (#) after the top-left cell reference (e.g., A1#).

(Note: Dynamic array capabilities are only available in Excel for Microsoft 365 or Excel 2021 and later. If you open these formulas in an older version, you’ll see an @ symbol added, or they won’t function.)

• SORT Function

The SORT function arranges data easily.

Syntax:

=SORT(array, [sort_index], [sort_order], [by_col])

Example: Sorting a list of products by price in descending order.

=SORT(A2:B10, 2, -1)

The products now appear at the highest and lowest prices automatically.

Quick tip: SORT works vertically by default, but use by_col=TRUE it to sort horizontally!

Before moving on, note: If your source data is in an Excel Table, SORT will not automatically expand the output when new data is added (because you’d have to adjust the array reference). Consider using structured references or the SORTBY function for more flexibility.

• SORTBY Function

SORTBY sorts data based on different arrays.

Syntax:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)

Example: Sort employees by department first, then by salary.

=SORTBY(A2:C10, B2:B10, 1, C2:C10, -1)

Now, your employees are organised neatly, grouped by department, then by salary within each department.

Trick: Randomize your list easily!

=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

Instantly shuffle data like a lottery draw.

Why use SORTBY instead of SORT? The SORTBY function shines when you need multi-level sorts or custom sorts that aren’t just ascending/descending on one of the output columns. Also, if your source data range might get new columns inserted, SORTBY (which references ranges by name) is more robust than SORT (which uses index numbers that could become incorrect if columns shift)

• FILTER Function

FILTER returns only the rows meeting specific criteria.

Syntax:

=FILTER(array, include, [if_empty])

Example: Filtering orders above $500.

=FILTER(A2:B100, B2:B100>500, "No orders")

This quickly lists high-value orders, dynamically updating as data changes.

Multi-criteria tip:

Combine criteria using multiplication (AND logic):

=FILTER(A2:C100, (B2:B100="East")*(C2:C100>500))

• UNIQUE Function

UNIQUE extracts distinct values effortlessly.

Syntax:

=UNIQUE(array, [by_col], [exactly_once])

Example: Get a list of unique customer names.

=UNIQUE(A2:A50)

Quickly generates a clean customer list without duplicates.

Tip: Get entries appearing exactly once:

=UNIQUE(A2:A50, FALSE, TRUE)

Perfect for identifying unique survey responses!

Trick – sort unique results: One thing to note: UNIQUE doesn’t sort the results; it preserves the original order of first occurrence. If you need a sorted list of uniques, you can wrap the UNIQUE with a SORT. For example: =SORT(UNIQUE(A2:A100)). This will give a sorted, unique list from A2:A100. After filtering some criteria, you can combine FILTER and UNIQUE, e.g., unique values.

• SEQUENCE Function

SEQUENCE creates sequences of numbers.

Syntax:

=SEQUENCE(rows, [columns], [start], [step])

Example: Generating monthly dates:

=SEQUENCE(12, 1, DATE(2024,1,1), 30)

Instantly creates a list of monthly dates starting January 2024.

Creative tip: Generate multiplication tables:

=SEQUENCE(10)*TRANSPOSE(SEQUENCE(10))

Trick – Creating a quick table of calculations: Because SEQUENCE can output a matrix, you can do creative things. For example, to generate a multiplication table 1 to 10: put =SEQUENCE(10,1,1,1) in A2:A11 for the vertical headers and =SEQUENCE(1,10,1,1) in B1:K1 for horizontal headers (or generate both via formulas). Then in B2, enter =$A2 B$1 and copy it through B2:K11 – voila. However, an even cooler trick: you can do it in one go with dynamic arrays: =SEQUENCE(10,1,1,1) * SEQUENCE(1,10,1,1) entered in a single cell will spill a 10x10 multiplication table! You could do =SEQUENCE(10)*TRANSPOSE(SEQUENCE(10)) to achieve the same with one formula. This shows how dynamic arrays and functions like SEQUENCE enable matrix calculations without helper cells.

• RANDARRAY Function

RANDARRAY generates random numbers.

Syntax:

=RANDARRAY([rows], [columns], [min], [max], [whole_number])

Example: Random integers for a mock survey.

=RANDARRAY(5,3,1,10,TRUE)

Produces random survey responses instantly.

Bonus: Generate random letters for passwords or IDs.

=CHAR(RANDARRAY(6,1,65,90,TRUE))

Produces random uppercase letters quickly.

Extra trick – random letters or strings: While not a built-in feature, you can generate random letters using RANDARRAY with the CHAR function. For example, =CHAR(RANDARRAY(5,1,65,90,TRUE)) will give 5 random uppercase letters (because CHAR(65) = "A" and 90 = "Z"). This works by generating random integers from 65 to 90. You could generate longer text strings by generating an array and concatenating, or using the new TEXTJOIN/LAMBDA approaches.

• LET Function

LET defines variables inside formulas, simplifying calculations.

Syntax:

=LET(name1,value1,name2,value2,..., calculation)

Example: Using LET for clarity:

=LET(avg, AVERAGE(A1:A10), SUM((A1:A10 - avg)^2))

Calculate complex formulas clearly and efficiently.

Pro-tip: Combine LET with dynamic arrays for ultimate flexibility:

=LET(dates, SEQUENCE(endDate-startDate+1,,startDate), FILTER(dates, WEEKDAY(dates,2)<6))

Generates weekdays dynamically between two dates!

A note on scope: Variables defined in LET only exist inside that formula. They do not appear in the worksheet’s Name Manager or anywhere else. They are like temporary variables in a programming function. Once the formula is done, those names go away. This means you can use common names like x or temp without worrying about conflicts (avoid names that could be cell references, e.g., don’t use "AA1" as a name, and remember names can’t start with a number or look like cell addresses).

When to use LET: LET might be overkill if you have a simple one-liner formula. But LET can be beneficial when you repeat an expression, or if the formula is complex enough, you’d like to document parts of it. It can speed up recalculation (by removing redundant calculations) and makes maintenance easier: you can change a sub-expression in one spot instead of multiple. It’s like creating a mini “macro” within your formula.

For example, an elaborate conditional formula like:

=IF(((A1+B1)/2 > 50)  ||  ((A1+B1)/2 < 20),  (A1+B1)/2,  "OK")

The sub-expression (A1+B1)/2 is repeated three times. You could LET it:

=LET(avg, (A1+B1)/2,  IF( OR(avg>50, avg<20),  avg, "OK") )

This is shorter and clearer. Excel calculates the average once and reuses it.

In sum, LET doesn’t change what Excel can do but changes how you build the formula, making it cleaner and often more efficient. Think of it as a tool for the formula author more than for the formula result. It’s especially powerful when combined with dynamic arrays and even the new LAMBDA function (for creating custom functions), but that’s another topic.

Final Thoughts

Dynamic array functions in Excel simplify complex tasks, making spreadsheets responsive and easy to maintain. Mastering these functions—SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, RANDARRAY, and LET—will significantly enhance your data-handling skills.

Give these powerful tools a try and watch your Excel efficiency soar!

A few final tips for using dynamic arrays effectively:

• Make sure the output spill range has space to expand. If not, you’ll get a #SPILL error. Clear any blocking cells, and the result will appear.

• Remember that recalculating these formulas can cause significant updates. They’re generally efficient, but if you nest many large dynamic functions, keep an eye on performance.

• Embrace the new referencing with the # operator. It can easily feed the results of one dynamic array into other formulas. For example, if =SORT(...) is in E2 spilling downward, you can refer to all its output as E2# in another formula – no need to guess the exact range.

• These functions do not work inside Excel tables (structured table references). If you need dynamic arrays in tables, one workaround is to spill to cells outside the table, or use them to generate data that you then bring into a table. Microsoft chose this to avoid ambiguity in structured references and table autofill behaviour.

• Stay updated: Microsoft periodically introduces new functions (like XLOOKUP, LAMBDA, etc.) that work nicely with dynamic arrays. The Excel formula language is evolving quickly in the subscription version.