Dynamic arrays are revolutionizing data analysis in Excel. From managing automatic resizing ranges to providing simpler formulas, the dynamic array functions introduce a new era in Excel productivity.
Let's delve into how these functions can simplify your work and boost efficiency.
Dynamic Arrays in this Guide- UNIQUE Function
- SORT Function
- SORTBY Function
- SEQUENCE Function
- FILTER Function
- RANDARRAY Function
- XLOOKUP Function
- SUMIFS Function
- TEXTJOIN Function
- IFERROR Function
- Dynamic Array Formulas
Dynamic Arrays: The Core Concept
The concept of dynamic arrays simplifies data analysis by automatically adjusting the size of the output range. If the data expands or shrinks, the results are updated accordingly.
The results are referred to as a "spill range," a term synonymous with dynamic arrays.
Excel Dynamic Array Functions Overview
The following six new dynamic array functions expand the analytical possibilities in Excel:
- UNIQUE Function: Extracts distinct values from a dataset.
- SORT Function: Sorts data in ascending or descending order.
- SORTBY Function: Sorts data based on criteria in another range.
- SEQUENCE Function: Generates a sequence of numbers with specified rows, columns, start, and step values.
- FILTER Function: Filters data based on specified criteria.
- RANDARRAY Function: Generates an array of random numbers.
Let's explore each function's syntax, use cases, and unique characteristics.
UNIQUE Function
The UNIQUE function can effortlessly extract distinct values from a dataset.
Syntax:
=UNIQUE(array, [by_col], [exactly_once])
- array: The input data.
- [by_col]: Logical value (TRUE or FALSE) specifying whether to evaluate by rows or columns.
- [exactly_once]: Logical value indicating whether to return only values appearing exactly once.
Example:
SORT Function
Sorting your data is simplified with the SORT function.
Syntax:
=SORT(array, [sort_index], [sort_order], [by_col])
- array: The data to sort.
- [sort_index]: The column or row to sort by.
- [sort_order]: Sorting order (1 for ascending, -1 for descending).
- [by_col]: Logical value specifying whether to sort by rows (FALSE) or columns (TRUE).
Example:
In this example:
- A2:D6 is the range of the original data.
- 2 indicates that we are sorting by the second column (Age).
- 1 specifies ascending order.
This should provide a clear example of how the SORT function works.
SORTBY Function
SORTBY allows you to sort based on the values in a different range.
Syntax:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
- array: The data to sort.
- by_array1: The range to sort by.
- [sort_order1]: Sorting order.
- Additional sort conditions follow the same pattern.
Example:
SEQUENCE Function
The SEQUENCE function generates numerical sequences with specified criteria.
Syntax:
=SEQUENCE(rows, [columns], [start], [step])
- rows: Number of rows.
- [columns]: Number of columns.
- [start]: Starting value.
- [step]: Increment between each value.
Example:
=SEQUENCE(5, 3, 10, 2)
FILTER Function
The FILTER function enables conditional filtering of data.
Syntax:
=FILTER(array, include, [if_empty])
- array: The input data.
- include: Criteria to include in the output.
- [if_empty]: Value to return if no data meets the criteria.
Example:
=FILTER(A2:D10, C2:C10="North")
RANDARRAY Function
Generate random numbers with the RANDARRAY function.
Syntax:
=RANDARRAY([rows], [columns], [min], [max], [whole_number])
- [rows]: Number of rows.
- [columns]: Number of columns.
- [min]: Minimum value.
- [max]: Maximum value.
- [whole_number]: Logical value indicating whether to return whole numbers (TRUE) or decimals (FALSE).
Example:
=RANDARRAY(5, 3, 1, 100, TRUE)
XLOOKUP Function
Search for a value in a range or array and return a corresponding value.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value to search for.
- lookup_array: The range or array to search within.
- return_array: The range or array to return the value from.
- [if_not_found]: Value to return if no match is found.
- [match_mode]: The match type (0 for exact match, 1 for exact or next larger, -1 for exact or next smaller).
- [search_mode]: The search mode (1 for first-to-last, -1 for last-to-first).
Example:
SUMIFS Function
Add the cells specified by a given set of conditions or criteria.
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range: The range of cells to sum.
- criteria_range1: The range to apply the first condition to.
- criteria1: The condition to apply.
- [criteria_range2, criteria2]: Additional ranges and conditions (optional).
Example:
TEXTJOIN Function
Join multiple ranges and/or strings with a delimiter.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
- delimiter: The text to use between joined items.
- ignore_empty: TRUE to ignore empty cells, FALSE to include them.
- text1, [text2], ...: The text items to join.
Example:
IFERROR Function
Return a value if a formula results in an error.
Syntax:
=IFERROR(value, value_if_error)
- value: The value, expression, or formula to check for errors.
- value_if_error: The value to return if an error is found.
Example:
Dynamic Array Formulas
Dynamic arrays also bring significant changes to existing formulas. Here's a snapshot of what's new:
- Automatic Spill Ranges: The formula result automatically adjusts its range.
- Implicit Intersection Operator (@): Ensures backward compatibility by extracting a single value from a spilling formula.
- Error Handling: The #SPILL! error indicates issues with output ranges.
Practical Use Cases of Dynamic Arrays
- Generating a List of Unique Values:
- Extract a list of distinct departments:
=UNIQUE(A2:A100)
- Sorting and Filtering Data:
- Filter and sort sales data based on region:
=SORT(FILTER(A2:D100, C2:C100="West"), 3, -1)
- Creating a Multi-Column Sequence:
- Generate a sequence for inventory management:
Final Thoughts
Excel dynamic arrays streamline complex data analysis tasks, saving time and reducing the need for auxiliary formulas. Understanding and utilizing these functions will significantly enhance your Excel proficiency, enabling you to handle data more effectively.