You're analyzing quarterly sales data from twelve regional offices, and your manager asks for a dashboard showing: unique product categories, sorted by revenue, filtered to only profitable items, with sequence numbers for ranking. In the old Excel world, this would require multiple helper columns, complex formulas, and careful range management. One small change to your source data? Time to rebuild everything.
Dynamic arrays changed this game entirely. These functions return multiple values that automatically spill into adjacent cells, creating self-maintaining data transformations that adapt as your source data changes. Master these four core functions—FILTER, SORT, UNIQUE, and SEQUENCE—and you'll build analysis workflows that are both more powerful and more maintainable than traditional Excel approaches.
What you'll learn:
You should be comfortable with Excel basics including cell references (A1 vs $A$1), basic functions like SUM and IF, and understand how arrays work conceptually. Familiarity with Boolean logic (AND/OR operations) will help but isn't required.
Dynamic arrays represent a fundamental shift in how Excel handles multi-cell results. Traditional formulas return single values or require array entry (Ctrl+Shift+Enter). Dynamic array functions automatically determine their output size and claim the necessary cells.
Let's start with a simple example using sales data:
A1: Product B1: Category C1: Revenue D1: Profit
A2: Widget A Electronics 15000 3000
A3: Widget B Electronics 12000 2400
A4: Gadget X Electronics 8000 -500
A5: Tool Y Tools 5000 1000
A6: Tool Z Tools 7000 1500
Enter this UNIQUE formula in cell F1:
=UNIQUE(B2:B6)
Excel immediately spills the result into F1:F2, showing "Electronics" and "Tools". If you add a new category in your source data, the spilled range automatically expands. If you try to type in F2, Excel blocks you with a #SPILL! error, protecting the dynamic range.
This automatic resizing behavior eliminates one of Excel's most frustrating limitations: manually adjusting ranges when data changes. Your formulas become self-maintaining.
FILTER extracts rows that meet specific criteria, returning all matching columns. The syntax is:
FILTER(array, include, [if_empty])
The include argument is where the power lies. This must be a Boolean array (TRUE/FALSE values) with the same number of rows as your data.
Using our sales data, let's filter for profitable products:
=FILTER(A2:D6, C2:C6>0)
This returns complete rows where profit (column C) is positive. The result automatically includes headers if you reference them:
=FILTER(A1:D6, C1:C6<>"Profit") // Excludes the header row
FILTER truly shines with multiple conditions. Want profitable Electronics products with revenue over $10,000?
=FILTER(A2:D6, (B2:B6="Electronics") * (C2:C6>0) * (A2:A6>10000))
The multiplication operator () creates AND logic between conditions. Each condition returns TRUE/FALSE, and TRUETRUE*TRUE=1 (TRUE), while any FALSE makes the result 0 (FALSE).
For OR logic, use addition with comparison operators:
=FILTER(A2:D6, (B2:B6="Electronics") + (B2:B6="Tools"))
This returns products in either Electronics OR Tools categories.
Filtering by text patterns:
=FILTER(A2:D6, ISNUMBER(SEARCH("Widget", A2:A6)))
Filtering by top percentile:
=FILTER(A2:D6, C2:C6>=PERCENTILE(C2:C6, 0.8))
Filtering with calculated conditions:
=FILTER(A2:D6, (C2:C6/A2:A6)>0.2) // Profit margin > 20%
Warning: FILTER returns a #CALC! error if no rows match your criteria. Use the third parameter to provide a default:
FILTER(A2:D6, C2:C6>100000, "No matches found")
SORT arranges data by specified columns with full control over sort direction and order. The syntax is:
SORT(array, [sort_index], [sort_order], [by_col])
Sort our sales data by revenue (column 3), descending:
=SORT(A2:D6, 3, -1)
The third parameter controls direction: 1 for ascending, -1 for descending. Without it, Excel defaults to ascending.
For complex sorting, pass arrays to sort_index and sort_order:
=SORT(A2:D6, {2,3}, {1,-1})
This sorts first by Category (ascending), then by Revenue (descending) within each category. The curly braces create arrays of sort columns and directions.
Sorting by calculated values:
=SORT(A2:D6, 1, 1, FALSE, (C2:C6-D2:D6)) // Sort by profit margin
Custom sort orders: Use helper columns or complex logic for non-alphabetical ordering:
=SORT(A2:D6, 1, 1, FALSE, MATCH(B2:B6, {"Electronics","Tools","Other"}, 0))
Sorting with FILTER:
=SORT(FILTER(A2:D6, C2:C6>0), 3, -1)
This filters profitable items, then sorts by revenue descending.
UNIQUE removes duplicates from data, with options for different types of uniqueness. The syntax is:
UNIQUE(array, [by_col], [occurs_once])
Extract unique categories:
=UNIQUE(B2:B6)
Extract unique combinations of Category and Product:
=UNIQUE(A2:B6)
The by_col parameter (default TRUE) determines whether to compare by columns or rows. For most data analysis, stick with the default.
The occurs_once parameter changes UNIQUE's behavior fundamentally:
FALSE (default): Returns distinct values (standard "remove duplicates")TRUE: Returns values that appear exactly once (excluding items with any duplicates)With sample data containing duplicates:
A8: Widget A Electronics
A9: Widget A Electronics // Duplicate
A10: Unique Item Tools
=UNIQUE(A2:B9, TRUE, FALSE) // Returns all distinct combinations
=UNIQUE(A2:B9, TRUE, TRUE) // Excludes Widget A entirely
Finding data quality issues:
=UNIQUE(A2:A6, TRUE, TRUE) // Products that appear exactly once
Creating dynamic dropdowns:
=UNIQUE(FILTER(B2:B6, A2:A6<>"")) // Non-empty categories for validation lists
Counting distinct values:
=ROWS(UNIQUE(B2:B6)) // Count of unique categories
SEQUENCE creates arrays of sequential numbers with flexible patterns. The syntax is:
SEQUENCE(rows, [columns], [start], [step])
Generate numbers 1 through 10:
=SEQUENCE(10)
Create a 3x4 grid starting at 100, incrementing by 5:
=SEQUENCE(3, 4, 100, 5)
Row numbering:
=SEQUENCE(ROWS(A2:A6)) // Numbers 1 through row count
Date ranges:
=TODAY() + SEQUENCE(30) // Next 30 days
=DATE(2024,1,1) + SEQUENCE(365) // All days in 2024
Sampling and indexing:
=SEQUENCE(10, 1, 1, 2) // Every other number: 1,3,5,7...
Creating lookup arrays:
=INDEX(A2:A6, SEQUENCE(ROWS(A2:A6))) // Returns the original array
Mathematical sequences:
=POWER(2, SEQUENCE(10)) // Powers of 2: 2,4,8,16...
Random sampling with SEQUENCE:
=INDEX(A2:A6, SEQUENCE(3, 1, 1, RANDBETWEEN(1,5))) // Random products
The real power emerges when chaining these functions together. Each function's output becomes another's input, creating sophisticated data processing pipelines.
Start with our expanded dataset:
A1: Product B1: Category C1: Revenue D1: Profit E1: Quarter
A2: Widget A Electronics 15000 3000 Q1
A3: Widget B Electronics 12000 2400 Q1
A4: Widget A Electronics 16000 3200 Q2
A5: Gadget X Electronics 8000 -500 Q1
A6: Tool Y Tools 5000 1000 Q2
A7: Tool Z Tools 7000 1500 Q1
A8: Service A Services 25000 5000 Q2
A9: Widget B Electronics 11000 2200 Q2
Pipeline 1: Top profitable products by category
// Step 1: Filter profitable items
=FILTER(A2:E9, D2:D9>0)
// Step 2: Get unique categories
=UNIQUE(B2:B9)
// Step 3: Sort profitable items by category, then revenue
=SORT(FILTER(A2:E9, D2:D9>0), {2,3}, {1,-1})
Pipeline 2: Quarterly analysis with ranking
// Combined formula for Q2 top performers with ranking
=HSTACK(
SEQUENCE(ROWS(SORT(FILTER(A2:E9, (E2:E9="Q2")*(D2:D9>0)), 3, -1))),
SORT(FILTER(A2:E9, (E2:E9="Q2")*(D2:D9>0)), 3, -1)
)
This creates a ranked list of Q2 profitable products with sequence numbers.
Automatic summary tables:
// Unique categories with performance metrics
=LET(
categories, UNIQUE(B2:B9),
cat_count, ROWS(categories),
results, HSTACK(
categories,
BYROW(categories, LAMBDA(cat,
SUMIF(B2:B9, cat, C2:C9) // Total revenue by category
)),
BYROW(categories, LAMBDA(cat,
SUMIF(B2:B9, cat, D2:D9) // Total profit by category
))
),
results
)
Let's build a comprehensive dashboard using a realistic dataset. You'll create multiple interconnected reports that automatically update when the source data changes.
In a new worksheet, set up this sales data (A1:F20):
Product,Category,Region,Revenue,Cost,Quarter
Laptop Pro,Electronics,North,50000,35000,Q1
Laptop Pro,Electronics,South,45000,31500,Q1
Tablet X,Electronics,East,30000,21000,Q1
Desktop Z,Electronics,West,25000,17500,Q1
Software A,Software,North,15000,5000,Q1
Software B,Software,South,20000,8000,Q1
Chair Deluxe,Furniture,East,8000,4000,Q1
Desk Pro,Furniture,West,12000,6000,Q1
Laptop Pro,Electronics,North,52000,36400,Q2
Laptop Pro,Electronics,South,48000,33600,Q2
Tablet X,Electronics,East,32000,22400,Q2
Desktop Z,Electronics,West,28000,19600,Q2
Software A,Software,North,16000,5600,Q2
Software B,Software,South,22000,8800,Q2
Chair Deluxe,Furniture,East,8500,4250,Q2
Desk Pro,Furniture,West,13000,6500,Q2
Monitor 4K,Electronics,North,15000,9000,Q2
Service Pack,Software,All,30000,10000,Q2
Executive Chair,Furniture,North,5000,2500,Q2
In column H, create a dynamic report showing categories ranked by total profit:
// H1: Category Performance
=LET(
raw_data, A2:F20,
categories, UNIQUE(INDEX(raw_data,,2)),
profits, BYROW(categories, LAMBDA(cat,
SUMPRODUCT((INDEX(raw_data,,2)=cat) *
(INDEX(raw_data,,4)-INDEX(raw_data,,5)))
)),
ranked, SORT(HSTACK(categories, profits), 2, -1),
VSTACK({"Category", "Total Profit"}, ranked)
)
In column K, create a report of Electronics products with profit margins above 25%:
// K1: High-Margin Electronics
=LET(
data, A2:F20,
electronics, FILTER(data, INDEX(data,,2)="Electronics"),
with_margin, HSTACK(electronics,
(INDEX(electronics,,4)-INDEX(electronics,,5))/INDEX(electronics,,4)),
high_margin, FILTER(with_margin, INDEX(with_margin,,7)>0.25),
sorted, SORT(high_margin, 7, -1),
VSTACK({"Product", "Category", "Region", "Revenue", "Cost", "Quarter", "Margin%"},
sorted)
)
In column N, analyze Q2 vs Q1 performance by product:
// N1: QoQ Growth Analysis
=LET(
data, A2:F20,
products, UNIQUE(INDEX(data,,1)),
q1_revenue, BYROW(products, LAMBDA(prod,
SUMIFS(INDEX(data,,4), INDEX(data,,1), prod, INDEX(data,,6), "Q1")
)),
q2_revenue, BYROW(products, LAMBDA(prod,
SUMIFS(INDEX(data,,4), INDEX(data,,1), prod, INDEX(data,,6), "Q2")
)),
growth_pct, (q2_revenue - q1_revenue) / q1_revenue,
results, FILTER(
HSTACK(products, q1_revenue, q2_revenue, growth_pct),
(q1_revenue > 0) * (q2_revenue > 0)
),
sorted, SORT(results, 4, -1),
VSTACK({"Product", "Q1 Revenue", "Q2 Revenue", "Growth %"}, sorted)
)
Create a numbered ranking of regions by total revenue:
// Q1: Regional Rankings
=LET(
data, A2:F20,
regions, UNIQUE(INDEX(data,,3)),
revenue, BYROW(regions, LAMBDA(reg,
SUMIF(INDEX(data,,3), reg, INDEX(data,,4))
)),
sorted, SORT(HSTACK(regions, revenue), 2, -1),
ranked, HSTACK(SEQUENCE(ROWS(sorted)), sorted),
VSTACK({"Rank", "Region", "Total Revenue"}, ranked)
)
Problem: #SPILL! errors when formulas try to write to occupied cells.
Solution: Clear the spill range or move your formula. Dynamic arrays need exclusive access to their output cells.
Problem: FILTER returns #CALC! when the include array has different dimensions than the source array.
// Wrong: Different number of rows
=FILTER(A1:C10, B1:B5>100)
// Right: Matching dimensions
=FILTER(A1:C10, B1:B10>100)
Problem: Using AND/OR functions instead of arithmetic operators in dynamic contexts.
// Wrong: AND doesn't work with arrays
=FILTER(A1:C10, AND(B1:B10>100, C1:C10<50))
// Right: Use multiplication for AND
=FILTER(A1:C10, (B1:B10>100) * (C1:C10<50))
// Right: Use addition for OR
=FILTER(A1:C10, (B1:B10>100) + (C1:C10<50))
Large datasets: Dynamic arrays recalculate whenever source data changes. For datasets over 10,000 rows, consider:
Volatile functions: Avoid RAND(), NOW(), TODAY() in dynamic array formulas unless necessary, as they force constant recalculation.
Dynamic arrays consume memory proportional to their output size. A formula returning 1000x100 array uses significantly more memory than a simple SUM. Monitor performance and consider alternatives for very large results.
Use LET for complex calculations:
// Inefficient: Recalculates UNIQUE multiple times
=SORT(FILTER(A1:D100, ISNUMBER(MATCH(B1:B100, UNIQUE(B1:B100), 0))))
// Efficient: Calculate UNIQUE once
=LET(
unique_cats, UNIQUE(B1:B100),
SORT(FILTER(A1:D100, ISNUMBER(MATCH(B1:B100, unique_cats, 0))))
)
Minimize array size early:
// Better: Filter first, then sort
=SORT(FILTER(A1:D1000, E1:E1000>0), 3, -1)
// Worse: Sort everything, then filter
=FILTER(SORT(A1:D1000, 3, -1), E1:E1000>0)
Excel calculates formulas in dependency order. Structure your workbook so complex dynamic arrays calculate after their inputs stabilize:
For very large datasets or frequently-changing data:
Dynamic arrays fundamentally change how Excel handles multi-row results. FILTER provides precise data selection with complex Boolean logic. SORT creates sophisticated multi-column ordering. UNIQUE extracts distinct values or exactly-once occurrences. SEQUENCE generates structured number patterns for indexing and calculations.
The key insight: these functions work best in combination, creating data processing pipelines that automatically adapt as your source data changes. Your formulas become self-maintaining, eliminating the manual range adjustments that plagued traditional Excel workflows.
Immediate next steps:
Advanced topics to explore:
The dynamic array revolution makes Excel a genuine data transformation tool. Master these patterns, and you'll build analysis workflows that rival dedicated data platforms—while maintaining Excel's accessibility and flexibility.
Learning Path: Advanced Excel & VBA