Chapter 09 | Modern Excel Functions

Dynamic Array Functions

Build formulas that return complete lists, reports and sequences from a single cell. This chapter explains spill behaviour and teaches FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, TAKE, DROP and CHOOSECOLS through practical office and business applications.

Start Learning Practical Assignment

Choose Language

Dynamic Reporting Chapter · Eight Practical Activities Included

Learning Objectives

After This Chapter, You Will Be Able To

Build automatically updating lists and reports that reduce manual filtering, copying, sorting and helper-column work.

Control Spill Results

Understand dynamic arrays, spill ranges, the anchor cell and the causes of #SPILL! errors.

Generate Live Reports

Use FILTER to return every record that meets one or more practical business conditions.

Organize Results

Sort, deduplicate, sequence and reshape output without changing the original source data.

Build Smart Formulas

Combine modern functions to create compact, reusable and automatically expanding report systems.

Version Check: Dynamic array functions require an Excel edition that supports spill formulas. Before beginning, confirm that a simple formula such as =SEQUENCE(5) fills five cells automatically.

Lesson 1

Understanding Dynamic Arrays and Spill Behaviour

A dynamic-array formula can return more than one value and place the results into neighbouring cells automatically.

1 From One Formula to a Complete Result Range

Traditional formulas usually return one answer in one cell. A dynamic-array formula may return a complete list, table, row, column or calculated sequence. You enter the formula only in the first cell, called the anchor cell, and Excel expands the result into the required area.

Dynamic Array: A formula result containing multiple values that automatically spills into adjacent cells.

=SEQUENCE(6)

Open

Important Spill Principles

The formula is stored only in the anchor cell.
The spill area automatically expands or contracts when the source data changes.
You cannot edit an individual result cell inside the spill range.
Clearing the anchor cell removes the complete dynamic result.
The spill-reference operator # refers to the full output, such as H2#.

=SEQUENCE(6)

Creates the values 1 to 6 vertically from one formula.

