Now, let's understand the next web development project:

🚀 Excel Formulas Fundamentals — Part 11

⚡ Dynamic Array Functions (FILTER, SORT, UNIQUE, SEQUENCE, RANDARRAY)

Dynamic Array functions are available in modern versions of Microsoft Excel (Microsoft 365 and Excel 2021+). They automatically return multiple results and "spill" them into adjacent cells, making formulas simpler and reducing the need for helper columns.

📌 These functions are becoming increasingly common in modern Excel interviews and workplace tasks.

🧠 1. What are Dynamic Arrays?

A Dynamic Array formula can return multiple values from a single formula.

Example

=UNIQUE(A2:A10)

Instead of returning one value, Excel returns all unique values and automatically fills the cells below.

This automatic expansion is called a spill range.

🔍 2. FILTER() – Filter Data Dynamically

FILTER() returns only the rows that meet a condition.

Syntax

=FILTER(array,include,[if_empty])

Example

Employee Department Salary

Rahul Sales 50000

Priya HR 60000

Amit Sales 55000

Neha Finance 65000

Formula:

=FILTER(A2:C5,B2:B5="Sales","No Data")

Result:

Employee Department Salary

Rahul Sales 50000

Amit Sales 55000

📌 Use Cases:

• Interactive dashboards

• Dynamic reports

• Filtering records without using AutoFilter

📊 3. SORT() – Sort Data Automatically

SORT() sorts data without changing the original dataset.

Syntax

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

Example

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

Meaning

• Sort by the 3rd column (Salary)

• -1 = Descending order

📌 Useful for displaying Top Performers dynamically.

🎯 4. UNIQUE() – Return Unique Values

UNIQUE() removes duplicate values and returns only distinct entries.

Syntax

=UNIQUE(array)

Example

Department

Sales

HR

Sales

Finance

HR

Formula:

=UNIQUE(A2:A6)

Result:

Sales

HR

Finance

📌 Common Uses:

• Dropdown lists

• Customer lists

• Product categories

🔢 5. SEQUENCE() – Generate Number Series

SEQUENCE() creates a sequence of numbers automatically.

Syntax

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

Example

=SEQUENCE(10)

Result:

1

2

3

4

5

6

7

8

9

10

Another Example

=SEQUENCE(5,1,100,10)

Result:

100

110

120

130

140

📌 Useful for creating serial numbers and sample datasets.

🎲 6. RANDARRAY() – Generate Random Numbers

RANDARRAY() creates an array of random numbers.

Syntax

=RANDARRAY(rows,[columns],[min],[max],[whole_number])

Example

=RANDARRAY(5,1,1,100,TRUE)

Result (changes every recalculation):

15

87

42

63

91

📌 Use Cases:

• Sample data

• Testing dashboards

• Random selections

🔄 7. Combining Dynamic Array Functions

Dynamic Array functions can be combined for powerful analysis.

Example

Display unique departments in alphabetical order:

=SORT(UNIQUE(B2:B20))

Result:

Finance

HR

Sales

Example

Display only Sales employees sorted by salary:

=SORT(FILTER(A2:C20,B2:B20="Sales"),3,-1)

This filters Sales employees and sorts them by Salary in descending order.

💼 8. Real-World Business Scenario

Sales Dashboard

Region Product Sales

East Laptop 50000

West Mobile 30000

East Tablet 40000

South Laptop 35000

Show Only East Region

=FILTER(A2:C5,A2:A5="East")

Show Unique Products

=UNIQUE(B2:B5)