Master Dynamic Arrays in Excel: FILTER, SORT, and UNIQUE

You've been building complex formulas with multiple helper columns, array formulas that need Ctrl+Shift+Enter, and VBA macros just to filter or sort data. Excel's dynamic array functions make all of that obsolete.

One formula. One cell. Automatic results that expand and contract based on your data. That's the power of dynamic arrays.

What You'll Learn

• What dynamic arrays are and how they work
• FILTER function for conditional data extraction
• SORT and SORTBY for automatic sorting
• UNIQUE for removing duplicates
• SEQUENCE for generating number series
• Combining functions for powerful data manipulation
• Real-world examples and practical applications

What Are Dynamic Arrays?

Dynamic arrays are formulas that return multiple values and automatically "spill" into adjacent cells.

Old way (pre-2020):

=IF(A2:A100="Yes", B2:B100, "")  // Array formula, needs Ctrl+Shift+Enter
                                   // Returns #VALUE! or requires helper column

New way (dynamic arrays):

Benefits:

• ✅ No Ctrl+Shift+Enter needed
• ✅ Results automatically resize
• ✅ Update when source data changes
• ✅ Cleaner, more readable formulas
• ✅ Eliminate helper columns

Availability: Excel 365, Excel 2021, Excel for web

FILTER Function: Conditional Data Extraction

Extract rows that meet specific criteria.

Basic Syntax

1=FILTER(array, include, [if_empty])

• array: Data range to filter
• include: Criteria (must return TRUE/FALSE for each row)
• if_empty: (Optional) Value if no results found

Example 1: Filter by Single Condition

Find all orders over $1000:

1=FILTER(A2:D100, C2:C100>1000)

What it does:

• Looks at entire data range (A2:D100)
• Checks if each value in column C (amounts) > 1000
• Returns all columns for matching rows

Result: Automatically displays all rows where amount > 1000

Example 2: Filter by Multiple Conditions (AND)

Find orders over $1000 AND status is "Completed":

1=FILTER(A2:D100, (C2:C100>1000) * (D2:D100="Completed"))

The * creates AND logic: Both conditions must be TRUE (1 * 1 = 1, anything else = 0)

Example 3: Filter by Multiple Conditions (OR)

Find orders that are either over $1000 OR status is "Priority":

1=FILTER(A2:D100, (C2:C100>1000) + (D2:D100="Priority"))

The + creates OR logic: Either condition can be TRUE (0 + 1 = 1, 1 + 0 = 1, 1 + 1 = 2, which is still TRUE)

Example 4: Handle Empty Results

1=FILTER(A2:D100, C2:C100>1000, "No results found")

Instead of #CALC! error when no matches, displays "No results found"

Example 5: Filter Text Contains

Find all customers with "Smith" in their name:

1=FILTER(A2:D100, ISNUMBER(SEARCH("Smith", B2:B100)))

Breaking it down:

• SEARCH("Smith", B2:B100): Finds "Smith" in each cell, returns position or #VALUE!
• ISNUMBER(...): Returns TRUE if number (found), FALSE if error (not found)
• FILTER(...): Returns rows where TRUE

Example 6: Top N Results

Get top 10 highest-value orders:

1=FILTER(A2:D100, C2:C100>=LARGE(C2:C100, 10))

How it works:

• LARGE(C2:C100, 10): Finds 10th largest value
• C2:C100>=...: TRUE for values >= 10th largest
• Returns top 10 rows

SORT Function: Automatic Sorting

Sort data without manually selecting and sorting.

Basic Syntax

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

• array: Data range to sort
• sort_index: Column to sort by (1, 2, 3...) - optional, defaults to 1
• sort_order: 1 = ascending, -1 = descending - optional, defaults to 1
• by_col: TRUE = sort by columns, FALSE = sort by rows - optional, defaults to FALSE

Example 1: Simple Sort

Sort customer list by name (column A):

1=SORT(A2:D100)

Default: Sorts by first column, ascending order

Example 2: Sort by Specific Column

Sort by amount (column C), highest to lowest:

1=SORT(A2:D100, 3, -1)

• 3: Sort by 3rd column (amount)
• -1: Descending order (highest first)

Example 3: Multi-Level Sort

Sort by region, then by sales (descending):

1=SORT(A2:D100, 2, 1, 3, -1)

Wait, SORT only accepts 4 parameters? Use SORTBY instead for multi-level sorting.

SORTBY Function: Sort by Multiple Columns

More flexible sorting using separate criteria.

Basic Syntax

1=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

Example: Multi-Level Sort

Sort by region (ascending), then amount (descending):

1=SORTBY(A2:D100, B2:B100, 1, C2:C100, -1)

Breaking it down:

• A2:D100: Data to sort
• B2:B100, 1: First sort by region column, ascending
• C2:C100, -1: Then sort by amount column, descending

