Excel Dynamic Array Formulas - A Game Changer
If you've ever wished Excel could just figure out how many cells a formula needs without you dragging it down manually—good news! Microsoft heard you. Dynamic arrays are one of the most significant changes to Excel formulas in years, allowing a single formula to spill results into multiple cells automatically.
Let’s break down what dynamic arrays are and look at some key formulas that take full advantage of them.
What Are Dynamic Arrays?
In traditional Excel formulas, if you needed multiple results (like a list of unique values), you had to use Ctrl + Shift + Enter or drag formulas down manually. With dynamic arrays, Excel automatically fills the necessary cells without extra steps.
Key Concept: The “Spill” Effect
- If a formula returns multiple results, Excel spills them into adjacent cells automatically.
- If something blocks the spill range, Excel returns a #SPILL! error.
Key Dynamic Array Formulas
1. UNIQUE – Extracting Unique Values
Syntax:
The by_col argument in the UNIQUE function determines whether Excel should extract unique values by row (default) or by column.
-
If by_col is FALSE or omitted → Excel looks for unique values vertically (by rows).
-
If by_col is TRUE → Excel checks for unique values horizontally (by columns).
You can also use the [exactly_once] argument (set to TRUE) to return only values that appear once in the list, removing all duplicates entirely.
2. SORT – Sorting Data Automatically
Syntax:
=SORT(array, [sort_index], [sort_order], [by_col])
At its basic functionality, this sorts a list in ascending order.
The optional [sort_index] parameter comes in when there are multiple columns in our range, and it allows us to specify which columns we are basing our sort on:
The [sort_order] optional parameter allows us to specify whether we want the list sorted ascending or descending, and the last optional parameter lets us sort by rows (default) or by columns.
3. SORTBY – Sorting Based on Another Column
Syntax:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)
The SORTBY function in Excel allows you to sort a range or array based on the values in another column, without altering the original data. Unlike the SORT function, which sorts based on the selected range itself, SORTBY dynamically orders data based on one or more independent criteria.
Note that the formula only returns the sorted list from the range it was given as the first parameter, in this case A1:A10. I brought in the corresponding values using a VLOOKUP.
4. FILTER – Extracting Data Based on Criteria
Syntax:
=FILTER(array, include, [if_empty])
The FILTER() function in Excel allows you to return a subset of data from a range or array based on specified criteria. It takes an array or range as input, along with one or more conditions, and returns only the values that meet those conditions. This is useful for extracting specific data from large datasets without needing to manually sort or copy values.
If the optional parameter is not included, the formula will simply return a #CALC! error if no values meet the condition.
5. SEQUENCE – Generating a List of Numbers
Syntax:
=SEQUENCE(rows, [columns], [start], [step])
This function generates a list of sequential numbers in a column or row, based on the parameters you provide. You can specify the number of rows, columns, starting value, and the step value (increment). It's often used to quickly create number series for analysis, charts, or as part of more complex formulas.
In the example, I asked it to generate 5 numbers, in 1 column, starting with 10 and using increments of 25.
6. RANDARRAY – Creating Random Numbers
Syntax:
=RANDARRAY([rows], [columns], [min], [max], [integer])
Similar to SQUENCE(), the RANDARRAY() function generates an array of random numbers. You can define the number of rows and columns, and set the range of values for the random numbers (e.g., between 0 and 1, or any other specified range). This function is useful for creating random datasets for simulations, statistical analysis, or testing purposes.
Here we have an example where the formula generates 5 numbers between 0 and 100. The final parameter is set to TRUE so the numbers generated are integers - setting it to 0, FALSE or simply omitting it will cause the formula to generate numbers with decimal points.
One thing might be worth mentioning here - RANDARRAY() is a volatile function, which means it recalculates every time a change is made anywhere in the workbook.
7. Any older formula, when it returns an array instead of a single value!
Previously, the formulas would just break if their result was an array or a range, but no more! Now the results will be spilled into the neighboring cells - provided there is room.
The example in the screenshot returns {0,1,2} - and as you can see, it spills the values in the adjacent cells. Neat!
Final thoughts:
Dynamic array formulas make Excel faster, smarter, and easier to use. No more copying formulas down, no more array formula headaches—just clean, efficient calculations.
If you're still using an older version of Excel, now might be a great time to upgrade and take advantage of these powerful tools!
Cat On A Spreadsheet
Full Service Consulting
Reporting
Automation
Cat On A Spreadsheet