MS Excel - Dynamic Array Functions in Excel (FILTER, UNIQUE, SORTBY)

Dynamic array functions represent a major shift in how formulas work in modern Excel. In older versions, formulas typically returned a single value per cell, and if you needed multiple results, you had to copy the formula manually or use complex array formulas with special key combinations. Dynamic arrays remove that limitation by allowing a single formula to return multiple values that automatically “spill” into adjacent cells.

This behavior is known as a spill range. When you enter a dynamic array formula in one cell, Excel automatically fills the results into neighboring cells based on the size of the output. If the source data changes, the results update instantly without requiring any manual adjustments.

1. FILTER Function

The FILTER function is used to extract a subset of data from a larger dataset based on specific conditions.

Syntax:

=FILTER(array, include, [if_empty])

  • array: The data range you want to filter

  • include: The condition that determines which rows to return

  • if_empty: Optional value if no results match

Example:
If you have a dataset with employee names and departments, and you want to display only employees from the Sales department:

=FILTER(A2:C100, B2:B100="Sales")

This formula will return all rows where the department column equals "Sales". The result automatically expands into multiple rows.

Key advantages:

  • No need for manual filtering or copying data

  • Automatically updates when data changes

  • Can handle multiple conditions using logical operators

2. UNIQUE Function

The UNIQUE function extracts distinct values from a dataset, removing duplicates.

Syntax:

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

  • array: Range from which to extract unique values

  • by_col: Optional, whether to compare columns instead of rows

  • exactly_once: Optional, returns only values that appear once

Example:
To get a list of unique product names:

=UNIQUE(A2:A100)

This will return each product only once, even if it appears multiple times in the dataset.

Use cases:

  • Creating dropdown lists

  • Data cleaning and deduplication

  • Preparing summary reports

3. SORTBY Function

SORTBY allows you to sort data based on one or more columns, independently of the displayed data.

Syntax:

=SORTBY(array, by_array1, [sort_order1], ...)

  • array: Data to sort

  • by_array1: Range to sort by

  • sort_order1: 1 for ascending, -1 for descending

Example:
To sort employee data based on salary in descending order:

=SORTBY(A2:C100, C2:C100, -1)

This sorts the dataset based on the salary column without altering the original data.

Benefits:

  • Dynamic sorting without changing source data

  • Supports multiple sorting conditions

  • Works seamlessly with other dynamic functions

Combined Usage

One of the most powerful aspects of dynamic array functions is that they can be combined into a single formula.

Example:

=SORTBY(UNIQUE(FILTER(A2:A100, B2:B100="Sales")), 1, 1)

This formula:

  1. Filters data for Sales department

  2. Extracts unique values

  3. Sorts them in ascending order

All of this happens in one formula without helper columns.

Spill Behavior and Errors

Dynamic arrays automatically expand, but there are a few important considerations:

  • If cells in the spill range are not empty, Excel shows a #SPILL! error

  • The formula exists only in the first cell; other cells are dependent outputs

  • You can reference the entire spill range using the # symbol
    Example:

    =A1#

Practical Impact

Dynamic array functions significantly reduce the need for:

  • Complex nested formulas

  • Helper columns

  • Manual copying and dragging

They make Excel more efficient, readable, and closer to a data analysis tool rather than just a spreadsheet application.

In modern Excel workflows, mastering FILTER, UNIQUE, and SORTBY is essential for handling structured and dynamic datasets efficiently.