MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Array Formulas & Dynamic Arrays (FILTER, SORT, UNIQUE)0%
Array Formulas & Dynamic Arrays (FILTER, SORT, UNIQUE)
Beginner12 min read•Updated: Jul 11, 2026
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 Zoom2. The Core Modern Dynamic Functions
A. UNIQUE: Instant Deduplication
Extracts all distinct values from a column or table:
=UNIQUE(A2:A500)
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:
=SORT(array, [sort_index], [sort_order])
Example: Sort employees by Salary (Col 3) Descending (-1)
=SORT(A2:C100, 3, -1)
C. FILTER: The Query Engine
Extracts records that satisfy one or more criteria:
Syntax:
=FILTER(array, include, [if_empty])
Example: Extract all employees in the "Finance" department
=FILTER(A2:D100, B2:B100="Finance", "No Records")
Combining AND / OR Criteria in FILTER:
- *AND Logic (Multiply ''):** Finance team with Salary > 50,000:
=FILTER(A2:D100, (B2:B100="Finance") * (C2:C100>50000), "None Found")
- OR Logic (Add '+'): Employees in Finance OR Operations:
=FILTER(A2:D100, (B2:B100="Finance") + (B2:B100="Operations"), "None Found")
3. The Ultimate Synergy: Combining UNIQUE, FILTER & SORT
In advanced dashboards, you can nest these dynamic functions into a single formula:
=SORT(UNIQUE(FILTER(A2:A100, B2:B100="West")))
Filters the data for the West region, strips out duplicate product names, and sorts the resulting list alphabetically in one calculation!
Interactive Quiz
Multiple Choice Questions
Question 1
What causes a '#SPILL!' error in modern Microsoft Excel?
Question 2
How do you refer to the entire spilled range originating from cell F2 in subsequent formulas?
Question 3
Which formula extracts a clean, deduplicated list of unique sales representatives from column B?
Question 4
In the FILTER function, how do you specify that two conditions must BOTH be true (AND logic)?
Question 5
What will '=SORT(A2:B20, 2, -1)' do?
Next Lesson
Grouping Dates and Numbers in Pivot Tables
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
| Previous Lesson | Next Lesson |
|---|---|
| XLOOKUP: The Modern Flexible Lookup Function | Grouping Dates and Numbers in Pivot Tables |
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.