Dynamic Array Functions with Practical Examples

Dynamic array functions allow one Excel formula to return multiple results automatically. Instead of copying formulas down hundreds of rows, you can use a single formula to filter, sort, extract and generate complete datasets.

In this chapter, you will learn FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, TAKE, DROP, CHOOSECOLS and CHOOSEROWS. You will also combine these functions to create interactive and automatically expanding reports.

Learning Objectives

After completing this chapter, you should be able to:

• Understand dynamic arrays and spilled ranges.
• Filter datasets using one or multiple conditions.
• Sort complete datasets using formulas.
• Generate unique lists automatically.
• Create sequential numbers and dates.
• Extract selected rows and columns.
• Combine multiple dynamic array functions.
• Create dependent dropdown source lists.
• Build dynamic search and reporting tools.
• Identify and resolve common dynamic array errors.

1. What Are Dynamic Arrays in Excel?

A traditional Excel formula normally returns one value in one cell. A dynamic array formula can return multiple values across several rows or columns.

Traditional Formula

=A2*B2

This formula returns a single result and must usually be copied down.

Dynamic Array Formula

=SORT(A2:A20)

This single formula can return a complete sorted list. Excel automatically places the results into the neighbouring cells.

Important Compatibility Note

Dynamic array functions are available in Microsoft 365 and supported modern versions of Excel. Some functions, including TAKE, DROP, CHOOSECOLS and CHOOSEROWS, may not be available in older versions.

2. Understanding the Spill Range

When a formula returns multiple values, Excel places those results into a spill range. Only the first cell contains the actual formula. The remaining cells display its results.

Example

=SEQUENCE(10)

If the formula is entered in cell A2, Excel generates numbers from 1 to 10 in cells A2:A11.

Referencing a Spilled Range

Use the hash symbol to refer to the complete spill range:

=A2#

Count Values in a Spill Range

