Dynamic Arrays & Spill Behavior

Link Topic Comprehension

Formulas Spill & Other Dynamic Array
Spill Select One…
Characteristics
FILTER Filter (The New Power Lookup) Select One…
SORT(BY) SORT Function & SORTBY Formula Select One…
UNIQUE UNIQUE Function Select One…
Combine Combining Dynamic Array functions Select One…
SEQUENCE SEQUENCE Function Select One…
RANDARRAY RANDARRAY Function Select One…
@ Prefix for Compatibility (Previously
@ Prefix Select One…
SINGLE)
Challenge Challenge: Create a Quick Overview Report Select One…
Your Notes
Formulas Spill & Other Dynamic Array Characteristics INDEX

Raw Data Spilled array

Division Region Revenue

Utility North America 44,196
Utility South America 20,898
Utility Asia 46,994
Utility Europe 43,695
Utility Australia 34,196
Productivity North America 34,155
Productivity South America 24,396
Productivity Asia 29,276
Productivity Europe 45,540
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656
The advantage to using an Excel Table as the source
data is that the spilled array automatically expands as
new data rows are added to the table.

Raw Data as Table Spilled array (from Table)

15000 100
Division Region Revenue Column1
Utility North America 44,196 1 100
Utility South America 20,898 2
Utility Asia 46,994 3
Utility Europe 43,695 4
Utility Australia 34,196 5
Productivity North America 34,155 6
Productivity South America 24,396
Productivity Asia 29,276
Productivity Europe 45,540
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656
 Typing in =A6:C20 in G5 and pressing Enter gives us
a spilled array of all source cells. In this version we referenced a
Click anywhere inside the range and you see the bigger range. We can hide the
blue border where the "spill" has occurred. zeros with Custom number
formatting - highlight the
results range and type in #;-#;
in custom formatting.

xcel Table as the source

automatically expands as
the table.

lled array (from Table) Referencing a spilled array range

n we referenced a
We can hide the
ustom number
highlight the
and type in #;-#;
rmatting.
Filter (The New Power Lookup) INDEX

Syntax is FILTER(array, include, [if_empty])

Division Region Revenue Region

Utility North America 44,196
Utility South America 20,898
Utility Asia 46,994
Utility Europe 43,695
Utility Australia 34,196
Productivity North America 34,155 Revenue greater than:
Productivity South America 24,396
Productivity Asia 29,276
Productivity Europe 45,540
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656

Table as Source

Division Region Revenue Region Asia

Utility North America 44,196
Utility South America 20,898
Utility Asia 46,994
Utility Europe 43,695
Utility Australia 34,196
Productivity North America 34,155
Productivity South America 24,396
Productivity Asia 29,276 Division Game
Productivity Europe 45,540 Region
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656
SORT Function & SORTBY Formula INDEX

Syntax is SORT(array, [sort index], [sort order], [by_col]) Sort by Revenue in descending order

Division Region Revenue Division Region

Utility North America 44,196
Utility South America 20,898
Utility Asia 46,994
Utility Europe 43,695
Utility Australia 34,196
Productivity North America 34,155
Productivity South America 24,396
Productivity Asia 29,276
Productivity Europe 45,540
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656

SORTBY Function
Syntax of SORTBY(array, by_array1, sort_order1…)
Sort by Revenue descending but only show division

Division Region Revenue Division Region

Utility North America 44,196
Utility South America 20,898
Utility Asia 46,994
Utility Europe 43,695
Utility Australia 34,196
Productivity North America 34,155
Productivity South America 24,396
Productivity Asia 29,276
Productivity Europe 45,540
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656
SORT by Column

Division Utility Utility Utility Utility Utility Productivity

Region North America South AmericAsia Europe Australia North America
Revenue 44,196 20,898 46,994 43,695 34,196 34,155

Sort by Revenue in descending order

cending order Sort by Division then Region Sort by Region in ascending and Revenue i

Revenue Division Region Revenue Division

nding but only show division and region

ProductivitProductivitProductivity Productivity Game Game Game
South AmerAsia Europe Australia North AmerSouth AmerAsia
24,396 29,276 45,540 29,277 44,675 42,569 43,784
y Region in ascending and Revenue in descending

Region Revenue
Game Game
Europe Australia
46,336 49,656
UNIQUE Function INDEX

Syntax of UNIQUE(array, [by_col], [occurs_once]) Unique list of Division

Division Region Revenue Division

Utility North America 44,196
Utility South America 20,898
Utility Asia 46,994
Utility Europe 43,695
Utility Australia 34,196
Productivity North America 34,155
Productivity South America 24,396
Productivity Asia 29,276 Unique Count
Productivity Europe 45,540
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656
Health Europe 25,000

Distinct List of Division

Division Region
Utility North America
Utility South America
Utility Asia Unique list of Division and Region
Utility Europe Division Region
Utility Australia
Productivity North America
Productivity South America
Productivity Asia
Productivity Europe
Productivity Australia
Game North America
Game South America
Game Asia
Game Europe
Game Australia
Health Europe
Productivity North America
Productivity Europe
Utility Asia
Utility South America
 Unique list of Division - account for extra rows

Division

Unique Count

Division and Region

Combining Dynamic Array Functions INDEX

Revenue by division (sorted)

Division Region Revenue Revenue
Utility North America 44,196
Utility South America 20,898
Utility Asia 46,994
Utility Europe 43,695
Utility Australia 34,196
Productivity North America 34,155 Divisions with revenue less than 30k
Productivity South America 24,396
Productivity Asia 29,276
Productivity Europe 45,540
Productivity Australia 29,277
Game North America 44,675
Game South America 42,569 Divisions with revenue less than 30k & exclude empt
Game Asia 43,784
Game Europe 46,336
Game Australia 49,656
 Get the total
revenue by division.

han 30k & exclude empty cells