Example: Sort by Calculated Value

Sort by profit margin (Sales - Cost):

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

Sorts by the difference between columns C and D, without needing a helper column.

UNIQUE Function: Remove Duplicates

Extract unique values from a list.

Basic Syntax

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

• array: Range to extract unique values from
• by_col: TRUE = compare columns, FALSE = compare rows - optional, defaults to FALSE
• exactly_once: TRUE = only values that appear once, FALSE = all unique values

Example 1: Unique Customer Names

1=UNIQUE(B2:B100)

Returns each unique customer name once, removing duplicates.

Example 2: Unique Combinations

Get unique combinations of customer and region:

1=UNIQUE(B2:C100)

Returns rows where the combination of customer AND region is unique.

Example 3: Values That Appear Only Once

Find customers who only ordered once:

1=UNIQUE(B2:B100, , TRUE)

Third parameter = TRUE: Only return values that appear exactly once.

Example 4: Count Unique Values

How many unique customers?

1=COUNTA(UNIQUE(B2:B100))

COUNTA counts non-empty cells in the unique list.

SEQUENCE Function: Generate Number Series

Create sequences of numbers automatically.

Basic Syntax

1=SEQUENCE(rows, [columns], [start], [step])

• rows: Number of rows to generate
• columns: Number of columns (optional, default 1)
• start: Starting number (optional, default 1)
• step: Increment (optional, default 1)

Example 1: Simple Number List

Generate numbers 1-100:

1=SEQUENCE(100)

Creates vertical list: 1, 2, 3, ... 100

Example 2: Even Numbers

Generate first 50 even numbers:

1=SEQUENCE(50, , 2, 2)

• 50 rows
• Start at 2
• Step by 2
• Results: 2, 4, 6, 8, ... 100

Example 3: Dates for Next 30 Days

1=TODAY() + SEQUENCE(30, , 0)

• Generates 0-29
• Adds to TODAY()
• Results: Today, tomorrow, ... 29 days from now

Example 4: Multiplication Table

1=SEQUENCE(10) * SEQUENCE(1, 10)

Creates 10x10 multiplication table.

Combining Functions: Real Power

Dynamic array functions become powerful when combined.

Example 1: Filter and Sort

Get high-value orders, sorted by amount:

1=SORT(FILTER(A2:D100, C2:C100>1000), 3, -1)

1. FILTER: Gets orders > $1000
2. SORT: Sorts filtered results by column 3 (amount), descending

Example 2: Unique Filtered Values

Get unique regions with sales > $5000:

1=UNIQUE(FILTER(B2:B100, C2:C100>5000))

1. FILTER: Get rows where sales > $5000
2. UNIQUE: Extract unique regions from filtered results

Example 3: Dynamic Drop-Down List

Create data validation list that updates automatically:

1. Create named range: UniqueCategories = =UNIQUE(Categories!A2:A100)
2. Data Validation → List → Source: =UniqueCategories

Drop-down automatically updates when new categories are added.

Example 4: Filtered Summary Table

Create summary of high-priority items:

