The dynamic array concept is one formula can return a table (with multiple rows & columns), with this comes a slew of extra functionality, with new functions and changed behaviour on existing formulas through ARRAYFORMULA. You can also stack different tables on top of another through using {}.
Dynamic array functions include TRANSPOSE switches rows & columns, SPLIT separates text in multiple columns based on a character, UNIQUE returns a unique list based on a source. SORT and FILTER apply those common actions in an output range, IMPORTRANGE grabs a table from elsewhere, GOOGLEFINANCE fetches live stock data, SPARKLINE creates in cell charts, ARRAY_CONSTRAIN allows you to limit the max area, SORTN, and finally the incredibly sophisticated QUERY function.
Explore the QUERY function further here: • QUERY Complete guide: Google Sheets' ...
Excel introduced dynamic Arrays in 2020, but Google Sheets has had them for a lot longer, and they are much more sophisticated.
00:00 Introduction
00:40 UNIQUE
01:28 TRANSPOSE (switch rows/cols)
01:52 SPLIT (text to columns)
02:34 ARRAYFORMULA
03:28 Stacking tables
04:15 IMPORTRANGE
04:53 GOOGLEFINANCE
05:43 SPARKLINE
06:19 SORT
07:16 ARRAY_CONSTRAIN
07:38 SORTN
09:57 FILTER
11:18 QUERY