Dynamic Array Functions (FILTER, SORT, UNIQUE)

Dynamic Array Functions were introduced in Microsoft Excel 365 and Excel 2021. These functions automatically return multiple results into neighboring cells without copying formulas.

When a Dynamic Array formula returns multiple values, Excel automatically fills the results into adjacent cells. This behavior is called Spilling.

What is Spill Range?

The range where Dynamic Array results automatically appear is called the Spill Range.

Sample Data Used in Examples

FILTER Function

The FILTER function extracts records that match specified conditions.

Syntax

FILTER(array, include, [if_empty])

Example 1: Show Only Excel Students

=FILTER(A2:D6,C2:C6="Excel","No Records Found")

Result: Rahul, Amit, and Karan records are displayed.

Example 2: Show Students with Marks Above 85

=FILTER(A2:D6,D2:D6>85,"No Data")

Returns only students whose marks are greater than 85.

SORT Function

The SORT function sorts data automatically in ascending or descending order.

Syntax

SORT(array,[sort_index],[sort_order],[by_col])

Example 1: Sort by Marks (Highest First)

=SORT(A2:D6,4,-1)

Sorts the table using Marks column in descending order.

Example 2: Sort Student Names A to Z

=SORT(A2:D6,2,1)

Sorts the table alphabetically using Student Name.

UNIQUE Function

The UNIQUE function returns distinct values and removes duplicates automatically.

Syntax

UNIQUE(array,[by_col],[exactly_once])

Example 1: Unique Courses

=UNIQUE(C2:C6)

Result:

• Excel
• Python
• Power BI

Example 2: Unique Student Names

=UNIQUE(B2:B100)

Returns each student name only once.

Combining Dynamic Array Functions

Dynamic Array Functions can be combined to create powerful reports.

Example: Unique Courses Sorted Alphabetically

=SORT(UNIQUE(C2:C100))

First removes duplicates and then sorts the result.

Example: Filter and Sort Together

=SORT(FILTER(A2:D100,D2:D100>=80))

Displays students scoring 80 or above and sorts the results automatically.

Comparison of Dynamic Array Functions