Excel Dynamic Arrays: FILTER, SORT, UNIQUE, SEQUENCE & Spill Ranges
Let one formula fill a whole table: spill ranges and #SPILL!, SORT and SORTBY with several keys, FILTER with AND/OR and an if-empty message, UNIQUE distinct lists and counts, SEQUENCE date series and calendars, A1# spill references, VSTACK/TAKE/CHOOSECOLS reports and a top-N dashboard with a chart: 8 hands-on labs, graded instantly.
The first 2 lessons are free — no account needed.
About this course
An Excel dynamic arrays course that lets one formula fill a whole table for one fictional tea wholesaler, Lumen Tea Co., for Excel users moving to Microsoft 365, analysts and anyone who builds reports. You let a formula spill down a column and fix the #SPILL! error, sort customers by revenue with SORT and by two keys or a hidden key with SORTBY, extract orders with FILTER using AND and OR conditions and an if-empty message, list the distinct customers and count them with ROWS(UNIQUE()), generate numbering, date series and a monthly calendar with SEQUENCE, build a region summary on A1# spill references that grows by itself, stack and trim reports with VSTACK, DROP, TAKE and CHOOSECOLS, and finish with a top-N dashboard and a bar chart. The functions need Microsoft 365 or a recent Excel version. The first two labs are free.
Lessons
- Spill Basics: One Formula, Many Cells, and the #SPILL! ErrorFree
- SORT & SORTBY: Largest First, Two Sort Keys and Custom OrdersFree
- FILTER: Criteria Cells, AND / OR Conditions and "If Empty" Pro
- UNIQUE: Distinct Lists, Distinct Counts and "Exactly Once" Pro
- SEQUENCE: Numbering, Date Series and a Monthly Calendar Pro
- Spill References (A1#): A Region Summary That Grows by Itself Pro
- Report Building: VSTACK, DROP, CHOOSECOLS, TAKE and HSTACK Pro
- Capstone: A Dynamic Top-N Dashboard with a Chart Pro