=COUNTA(A2#)

Calculate the Sum of a Spill Range

=SUM(A2#)

Use a Spill Range as a Chart Source

A chart connected to a spilled range can expand or contract automatically as the formula results change.

3. Create the Sales Dataset

Create a worksheet named Sales_Data and enter the following records:

OrderID	OrderDate	Salesperson	Region	Product	Category	Quantity	TotalSales	Status
ORD-1001	01-Jul-2026	Rahul	East	Laptop	Computer	2	90000	Completed
ORD-1002	02-Jul-2026	Priya	North	Monitor	Computer	4	48000	Completed
ORD-1003	03-Jul-2026	Imran	West	Keyboard	Accessories	10	15000	Pending
ORD-1004	04-Jul-2026	Neha	South	Printer	Office Equipment	3	55500	Completed
ORD-1005	05-Jul-2026	Rahul	East	Mouse	Accessories	15	12000	Completed
ORD-1006	06-Jul-2026	Priya	North	Laptop	Computer	1	45000	Pending
ORD-1007	07-Jul-2026	Imran	West	Monitor	Computer	5	60000	Completed
ORD-1008	08-Jul-2026	Neha	South	Keyboard	Accessories	8	12000	Cancelled
ORD-1009	09-Jul-2026	Rahul	East	Printer	Office Equipment	2	37000	Completed
ORD-1010	10-Jul-2026	Priya	North	Mouse	Accessories	20	16000	Completed
ORD-1011	11-Jul-2026	Imran	West	Laptop	Computer	3	135000	Completed
ORD-1012	12-Jul-2026	Neha	South	Monitor	Computer	6	72000	Pending

Convert the dataset into an Excel Table and name it SalesData.

4. Using the FILTER Function

FILTER returns only the rows or columns that meet selected conditions.

FILTER Syntax

=FILTER(array,include,[if_empty])

FILTER Arguments

• array – The complete range to return.
• include – The condition used to select records.
• if_empty – An optional result when no records match.

Filter East Region Records

=FILTER(
SalesData,
SalesData[Region]="East",
"No Records Found"
)

Filter Completed Orders

=FILTER(
SalesData,
SalesData[Status]="Completed",
"No Completed Orders"
)

Filter Laptop Orders

=FILTER(
SalesData,
SalesData[Product]="Laptop",
"No Laptop Orders"
)

Filter Sales Greater Than ₹50,000

=FILTER(
SalesData,
SalesData[TotalSales]>50000,
"No High-Value Orders"
)

5. FILTER with Criteria from a Cell

A cell reference can be used instead of typing the condition directly. Suppose a region dropdown is created in cell B2.

=FILTER(
SalesData,
SalesData[Region]=B2,
"No Records Found"
)

Changing the region in B2 automatically updates the complete report.

Dynamic Product Filter

Suppose the selected product is stored in B3.

=FILTER(
SalesData,
SalesData[Product]=B3,
"No Product Records Found"
)

6. FILTER with Multiple AND Conditions

Multiply conditions when every condition must be true. Multiplication represents AND logic.

Filter Completed Orders in the East Region

=FILTER(
SalesData,
(SalesData[Region]="East")*
(SalesData[Status]="Completed"),
"No Records Found"
)

Filter Using Criteria Cells

Suppose Region is selected in B2 and Status is selected in B3.

=FILTER(
SalesData,
(SalesData[Region]=B2)*
(SalesData[Status]=B3),
"No Records Found"
)

Filter High-Value Laptop Orders

=FILTER(
SalesData,
(SalesData[Product]="Laptop")*
(SalesData[TotalSales]>=50000),
"No Matching Orders"
)

Filter Between Two Dates

Suppose Start Date is stored in B4 and End Date is stored in B5.

=FILTER(
SalesData,
(SalesData[OrderDate]>=B4)*
(SalesData[OrderDate]<=B5),
"No Orders Found"
)

7. FILTER with OR Conditions

Add conditions when any one condition can be true. Addition represents OR logic.

Filter East or North Records

=FILTER(
SalesData,
(SalesData[Region]="East")+
(SalesData[Region]="North"),
"No Records Found"
)

Filter Completed or Pending Orders

=FILTER(
SalesData,
(SalesData[Status]="Completed")+
(SalesData[Status]="Pending"),
"No Records Found"
)

Combine AND and OR Conditions

Return Completed orders from either East or North:

=FILTER(
SalesData,
(
(SalesData[Region]="East")+
(SalesData[Region]="North")
)*
(SalesData[Status]="Completed"),
"No Records Found"
)

8. Return Selected Columns with FILTER

FILTER does not always need to return every column. You can select a smaller return range.

Return Order ID, Salesperson and Total Sales

=FILTER(
SalesData[[OrderID]:[Salesperson]],
SalesData[Region]="East",
"No Records Found"
)

The formula above returns all columns between OrderID and Salesperson. To return separated columns, use CHOOSECOLS.

9. Using the SORT Function

SORT arranges the values in a range or array in ascending or descending order.

SORT Syntax

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

SORT Arguments

• array – The data that should be sorted.
• sort_index – The row or column number used for sorting.
• sort_order – Use 1 for ascending or -1 for descending.
• by_col – Use FALSE to sort rows or TRUE to sort columns.

Sort the Complete Table by Order ID

=SORT(SalesData,1,1)

Sort by Total Sales from Highest to Lowest

TotalSales is the eighth column in the SalesData table.

=SORT(SalesData,8,-1)

Sort by Order Date from Newest to Oldest

=SORT(SalesData,2,-1)

10. Combining FILTER and SORT

FILTER can select the records, while SORT arranges the filtered results.

Filter East Region and Sort by Total Sales

=SORT(
FILTER(
SalesData,
SalesData[Region]="East",
"No Records Found"
),
8,
-1
)

Filter Completed Orders and Sort by Date

=SORT(
FILTER(
SalesData,
SalesData[Status]="Completed",
"No Records Found"
),
2,
-1
)

Dynamic Criteria Version

=SORT(
FILTER(
SalesData,
SalesData[Region]=B2,
"No Records Found"
),
8,
-1
)

11. Using the SORTBY Function

SORTBY sorts an array using one or more corresponding ranges. It is often easier to understand than specifying fixed column numbers.

SORTBY Syntax

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

Sort Sales Data by Total Sales

=SORTBY(
SalesData,
SalesData[TotalSales],
-1
)

Sort by Region and Then Total Sales

=SORTBY(
SalesData,
SalesData[Region],
1,
SalesData[TotalSales],
-1
)

This formula sorts Region alphabetically and then sorts Total Sales from highest to lowest within each region.

Sort Completed Orders by Total Sales

=SORTBY(
FILTER(
SalesData,
SalesData[Status]="Completed",
"No Records Found"
),
FILTER(
SalesData[TotalSales],
SalesData[Status]="Completed"
),
-1
)

12. SORT vs SORTBY

SORT	SORTBY
Uses a row or column position	Uses an actual sort range
Simple for small arrays	More readable for structured data
May depend on a fixed column number	Can reference named table columns
Supports one selected sort position	Conveniently supports multiple sort levels

13. Using the UNIQUE Function

UNIQUE returns a list containing one copy of each distinct value.

UNIQUE Syntax

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

Generate a Unique Region List

=UNIQUE(SalesData[Region])

Generate a Unique Product List

=UNIQUE(SalesData[Product])

Generate a Unique Salesperson List

=UNIQUE(SalesData[Salesperson])

Sort the Unique Region List

=SORT(UNIQUE(SalesData[Region]))

Count Unique Products

=COUNTA(UNIQUE(SalesData[Product]))

Count Unique Salespeople in the East Region

=COUNTA(
UNIQUE(
FILTER(
SalesData[Salesperson],
SalesData[Region]="East"
)
)
)

14. Return Values That Appear Exactly Once

The third UNIQUE argument can return only records that appear exactly once.

=UNIQUE(
SalesData[Product],
FALSE,
TRUE
)

This formula returns products appearing only once in the selected column.

15. Create Dynamic Dropdown Source Lists

UNIQUE and SORT can generate an automatically updating dropdown source.

Step 1: Generate a Unique List

Enter the following formula in cell H2:

=SORT(UNIQUE(SalesData[Region]))

Step 2: Create the Dropdown

1. Select the required input cell.
2. Go to Data → Data Validation.
3. Select List.
4. Enter the spill-range reference as the source.

=H2#

When a new region is added to SalesData, the dropdown list updates automatically.

16. Create a Dependent Product List

Suppose the user selects a Category in B2. Generate products belonging only to that category:

=SORT(
UNIQUE(
FILTER(
SalesData[Product],
SalesData[Category]=B2,
"No Products Found"
)
)
)

Use the spilled result as the source for a dependent Product dropdown.

17. Using the SEQUENCE Function

SEQUENCE generates a series of sequential numbers arranged in rows and columns.

SEQUENCE Syntax

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

Generate Numbers from 1 to 10

=SEQUENCE(10)

Generate Numbers from 1001 to 1010

=SEQUENCE(10,1,1001,1)

Generate Even Numbers

=SEQUENCE(10,1,2,2)

Generate Numbers Horizontally

=SEQUENCE(1,12,1,1)

Create Serial Numbers Based on Record Count

=SEQUENCE(ROWS(SalesData))

18. Generate a Date Series

Generate the Next 30 Dates

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

Format the results as dates.

Generate the First Date of 12 Consecutive Months

=EDATE(
DATE(2026,1,1),
SEQUENCE(12,1,0,1)
)

Generate Month Names

=TEXT(
EDATE(
DATE(2026,1,1),
SEQUENCE(12,1,0,1)
),
"mmmm"
)

Generate Working Dates

=WORKDAY(
TODAY(),
SEQUENCE(10)
)

19. Using the TAKE Function

TAKE returns a specified number of rows or columns from the beginning or end of an array.

TAKE Syntax

=TAKE(array,rows,[columns])

Return the First Five Sales Records

=TAKE(SalesData,5)

Return the Last Five Sales Records

=TAKE(SalesData,-5)

Return the First Four Columns

=TAKE(SalesData,,4)

Return the Last Two Columns

=TAKE(SalesData,,-2)

20. Display the Top Five Sales

First sort the data from highest to lowest, and then take the first five rows.

=TAKE(
SORTBY(
SalesData,
SalesData[TotalSales],
-1
),
5
)

Display the Bottom Five Sales

=TAKE(
SORTBY(
SalesData,
SalesData[TotalSales],
1
),
5
)

Top Three Completed Orders

=TAKE(
SORTBY(
FILTER(
SalesData,
SalesData[Status]="Completed"
),
FILTER(
SalesData[TotalSales],
SalesData[Status]="Completed"
),
-1
),
3
)

21. Using the DROP Function

DROP removes a specified number of rows or columns from the beginning or end of an array.

DROP Syntax

=DROP(array,rows,[columns])

Remove the First Five Rows

=DROP(SalesData,5)

Remove the Last Three Rows

=DROP(SalesData,-3)

Remove the First Two Columns

=DROP(SalesData,,2)

Remove the Last Two Columns

=DROP(SalesData,,-2)

Practical Use of DROP

• Removing imported header rows
• Removing footer or total rows
• Ignoring the first few records
• Preparing a smaller array for another calculation

22. Using the CHOOSECOLS Function

CHOOSECOLS returns selected columns from an array. The columns do not need to be next to each other.

CHOOSECOLS Syntax

=CHOOSECOLS(array,col_num1,[col_num2],...)

Return Order ID, Salesperson and Total Sales

In SalesData, these are columns 1, 3 and 8.

=CHOOSECOLS(
SalesData,
1,
3,
8
)

Return Region, Product and Status

=CHOOSECOLS(
SalesData,
4,
5,
9
)

Return the Last Column

=CHOOSECOLS(SalesData,-1)

Filter and Return Only Selected Columns

=CHOOSECOLS(
FILTER(
SalesData,
SalesData[Region]="East",
"No Records Found"
),
1,
2,
3,
5,
8
)

23. Using the CHOOSEROWS Function

CHOOSEROWS returns selected rows from an array.

CHOOSEROWS Syntax

=CHOOSEROWS(array,row_num1,[row_num2],...)

Return the First, Third and Fifth Records

=CHOOSEROWS(
SalesData,
1,
3,
5
)

Return the Last Record

=CHOOSEROWS(SalesData,-1)

Return the Last Three Records

=CHOOSEROWS(
SalesData,
-1,
-2,
-3
)

24. Build a Dynamic Sales Report

Create a worksheet named Dynamic_Report with the following selection cells:

Cell	Selection
B2	Region
B3	Product
B4	Status
B5	Minimum Sales

Dynamic Report Formula

=SORTBY(
FILTER(
SalesData,
(SalesData[Region]=B2)*
(SalesData[Product]=B3)*
(SalesData[Status]=B4)*
(SalesData[TotalSales]>=B5),
"No Matching Records"
),
FILTER(
SalesData[TotalSales],
(SalesData[Region]=B2)*
(SalesData[Product]=B3)*
(SalesData[Status]=B4)*
(SalesData[TotalSales]>=B5)
),
-1
)

Return Only Important Columns

=CHOOSECOLS(
SORTBY(
FILTER(
SalesData,
(SalesData[Region]=B2)*
(SalesData[Status]=B4)*
(SalesData[TotalSales]>=B5),
"No Matching Records"
),
FILTER(
SalesData[TotalSales],
(SalesData[Region]=B2)*
(SalesData[Status]=B4)*
(SalesData[TotalSales]>=B5)
),
-1
),
1,
2,
3,
5,
8,
9
)

25. Create an Optional “All” Selection

A dashboard filter may include an “All” option. The following condition returns every region when B2 contains All:

=FILTER(
SalesData,
IF(
B2="All",
TRUE,
SalesData[Region]=B2
),
"No Records Found"
)

Use “All” for Region and Status

=FILTER(
SalesData,
IF(
B2="All",
TRUE,
SalesData[Region]=B2
)*
IF(
B3="All",
TRUE,
SalesData[Status]=B3
),
"No Records Found"
)

26. Count and Summarize Filtered Results

Suppose a FILTER formula is entered in cell G8.

Count Filtered Records

=ROWS(G8#)

Count All Nonblank Cells in the Result

=COUNTA(G8#)

Calculate Total Sales from the Filtered Result

If Total Sales is the eighth column in the spill range:

=SUM(CHOOSECOLS(G8#,8))

Calculate Average Sales

=AVERAGE(CHOOSECOLS(G8#,8))

Find the Highest Sale

=MAX(CHOOSECOLS(G8#,8))

27. Understanding the #SPILL! Error

Excel displays #SPILL! when a dynamic array formula cannot place all its results into the required cells.

Common Causes

• One or more cells in the spill range are not empty.
• Merged cells are blocking the output.
• The formula is trying to spill inside an Excel Table.
• The output would extend beyond the worksheet boundary.
• The spill range has an unsupported structure.

How to Fix #SPILL!

1. Select the cell containing the error.
2. Click the warning icon.
3. Select Select Obstructing Cells.
4. Clear or move the blocking values.
5. Remove merged cells from the spill area.
6. Place the formula outside the Excel Table if necessary.

28. Other Common Dynamic Array Errors

#CALC! Error

FILTER may return #CALC! when no records match and the optional if_empty argument has not been provided.

=FILTER(
SalesData,
SalesData[Region]=B2,
"No Records Found"
)

#VALUE! Error

This may occur when arrays used together do not have compatible dimensions.

#REF! Error

This can occur when a referenced workbook, worksheet or range is unavailable.

#NAME? Error

This may mean that the Excel version does not support the function or that the function name is misspelled.

29. Dynamic Array Best Practices

• Keep spill areas free of manually entered data.
• Avoid merged cells around dynamic array formulas.
• Use Excel Tables as source data.
• Place spilling formulas outside Excel Tables.
• Use the FILTER if_empty argument.
• Use criteria cells and dropdowns for interactive reports.
• Use SORTBY when sorting by named table columns.
• Use UNIQUE and SORT to create clean dropdown sources.
• Use CHOOSECOLS to display only necessary fields.
• Reference complete spilled results using the hash symbol.
• Test reports with conditions that return no records.
• Check Excel-version compatibility before sharing the workbook.

30. Practical Assignment: Dynamic Sales Search Report

Create an interactive sales-search report using at least 100 sales transactions.

Required Worksheets

• Sales_Data
• Support_Lists
• Dynamic_Report
• Top_Sales

Required Features

1. Convert the source data into an Excel Table.
2. Name the table SalesData.
3. Create unique lists of Regions, Products and Salespeople.
4. Sort all unique lists alphabetically.
5. Create dynamic dropdown lists using spill references.
6. Filter records by Region, Product and Status.
7. Add Start Date and End Date filters.
8. Add a Minimum Sales condition.
9. Sort filtered records from highest to lowest sales.
10. Display only Order ID, Date, Salesperson, Product and Total Sales.
11. Display the top five matching orders.
12. Calculate total, average and maximum sales from the spill range.
13. Display a custom message when no record is found.
14. Create a chart connected to the dynamic report.

31. Practice Exercises

Exercise 1: Employee Filter

Filter employees by Department and Employment Status. Sort the results by salary from highest to lowest.

Exercise 2: Unique Customer List

Generate a sorted list of unique customer names and count the total number of unique customers.

=COUNTA(
UNIQUE(CustomerData[CustomerName])
)

Exercise 3: Top Five Products

Sort products by Total Sales and return the top five records.

=TAKE(
SORTBY(
ProductData,
ProductData[TotalSales],
-1
),
5
)

Exercise 4: Monthly Date List

Generate the first date of 12 consecutive months and display the month names.

Exercise 5: Multiple-Condition Report

Filter orders using Region, Salesperson, Status and a selected date range.

32. Chapter Quiz

1. What is a dynamic array?
2. What is a spill range?
3. What does the hash symbol mean in A2#?
4. Which function returns records matching selected conditions?
5. How is AND logic represented inside FILTER?
6. How is OR logic represented inside FILTER?
7. What is the difference between SORT and SORTBY?
8. Which function returns distinct values?
9. What does SEQUENCE generate?
10. How can you return the top five records?
11. What is the purpose of CHOOSECOLS?
12. What usually causes a #SPILL! error?

33. Frequently Asked Questions

Do dynamic array formulas need to be copied down?

No. One formula automatically returns and updates all required results.

Can dynamic array formulas be entered inside an Excel Table?

Spilled dynamic array formulas should generally be entered outside Excel Tables. However, they can use a Table as their source.

How can I refer to the complete output of a dynamic formula?

Use the hash symbol after the formula’s first cell. For example, G8# refers to the complete spill range beginning at G8.

Why does FILTER return #CALC!?

This may happen when no record matches the condition and an empty-result message was not supplied. Use the third FILTER argument.

Can FILTER use more than one condition?

Yes. Multiply conditions for AND logic and add conditions for OR logic.

Can dynamic arrays be used in charts?

Yes. A chart can use a spilled range, allowing it to update when the number of returned records changes.

Conclusion

Dynamic array functions make Excel reports faster, more interactive and easier to maintain. FILTER extracts matching records, SORT and SORTBY arrange data, UNIQUE generates distinct lists and SEQUENCE creates automatic number or date series.

TAKE, DROP, CHOOSECOLS and CHOOSEROWS provide additional control over which parts of an array are displayed. By combining these functions, you can build search reports, top-performing lists, dynamic dropdowns and automatically expanding dashboards with fewer formulas.

In the next chapter, we will study text functions and data-cleaning techniques, including LEFT, RIGHT, MID, LEN, FIND, SEARCH, TRIM, CLEAN, SUBSTITUTE, TEXTBEFORE, TEXTAFTER and TEXTSPLIT.