All of Excel's Dynamic Array Functions
The Power Of Dynamic Array Functions
In the ever-evolving landscape of data analysis and spreadsheet management, Microsoft Excel has taken a monumental leap forward by introducing Dynamic Array Functions. This groundbreaking feature rolled out to Microsoft 365 users starting in 2020. It has revolutionized how professionals and enthusiasts alike interact with data within Excel.
This article will serve as your hub for all things dynamic arrays by providing a directory of these amazing functions.
What Are Dynamic Array Functions?
Dynamic Array Functions are a set of powerful tools in Excel that automatically return multiple values into a range of cells based on a single formula. This feature marks a departure from traditional functions that could only return a single value to a single cell.
With dynamic arrays, when you enter a formula that returns multiple values, Excel intelligently spills these values into adjacent cells, dynamically resizing the output range to accommodate all the results. This behavior is known as "spilling," and it allows for more dynamic and responsive data models.
Dynamic Array Functions have simplified complex tasks and introduced a level of efficiency and flexibility previously unimaginable in spreadsheet software.
List of Excel’s Dynamic Array Functions
The following table is a comprehensive listing of Excel functions that fall under the class of Dynamic Array (meaning they can output multiple results per formula). I've included the year each function was publicly released to Microsoft 365.
| Array Function | Release Year | Parameters | Description |
|---|---|---|---|
| FILTER | 2020 | array, include, [if_empty] | Filters a range of data based on the criteria you specify |
| LET | 2020 | name1, name_value1, calculation_or_name2, [name_value2], … | Assigns names to calculation results and values |
| RANDARRAY | 2020 | [rows], [columns], [min], [max], [integer] | Returns an array of random numbers |
| SEQUENCE | 2020 | rows, [columns], [start], [step] | Generates a list of sequential numbers |
| SORT | 2020 | array, [sort_index], [sort_order], [by_col] | Sorts the contents of a range or array |
| SORTBY | 2020 | array, by_array1, [sort_order1], ... | Sorts the contents of a range or array based on the values in a corresponding range or array |
| STOCKHISTORY | 2020 | stock, start_date, [end_date], [interval], [headers], [properties1], … | Returns historical data about a financial instrument (stocks, FX rates, Crypto) |
| UNIQUE | 2020 | array, [by_col], [occurs_once] | Returns a list of unique values from a range or array |
| XLOOKUP | 2020 | lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] | Searches a range or an array, and returns an item corresponding to the first match it finds |
| XMATCH | 2020 | lookup_value, lookup_array, [match_mode], [search_mode] | Searches for a specified item in an array or range of cells, and then returns the item's relative position |
| ARRAYTOTEXT | 2022 | array, [format] | Converts an array to a text where each item is separated by a delimiter |
| BYCOL | 2022 | array, [lambda] | Applies a LAMBDA function to each column in an array and returns an array of the results |
| BYROW | 2022 | array, [lambda] | Applies a LAMBDA function to each row in an array and returns an array of the results |
| CHOOSECOLS | 2022 | array, col_num1, [col_num2], ... | Returns an array with columns chosen from the source array |
| CHOOSEROWS | 2022 | array, row_num1, [row_num2], ... | Returns an array with rows chosen from the source array |
| DROP | 2022 | array, rows, [columns] | Removes a specified number of rows and/or columns from an array |
| EXPAND | 2022 | array, rows, [columns], [pad_width] | Expands an array to a specified size, filling new cells with a specified value or empty if not specified |
| HSTACK | 2022 | array1, [array2], ... | Combines multiple arrays horizontally into one array |
| ISOMITTED | 2022 | value | Returns TRUE if the value is an omitted argument in LAMBDA, otherwise FALSE |
| LAMBDA | 2022 | parameter_or_calculation, … | Defines a custom function |
| MAKEARRAY | 2022 | rows, columns, lambda | Creates an array by applying a LAMBDA function to each row and column index |
| MAP | 2022 | array1, lambda_or_array2, … | Applies a LAMBDA function to each element in one or more arrays and returns an array of the results |
| REDUCE | 2022 | initial_value, array, lambda | Reduces an array to a single value by applying a LAMBDA function to each element |
| SCAN | 2022 | initial_value, array, lambda | Scans an array and returns an array of intermediate calculation results |
| TAKE | 2022 | array, rows, [columns] | Returns a specified number of rows and/or columns from an array |
| TEXTAFTER | 2022 | text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] | Returns the part of the text after a specific delimiter |
| TEXTBEFORE | 2022 | text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] | Returns the part of the text before a specific delimiter |
| TEXTSPLIT | 2022 | text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with] | Splits text into rows and columns according to delimiters |
| TOCOL | 2022 | array, [ignore], [scan_by_column] | Converts an array to a single column |
| TOROW | 2022 | array, [ignore], [scan_by_column] | Converts an array to a single row |
| VALUETOTEXT | 2022 | value, [format] | Converts a value to text |
| VSTACK | 2022 | array1, [array2], ... | Combines multiple arrays vertically into one array |
| WRAPCOLS | 2022 | vector, wrap_count, [pad_width] | Wraps an array into a specified number of columns, creating a new array |
| WRAPROWS | 2022 | vector, wrap_count, [pad_width] | Wraps an array into a specified number of rows, creating a new array |
| GROUPBY | 2024* | row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter array] | Groups the rows of an array based on unique values in specified columns and applies a LAMBDA function |
| PIVOTBY | 2024* | row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter array] | Pivots an array by one column and aggregates another column using a LAMBDA function |
*In Excel Beta
Dynamic Array Function Categories
It may also help to see these functions grouped into categories that correspond with what they do. Below, I have organized these Excel functions into 6 categories.
Lookup Dynamic Array Functions
- XLOOKUP
- XMATCH
Lambda Dynamic Array Functions
- LAMBDA
- BYROW
- BYCOL
- MAP
- REDUCE
- SCAN
- MAKEARRAY
- ISOMITTED
Text-Based Dynamic Array Functions
- TEXTBEFORE
- TEXTAFTER
- TEXTSPLIT
- VALUETOTEXT
Array Sizing/Shaping Dynamic Array Functions
- VSTACK
- HSTACK
- TOROW
- TOCOL
- WRAPROWS
- WRAPCOLS
- TAKE
- DROP
- CHOOSECOLS
- EXPAND
Summarizing Dynamic Array Functions
- FILTER
- SORTBY
- UNIQUE
- SORT
- GROUPBY
- PIVOTBY
Miscellaneous Dynamic Array Functions
- STOCKHISTORY
- SEQUENCE
- RANDARRAY
- LET
I Hope This Microsoft Excel Article Helped!
Hopefully, I was able to provide you with a great resource for understanding all the available Dynamic Array functions you can utilize in your Microsoft Excel formulas. If you discover any Excel functions that are missing from this listing, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![How To Use Excel's XLOOKUP Function [Guide 2024]](https://www.thespreadsheetguru.com/wp-content/uploads/2019/09/Xlookup-Function-Guide.svg)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.