Array Formulas & Dynamic Arrays (FILTER, SORT, UNIQUE)

In modern Excel, the calculation engine was completely overhauled to support Dynamic Arrays. Instead of pressing 'Ctrl + Shift + Enter' for legacy CSE array formulas, modern formulas naturally evaluate lists of data and automatically "Spill" across multiple adjacent rows and columns.

1. The Dynamic Array Spill Engine & #SPILL! Error

When a function produces multiple results, it fills adjacent empty cells automatically. The surrounding border is highlighted with a blue bounding box known as the Spill Range.

• The Spill Operator ('#'): To reference the entire dynamic spill generated by cell D2, simply write '=D2#'. If the spilled data expands from 10 rows to 50 rows, '=D2#' automatically expands with it!
• The #SPILL! Error: Occurs when an existing value, comment, merged cell, or table boundary blocks the path where the formula needs to spill. Clear the blocking cells, and the formula spills immediately.

Click to Zoom

2. The Core Modern Dynamic Functions

A. UNIQUE: Instant Deduplication

Extracts all distinct values from a column or table:

Takes a list with 500 rows and spills only the unique customer names or cities!

• Optional 2nd arg: TRUE to compare columns; FALSE to compare rows.
• Optional 3rd arg: TRUE to return items that occur strictly once.

B. SORT & SORTBY: Formula-Driven Ordering

Sorts a range dynamically without using the Data tab:

C. FILTER: The Query Engine

Extracts records that satisfy one or more criteria:

Combining AND / OR Criteria in FILTER:

• *AND Logic (Multiply ''):** Finance team with Salary > 50,000:

• OR Logic (Add '+'): Employees in Finance OR Operations:

3. The Ultimate Synergy: Combining UNIQUE, FILTER & SORT

In advanced dashboards, you can nest these dynamic functions into a single formula:

Filters the data for the West region, strips out duplicate product names, and sorts the resulting list alphabetically in one calculation!

Multiple Choice Questions

1. What causes a '#SPILL!' error in modern Microsoft Excel?

A. A division by zero B. An obstacle (such as an existing text entry, number, or merged cell) is blocking the area where the dynamic array needs to expand C. The formula has invalid syntax D. The computer has run out of RAM Answer: B Explanation: A #SPILL! error occurs when the dynamic array cannot populate adjacent cells because one or more cells in the spill zone contain blocking data.

2. How do you refer to the entire spilled range originating from cell F2 in subsequent formulas?

A. =F2:ALL B. =F2# C. =SPILL(F2) D. =F2 Answer: B Explanation:* The hash symbol (#) appended to a cell coordinate (e.g., F2#) acts as the Spill Range Operator, referencing the complete dynamic array.

3. Which formula extracts a clean, deduplicated list of unique sales representatives from column B?

A. =DISTINCT(B2:B100) B. =UNIQUE(B2:B100) C. =CLEAN(B2:B100) D. =SINGLE(B2:B100) Answer: B Explanation: The UNIQUE function extracts and spills all distinct, non-duplicate values from the supplied range.

4. In the FILTER function, how do you specify that two conditions must BOTH be true (AND logic)?

A. Separate the conditions with the word AND B. Multiply the logical condition arrays together using the '' asterisk symbol C. Add the arrays with the '+' symbol D. Use a double comma Answer: B Explanation: In dynamic array criteria, multiplying boolean arrays with '' enforces logical AND, while adding with '+' enforces logical OR.

5. What will '=SORT(A2:B20, 2, -1)' do?

A. Sort by column 2 in descending order (highest to lowest) B. Sort by column 2 in ascending order C. Delete column 2 D. Invert the row colors Answer: A Explanation: The second argument (2) specifies sorting on the second column, and sort_order -1 specifies descending order.