The Power of Dynamic Array Functions
Vinayak Jadhav
?? Certified Microsoft Power BI Data Analyst Associate?? Certified Microsoft Office Specialist? Non IT Professional Driving Digital Transformation ?? Generate your several Income sources
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.
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 FILTER Release Year 2020 Parameters array, include, [if_empty] Description Filters a range of data based on the criteria you specify.
1. Array Function - FILTER
Release Year - 2020
Parameters - array, include, [if_empty]
Description - Filters a range of data based on the criteria you specify
2. Array Function - LET
Release Year - 2020
Parameters - name1, name_value1, calculation_or_name2, [name_value2], …
Description - Assigns names to calculation results and values
3. Array Function - RANDARRAY
Release Year - 2020
Parameters - [rows], [columns], [min], [max], [integer]
Description - Returns an array of random numbers
4. Array Function - SEQUENCE
Release Year - 2020
Parameters - rows, [columns], [start], [step]
Description - Generates a list of sequential numbers
5. Array Function - SORT
Release Year - 2020
Parameters - array, [sort_index], [sort_order], [by_col]
Description - Sorts the contents of a range or array
6. Array Function - SORTBY
Release Year - 2020
Parameters - array, by_array1, [sort_order1], ...
Description - Sorts the contents of a range or array based on the values in a corresponding range or array
7. Array Function - STOCKHISTORY
Release Year - 2020
Parameters - stock, start_date, [end_date], [interval], [headers], [properties1], …
Description - Returns historical data about a financial instrument (stocks, FX rates, Crypto)
8. Array Function - UNIQUE
Release Year - 2020
Parameters - array, [by_col], [occurs_once]
Description - Returns a list of unique values from a range or array
9. Array Function - XLOOKUP
Release Year - 2020
Parameters - lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]
Description - Searches a range or an array, and returns an item corresponding to the first match it finds
10. Array Function - XMATCH
Release Year - 2020
Parameters - lookup_value, lookup_array, [match_mode], [search_mode]
Description - Searches for a specified item in an array or range of cells, and then returns the item's relative position
11. Array Function - ARRAYTOTEXT
Release Year - 2022
Parameters - array, [format]
Description - Converts an array to a text where each item is separated by a delimiter
12. Array Function - BYCOL
Release Year - 2022
Parameters - array, [lambda]
Description - Applies a LAMBDA function to each column in an array and returns an array of the results
13. Array Function - BYROW
Release Year - 2022
Parameters - array, [lambda]
Description - Applies a LAMBDA function to each row in an array and returns an array of the results
14. Array Function - CHOOSECOLS
Release Year - 2022
Parameters - array, col_num1, [col_num2], ...
Description - Returns an array with columns chosen from the source array
15. Array Function - CHOOSEROWS
Release Year - 2022
Parameters - array, row_num1, [row_num2], ...
Description - Returns an array with rows chosen from the source array
16. Array Function - DROP
Release Year - 2022
Parameters - array, rows, [columns]
Description - Removes a specified number of rows and/or columns from an array
17. Array Function - EXPAND
Release Year - 2022
Parameters - array, rows, [columns], [pad_width]
Description - Expands an array to a specified size, filling new cells with a specified value or empty if not specified
18. Array Function - HSTACK
领英推荐
Release Year - 2022
Parameters - array1, [array2], ...
Description - Combines multiple arrays horizontally into one array
19. Array Function - ISOMITTED
Release Year - 2022
Parameters - value
Description - Returns TRUE if the value is an omitted argument in LAMBDA, otherwise FALSE
20. Array Function - LAMBDA
Release Year - 2022
Parameters - parameter_or_calculation, …
Description - Defines a custom function
21. Array Function - MAKEARRAY
Release Year - 2022
Parameters - rows, columns, lambda
Description - Creates an array by applying a LAMBDA function to each row and column index
22. Array Function - MAP
Release Year - 2022
Parameters - array1, lambda_or_array2, …
Description - Applies a LAMBDA function to each element in one or more arrays and returns an array of the results
23. Array Function - REDUCE
Release Year - 2022
Parameters - initial_value, array, lambda
Description - Reduces an array to a single value by applying a LAMBDA function to each element
24. Array Function - SCAN
Release Year - 2022
Parameters - initial_value, array, lambda
Description - Scans an array and returns an array of intermediate calculation results
25. Array Function - TAKE
Release Year - 2022
Parameters - array, rows, [columns]
Description - Returns a specified number of rows and/or columns from an array
26. Array Function - TEXTAFTER
Release Year - 2022
Parameters - text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]
Description - Returns the part of the text after a specific delimiter
27. Array Function - TEXTBEFORE
Release Year - 2022
Parameters - text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]
Description - Returns the part of the text before a specific delimiter
28. Array Function - TEXTSPLIT
Release Year - 2022
Parameters - text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]
Description - Splits text into rows and columns according to delimiters
29. Array Function - TOCOL
Release Year - 2022
Parameters - array, [ignore], [scan_by_column]
Description - Converts an array to a single column
30. Array Function - TOROW
Release Year - 2022
Parameters - array, [ignore], [scan_by_column]
Description - Converts an array to a single row
31. Array Function - VALUETOTEXT
Release Year - 2022
Parameters - value, [format]
Description - Converts a value to text
32. Array Function - VSTACK
Release Year - 2022
Parameters - array1, [array2], ...
Description - Combines multiple arrays vertically into one array
33. Array Function - WRAPCOLS
Release Year - 2022
Parameters - vector, wrap_count, [pad_width]
Description - Wraps an array into a specified number of columns, creating a new array
34. Array Function - WRAPROWS
Release Year - 2022
Parameters - vector, wrap_count, [pad_width]
Description - Wraps an array into a specified number of rows, creating a new array
35. Array Function - GROUPBY
Release Year - 2024*
Parameters - row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter array]
Description - Groups the rows of an array based on unique values in specified columns and applies a LAMBDA function
36. Array Function - PIVOTBY
Release Year - 2024*
Parameters - row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter array]
Description - Pivots an array by one column and aggregates another column using a LAMBDA function
--
9 个月1st of
Owner, Director | Cyber Security Graduate, Microsoft Certified Systems Engineer, Expert in Apple Computing
9 个月I appreciate your expertise in leveraging dynamic array functions, which has tremendously improved our data management efficiency and provided valuable insights. Thank you for your contribution!
Revamp your B2B Website for 50% Less!
9 个月Impressive insights, Vinayak. Your expertise in data analytics is truly commendable, and this newsletter is a testament to your valuable knowledge in the field. Thank you for sharing your wisdom with us.
Congratulations on the 13th edition! ?? Data analytics is such a fascinating field. What new insights or trends are you covering in this issue? Vinayak Jadhav
Charting Your Financial Future | Speaker on Wealth Beyond Finance | AMFI Registered Mutual Fund Distributor
9 个月Vinayak Jadhav, excel users like myself appreciate you keeping us up-to-date on the latest features. The table summarizing the different functions and their release years is a super helpful reference.