Mastering Excel Dynamic Arrays: Functions & Formulas

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.

1. UNIQUE Function
2. SORT Function
3. SORTBY Function
4. SEQUENCE Function
5. FILTER Function
6. RANDARRAY Function
7. XLOOKUP Function
8. SUMIFS Function
9. TEXTJOIN Function
10. IFERROR Function
11. 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:

1. UNIQUE Function: Extracts distinct values from a dataset.
2. SORT Function: Sorts data in ascending or descending order.
3. SORTBY Function: Sorts data based on criteria in another range.
4. SEQUENCE Function: Generates a sequence of numbers with specified rows, columns, start, and step values.
5. FILTER Function: Filters data based on specified criteria.
6. 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

1. Generating a List of Unique Values:

• Extract a list of distinct departments:

=UNIQUE(A2:A100)

2. Sorting and Filtering Data:

• Filter and sort sales data based on region:

=SORT(FILTER(A2:D100, C2:C100="West"), 3, -1)

3. 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.

Keep Learning

» Master Excel Array Formula: A Comprehensive Guide
» What’s New in Microsoft Office 2021?
» Microsoft Office 2021 for Mac Review
» Microsoft Office 2021 Review: Pros, Cons, Feature Highlight
» Microsoft Office 2021: What to Expect