Dynamic arrays are a powerful addition to Excel, revolutionizing how we work with data. Let’s dive into their functions and how they can transform your spreadsheets.
What are Dynamic Arrays?
Dynamic arrays are resizable arrays that automatically adjust their size based on the data they hold. This means you can enter a formula in a single cell, and Excel will spill the results into multiple cells as needed.
Dynamic arrays are a powerful new feature in Excel that allow you to work with multiple values in a single formula, eliminating the need for complex array formulas and simplifying data analysis.
Key Features and Benefits:
- Spilling: When you enter a dynamic array formula in a cell, the results will automatically spill into adjacent cells, filling the range as needed.
- Simplified Formula Entry: No more need for Ctrl+Shift+Enter to enter array formulas. Simply enter the formula and press Enter.
- New Dynamic Array Functions: Excel introduces new functions like FILTER, UNIQUE, SORT, and RANDARRAY that leverage dynamic arrays to efficiently manipulate and analyze data.
- Enhanced Data Analysis: Dynamic arrays make it easier to filter, sort, and extract data, leading to more efficient and accurate analysis.
| Function | Version | Purpose | Arguments |
|---|---|---|---|
| ARRAYTOTEXT | Excel 365 | Converts array or range to a text string | array format |
| BYCOL | Excel 365 | Apply function to column | array lambda |
| BYROW | Excel 365 | Apply function to row | array lambda |
| CHOOSECOLS | Excel 365 | Return specific columns from an array | array col_num1 col_num2 … |
| CHOOSEROWS | Excel 365 | Return specific rows from an array | array row_num1 row_num2 … |
| DROP | Excel 365 pdf file | Remove portion of an array | array rows col |
| EXPAND | Excel 365 | Expand array by adding rows or columns | array rows columns pad_with |
| FILTER | Excel 2021 | Filter range with given criteria | array include if_empty |
| GROUPBY | Beta | Summarize data by grouping rows | row_fields values function field_headers total_depth sort_order filter_array |
| HSTACK | Excel 365 | Combine ranges or arrays horizontally | array1 array2 … |
| IMAGE | Excel 365 | Retrieve image to Excel from the Internet | source alt_text sizing height width |
| ISOMITTED | Excel 365 | Check for optional arguments in LAMBDAs | argument |
| LAMBDA | Excel 365 | Create custom function | parameter … calculation |
| LET | Excel 2021 | Assign variables inside formula | name1 value1 name2/value2 … result |
| MAKEARRAY | Excel 365 | Create array with calculated values | rows columns function |
| MAP | Excel 365 | Map array to custom function | array1 array2 … lambda |
| PERCENTOF | Beta | Return a subset of data as a percentage of all data | data_subset all_data |
| PIVOTBY | Beta | Summarize data by grouping rows and columns | row_fields col_fields values function field_headers row_total_depth row_sort_order col_total_depth col_sort_order filter_array |
| RANDARRAY | Excel 2021 | Get array of random numbers | rows columns min max integer |
| REDUCE | Excel 365 | Reduce an array | initial_value array lambda |
| REGEXEXTRACT | Beta | Extract text with regex pattern | text pattern return_mode case_sensitivity |
| REGEXREPLACE | Beta | Replace text with a regex pattern | text pattern replacement occurrence case_sensitivity |
| REGEXTEST | Beta | Test a value for a specific pattern of text | text pattern case_sensitivity |
| SCAN | Excel 365 | Scan array and return intermediate results | initial_value array lambda |
| SEQUENCE | Excel 2021 | Get array of list of sequential numbers | rows columns start step |
| SORT | Excel 2021 | Sorts range or array | array sort_index sort_order by_col |
| SORTBY | Excel 2021 | Sorts range or array by column | array by_array sort_order array/order … |
| STOCKHISTORY | Excel 365 | Retrieve stock price information | stock start_date end_date interval headers properties … |
| TAKE | Excel 365 | Get a subset of an array | array rows col |
| TEXTAFTER | Excel 365 | Extract text after a delimiter | text delimiter instance_num match_mode match_end if_not_found |
| TEXTBEFORE | Excel 365 | Extract text before a delimiter | text delimiter instance_num match_mode match_end if_not_found |
| TEXTSPLIT | Excel 365 | Split a text string with a delimiter | text col_delimiter row_delimiter ignore_empty match_mode pad_with |
| TOCOL | Excel 365 | Transform array to single column | array ignore scan_by_column |
| TOROW | Excel 365 | Transform array to single row | array ignore scan_by_column |
| UNIQUE | Excel 2021 | Extract unique values from range | array by_col exactly_once |
| VALUETOTEXT | Excel 365 | Converts a value to a text string | value format |
| VSTACK | Excel 365 | Combine ranges or arrays vertically | array1 array2 … |
| WRAPCOLS | Excel 365 | Wrap array into columns | vector wrap_count pad_with |
| WRAPROWS | Excel 365 | Wrap array into rows | vector wrap_count pad_with |
| XLOOKUP | Excel 2021 | Look up values in range or array | lookup lookup_array return_array not_found match_mode search_mode |
| XMATCH | Excel 2021 | Get the position of an item in a list or table | lookup_value lookup_array match_mode search_mode |