1// Column A: Categories
2=UNIQUE(FILTER(Data!B:B, Data!E:E="High"))
3
4// Column B: Count
5=COUNTIFS(Data!B:B, A2#, Data!E:E, "High")
6
7// Column C: Total Value
8=SUMIFS(Data!C:C, Data!B:B, A2#, Data!E:E, "High")

A2# references the spilled range from dynamic array formula in A2.

Example 5: Top 10 by Category

Get top 10 products in each category:

1=LET(
2    cat, A2,
3    filtered, FILTER(Products, Category=cat),
4    sorted, SORT(filtered, 3, -1),
5    TAKE(sorted, 10)
6)

Uses LET to assign variables, then:

1. Filter by category
2. Sort by column 3 (sales)
3. TAKE first 10 rows

Advanced Techniques

Spill Range Operator:

Reference entire spilled range:

1// In A1: =UNIQUE(Data!B2:B100)
2// Results spill to A1:A25
3
4// In B1: =COUNTIF(Data!B:B, A1#)

A1# references the entire spilled range (A1:A25), automatically adjusting if results change.

TAKE and DROP Functions

TAKE: Get first/last N rows or columns

1=TAKE(A2:D100, 10)  // First 10 rows
2=TAKE(A2:D100, -10)  // Last 10 rows
3=TAKE(A2:D100, , 2)  // First 2 columns

DROP: Remove first/last N rows or columns

1=DROP(A1:D100, 1)  // Remove header row
2=DROP(A2:D100, -5)  // Remove last 5 rows

CHOOSECOLS and CHOOSEROWS

Select specific columns or rows:

1=CHOOSECOLS(A2:F100, 1, 3, 5)  // Get columns 1, 3, and 5
2=CHOOSEROWS(A2:F100, 1, 5, 10)  // Get rows 1, 5, and 10

TOCOL and TOROW

Convert ranges to single column or row:

1=TOCOL(A1:D10)  // Converts to single column
2=TOROW(A1:A100)  // Converts to single row

Real-World Applications

Application 1: Sales Dashboard

Problem: Manual dashboard requires constant updating

Solution: Dynamic formulas

1// Total Sales by Region (updates automatically)
2In A2: =UNIQUE(Sales!B:B)  // Regions
3In B2: =SUMIF(Sales!B:B, A2#, Sales!C:C)  // Total sales per region
4
5// Top 10 Products
6=SORT(Products, 3, -1)  // Sorted by sales, descending
7
8// This Week's Orders
9=FILTER(Orders, (OrderDate >= TODAY()-7) * (OrderDate <= TODAY()))

Application 2: Employee Directory

1// Active Employees
2=FILTER(Employees, Status="Active")
3
4// Department List
5=UNIQUE(FILTER(Employees!C:C, Employees!D:D="Active"))
6
7// Employees by Department (dropdown-driven)
8=FILTER(Employees, Department=SelectedDepartment)

Application 3: Inventory Management

1// Low Stock Alert (< 10 units)
2=FILTER(Inventory, Stock<10)
3
4// Items Needing Reorder
5=FILTER(Inventory, (Stock < ReorderPoint) * (Status="Active"))
6
7// Sorted by Days Until Stockout
8=SORTBY(FILTER(Inventory, Stock<50), Stock/DailyUsage, 1)

Application 4: Customer Analysis

1// High-Value Customers (> $10k lifetime value)
2=FILTER(Customers, LifetimeValue>10000)
3
4// Customers by Purchase Frequency
5=SORTBY(Customers, PurchaseCount, -1)
6
7// Unique Cities with Customers
8=UNIQUE(SORT(Customers!E:E))

Common Errors and Solutions

Error: #SPILL!

Cause: Cells where formula wants to spill are not empty

Solution: Clear cells in spill range or move formula

Error: #CALC!

Cause: FILTER found no matching results (when no third parameter provided)

Solution: Add third parameter with default value

1=FILTER(A2:D100, C2:C100>1000, "No results")

Error: #VALUE!

Cause: Incompatible array sizes in criteria

Solution: Ensure filter criteria range matches data range exactly

1// Bad: Mismatched ranges
2=FILTER(A2:D100, C2:C150>1000)
3
4// Good: Matching ranges
5=FILTER(A2:D100, C2:C100>1000)

Error: Results Not Updating

Cause: Formulas set to manual calculation

Solution: Change to automatic calculation

Performance Tips

✅ Use structured references (Tables) for clarity
✅ Avoid volatile functions (NOW, TODAY) in large arrays when possible
✅ Use FILTER before SORT (sort less data)
✅ Reference dynamic arrays with # operator instead of fixed ranges
✅ Use named ranges for reusable dynamic arrays
✅ Limit calculation scope by filtering early

Converting Old Formulas to Dynamic Arrays

Old: Array Formula with CSE

1{=IF(A2:A100="Yes", B2:B100, "")}  // Ctrl+Shift+Enter

New: FILTER

1=FILTER(B2:B100, A2:A100="Yes", "")

Old: SUMPRODUCT for Counting

1=SUMPRODUCT((A2:A100="Yes")*(B2:B100>100))

New: SUMPRODUCT with FILTER or direct count

1=SUMPRODUCT((FILTER(A2:A100, B2:B100>100)="Yes")*1)
2// Or simpler:
3=COUNTA(FILTER(A2:A100, (A2:A100="Yes")*(B2:B100>100)))

Old: Helper Columns for Sorting

Column E: =VLOOKUP formula
Column F: Manual sort

New: SORTBY with Calculated Values

1=SORTBY(A2:D100, VLOOKUP(A2:A100, Lookup, 2, 0), 1)

Key Takeaways

• Dynamic arrays automatically spill results to adjacent cells
• FILTER extracts rows meeting conditions (replaces complex IF arrays)
• SORT/SORTBY automatically sort data (no manual sorting needed)
• UNIQUE removes duplicates (replaces Remove Duplicates feature)
• SEQUENCE generates number patterns (eliminates manual entry)
• Combine functions for powerful data manipulation
• Use # operator to reference spilled ranges
• Excel 365/2021 required for dynamic array functions

Conclusion

Dynamic arrays represent the biggest leap forward in Excel functionality in decades. They eliminate helper columns, complex array formulas, and manual data manipulation steps.

Master FILTER, SORT, UNIQUE, and SEQUENCE, and you'll build dashboards and reports that update automatically, respond to changing data, and require zero maintenance. Your spreadsheets just became dramatically smarter.

Related articles: Ultimate Guide to Excel PivotTables, INDEX MATCH: Dynamic Duo in Excel