=SUM(H2#)

Uses the complete current spill range beginning at H2.

Why #SPILL! Appears

The #SPILL! error normally means Excel cannot place all returned values in the required area. A cell may contain data, merged cells may block the range, the formula may be inside an unsuitable structure, or the expected output may extend beyond the worksheet boundary.

Do not overwrite spill results: Keep a clear output area around dynamic formulas and place labels outside the expected spill range.

Practical Experiment 1: Observe a Spill Range

Create a simple sequence and observe how Excel manages the returned cells.

Step 1: Create

Enter =SEQUENCE(10) in an empty cell and press Enter.

Step 2: Inspect

Select different output cells and notice that the formula remains attached to the first cell.

Step 3: Block

Place a value inside the expected output area, re-enter the formula and study the #SPILL! message.

Learning Output: You will understand the anchor cell, spill boundary, automatic resizing and spill obstruction.

Lesson 2

FILTER: Return Matching Records Automatically

FILTER creates a live report containing only the rows that meet the selected condition.

2 Creating Condition-Based Lists

Syntax: =FILTER(array, include, [if_empty])

The array is the data to return. The include argument is a TRUE/FALSE test with the same number of rows or columns. The optional if_empty argument controls what appears when no record matches.

Business Requirement	Example Formula	Output
Show West region sales	`=FILTER(A2:F200,C2:C200="West","No records")`	All rows where Region is West
Show sales above 50000	`=FILTER(A2:F200,F2:F200>50000,"No records")`	Rows meeting the amount threshold
Show West sales above 50000	`=FILTER(A2:F200,(C2:C200="West")*(F2:F200>50000),"No records")`	Rows meeting both conditions
Show West or North region	`=FILTER(A2:F200,(C2:C200="West")+(C2:C200="North"),"No records")`	Rows meeting either condition

AND and OR Logic Inside FILTER

Multiplication (*) acts like AND because every condition must evaluate as TRUE. Addition (+) acts like OR because a row is included when at least one condition is TRUE. Place each logical test inside parentheses to make complex formulas easy to audit.

Professional Tip: Convert the source into an Excel Table and use structured references so the FILTER source expands when new records are added.

Practical Experiment 2: Department-Wise Employee List

Create a dropdown-driven employee report using FILTER.

Step 1: Prepare

Create Employee ID, Name, Department, City and Salary columns with at least 15 records.

Step 2: Filter

Type a department in H2 and use =FILTER(A2:E16,C2:C16=H2,"No employee").

Step 3: Test

Change H2 and add a new matching employee to confirm the report updates automatically.

Learning Output: You will build a reusable report controlled by one selection cell.

Lesson 3

SORT and SORTBY: Organize Dynamic Results

Sort a returned list without changing the order of the original database.

3 Formula-Based Sorting

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

Sorts a range by a column or row position inside the returned array.

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

Sorts one range according to values in another specified range.

When to Use SORT

Use SORT when the sorting column is included in the array and its position is stable. For example, =SORT(A2:F200,6,-1) sorts the complete data by the sixth column in descending order.

When to Use SORTBY

Use SORTBY when you want to name the exact sorting range or sort by a field that is not part of the displayed output. For example, =SORTBY(A2:C200,F2:F200,-1) displays columns A:C while sorting by the values in column F.

Sort Order	Value	Meaning
Ascending	1	A to Z, smallest to largest, oldest to newest
Descending	-1	Z to A, largest to smallest, newest to oldest

Practical Experiment 3: Top-Value Sales Report

Combine FILTER and SORT to create a high-value report.

Step 1: Filter

Return transactions above a selected value using FILTER.

Step 2: Sort

Wrap the FILTER formula inside SORT or SORTBY and arrange the highest value first.

Step 3: Verify

Change a source amount and confirm the report is filtered and re-sorted instantly.

Suggested Formula: =SORT(FILTER(A2:F200,F2:F200>H2,"No records"),6,-1)

Lesson 4

UNIQUE: Build Distinct and One-Time Lists

Extract distinct departments, products, cities, customers or categories without manually removing duplicates.

4 Automatically Updating Master Lists

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

By default, UNIQUE returns each distinct value once. Setting exactly_once to TRUE returns only values that appear exactly one time in the source, which is useful for identifying one-time customers, unmatched codes or unusual transactions.

=UNIQUE(C2:C200)

Returns every distinct value from the selected column.

=SORT(UNIQUE(C2:C200))

Creates an alphabetically sorted, automatically expanding master list.

=UNIQUE(C2:C200,,TRUE)

Returns only values that occur exactly once.

=COUNTA(UNIQUE(C2:C200))

Counts the number of distinct nonblank values in the range.

Blank values: Clean or filter blank source cells when you do not want a blank item to appear in the distinct list.

Practical Experiment 4: Dynamic Dropdown Source

Create a clean list that can support data validation and report controls.

Step 1: Extract

Use =SORT(UNIQUE(Table1[Department])) in an empty area.

Step 2: Reference

Use the spill reference from the anchor cell, such as =H2#, as the source for a named range.

Step 3: Expand

Add a new department to the source table and confirm it appears in the spill list.

Learning Output: You will create a self-maintaining master list for report filters and controlled entry.

Lesson 5

SEQUENCE: Generate Numbers, Dates and Structured Patterns

Create serial numbers, month lists, time slots and calculation grids from one formula.

5 Creating Controlled Series

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

Requirement	Formula	Result
Numbers 1 to 12	`=SEQUENCE(12)`	Vertical sequence
Numbers 10 to 100 by 10	`=SEQUENCE(10,1,10,10)`	10, 20, 30 ... 100
12 monthly starting dates	`=EDATE(DATE(2026,1,1),SEQUENCE(12,1,0,1))`	One date for each month
Five-column number grid	`=SEQUENCE(4,5)`	Four rows and five columns

Practical Uses

Create automatically numbered output beside a FILTER result.
Generate future due dates or monthly reporting periods.
Create test datasets and multiplication grids.
Develop hourly appointment slots using a time step.

Practical Experiment 5: Monthly Planning Calendar

Generate a 12-month planning list from a selected start date.

Step 1: Input

Enter the first planning date in H2.

Step 2: Generate

Use =EDATE(H2,SEQUENCE(12,1,0,1)).

Step 3: Format

Apply a month-year format and add planned targets in the next column.

Learning Output: You will generate a complete reporting calendar from one starting date.

Lesson 6

TAKE, DROP and CHOOSECOLS: Shape the Final Output

Control which rows and columns are displayed after a dynamic formula returns a larger result.

6 Reshaping Dynamic Reports

TAKEReturns a specified number of rows or columns from the beginning or end.

DROPRemoves a specified number of rows or columns from the beginning or end.

CHOOSECOLSReturns only the selected column positions from an array.

Spill ReferenceUses the complete dynamic output through the anchor-cell reference followed by #.

=TAKE(SORTBY(A2:F200,F2:F200,-1),10)

Returns the top ten rows after sorting by amount.

=DROP(A2:F200,1)

Removes the first row from the selected array.

=CHOOSECOLS(A2:F200,1,2,6)

Returns only columns 1, 2 and 6 from the source.

=CHOOSECOLS(FILTER(A2:F200,C2:C200=H2),1,2,4,6)

Filters matching rows and shows only required report fields.

Report Design Tip: Use CHOOSECOLS to separate the reporting layout from the database layout. The source can remain detailed while the final report displays only decision-useful fields.

Practical Experiment 6: Top Five Customer Summary

Create a focused report showing only customer name, region and sales value.

Step 1: Sort

Sort all customer records by sales in descending order.

Step 2: Limit

Use TAKE to keep only the first five records.

Step 3: Select

Use CHOOSECOLS to display only the three required fields.

Challenge: Build the complete output in one nested formula and then change the top-record count from 5 to 10.

Lesson 7

Combining Dynamic Array Functions

The real strength of modern Excel appears when functions are nested to complete several reporting steps at once.

7 Building a Complete Formula Workflow

1Select

Choose the database and the report conditions.

2Filter

Return only the rows matching the user selection.

3Organize

Sort the matching data and remove duplicates when required.

4Present

Select columns, limit rows and use the spill result in charts or formulas.

Example: Region-Based Top-10 Sales Report

Formula:
=TAKE(SORTBY(FILTER(A2:F200,C2:C200=H2,"No records"),FILTER(F2:F200,C2:C200=H2),-1),10)

This formula first filters the selected region, sorts the filtered records by sales amount in descending order and then returns the top ten rows. A separate selection cell controls the complete report.

Design Rules for Nested Dynamic Formulas

Build and test the inner function before wrapping it inside another function.
Keep source ranges aligned and equal in height or width.
Use meaningful input cells instead of typing conditions directly into every formula.
Add a clear no-result message to FILTER.
Use Excel Tables to reduce future range-maintenance work.
Document the report logic near the output area.

Practical Experiment 7: Interactive Branch Report

Create a report that changes when the learner selects a branch and reporting limit.

Step 1: Controls

Create a branch dropdown in H2 and enter the required record count in H3.

Step 2: Formula

Combine FILTER, SORTBY, TAKE and CHOOSECOLS using H2 and H3.

Step 3: Review

Change the controls and confirm the returned rows, order and selected columns respond correctly.

Learning Output: You will build a multi-step interactive report with no manual filtering or copying.

Real-Time Practical Assignment

Dynamic Sales Report Generator

Create a decision-ready report that updates automatically when the source data or report controls change.

Business Scenario

A growing distribution company maintains a transaction database with Date, Invoice No., Salesperson, Region, Product, Quantity and Sales Amount. Management wants a report where a user can choose a region, minimum sales value and number of records to display.

1Prepare the Database

Convert at least 50 transaction rows into an Excel Table with accurate headings and data types.

2Create Report Controls

Add region selection, minimum sales value and maximum record count cells with clear labels.

3Build the Output

Use dynamic-array functions to filter, sort, limit and select the required report columns.

4Add Supporting Metrics

Calculate returned-record count, total sales and average sales using the spill reference.

5Test Exceptions

Test no-match conditions, blank controls, new source rows and blocked spill areas.

6Present Professionally

Add a title, instructions, consistent number formats and a visible last-reviewed note.

Required Output: A working region-based report, a sorted top-record view, a distinct selection list, three spill-based summary calculations and a one-page explanation of the formula workflow.

Formula Selection Lab

Choose a Dynamic Reporting Task

Select the required output and generate a suitable starter formula.

Reporting Task Source Data Range Condition / Sort Range Control Cell

Recommended: Select a task to view a formula.

Practice Worksheet

Create one FILTER report controlled by a dropdown.
Create one report using two AND conditions.
Produce an alphabetical distinct product list.
Create a top-five customer report.
Generate 12 monthly dates with SEQUENCE.
Use a spill reference in SUM and COUNTA.

Quality Review Checklist

The output area is clear and not blocked.
Source and condition arrays are correctly aligned.
FILTER contains a useful no-result message.
The source expands when new records are added.
Number, currency and date formats are consistent.
Report controls and formula logic are documented.

Practical Experiment 8: Spill-Range Summary

Create summary calculations that automatically follow the size of a dynamic report.

Step 1: Identify

Suppose the dynamic report begins in J5. Confirm the full output can be referenced as J5#.

Step 2: Calculate

Use spill-aware formulas such as COUNTA, ROWS or SUM with the correct returned column.

Step 3: Test

Change the report criteria and confirm the summaries update as the spill range changes size.

Learning Output: You will connect dynamic reports with automatically updating management indicators.

AICPE Quality Learning Commitment: AICPE Gurukul provides practical, skill-based and career-oriented learning for students, institutes and professionals. Learn more at aicpeindia.org and aicpe.online.

Common Mistakes

Dynamic Array Errors Learners Should Avoid

Most problems come from blocked output areas, misaligned ranges or formulas that do not handle empty results.

Wrong Habits

Typing values inside an expected spill area.
Using source and condition ranges of different sizes.
Editing an individual cell inside a spill result.
Omitting the FILTER no-result argument.
Using fixed ranges that ignore future source records.
Nesting several functions before testing each step.
Placing report labels directly below an expanding array.

Professional Habits

Keep a clear, planned spill-output zone.
Use Excel Tables and aligned structured references.
Modify the formula only through its anchor cell.
Provide clear no-match messages for users.
Build nested formulas one tested layer at a time.
Use spill references for downstream calculations.
Document input controls and expected output behaviour.

Remember: A dynamic report requires space to expand. Treat the spill area as a live output zone, not as ordinary cells for manual entry.

Quick Revision

Remember These Dynamic Array Principles

Review these points before moving to LET and LAMBDA functions.

Spill Behaviour

One anchor formula can return multiple values, and the output grows or shrinks automatically.

FILTER

Return only matching records and combine multiple criteria with AND or OR logic.

SORT and SORTBY

Arrange dynamic results without changing the original database order.

UNIQUE

Create automatically updating distinct lists for reports, controls and analysis.

SEQUENCE

Generate serial numbers, dates and structured patterns from one formula.

Shape the Output

Use TAKE, DROP and CHOOSECOLS to limit rows and display only required report fields.