Dynamic Array Formulas
Dynamic Array Formulas are one of the biggest changes to Excel ever. They completely change the way you solve hard problems in Excel, with excellent new functions and an upgraded formula engine that can return multiple results at the same time. With Dynamic Arrays, you can extract unique values, sort and filter data, look up multiple values, split text values effortlessly, and build dynamic lists that instantly respond to new information. This course covers new functions in Excel that leverage dynamic arrays directly, including FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, RANDARRAY, TEXTAFTER, TEXTBEFORE, TEXTSPLIT, and XLOOKUP. It also covers Boolean logic, a useful way to perform conditional sums and counts in modern array formulas.
Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021.
Price
Sample Videos
What you get in this course
- Lifetime access to all videos 24 x 7 – learn online from anywhere
- Over 35 worksheets for immediate guided practice
- Complete audio transcript with each video
- Both Windows and Mac shortcuts in all videos
- A free Excel Shortcut PDF with 200+ shortcuts
- A 30-day money-back guarantee (we want you to be 100% satisfied)
Video lessons in this course
| Video | Time |
|---|---|
| 1. What are Dynamic Array formulas? | 3:14 |
| 2. New dynamic array functions in Excel | 3:38 |
| 3. Spilling and the spill range | 2:34 |
| 4. Dynamic arrays are native | 2:52 |
| 5. Nesting dynamic array formulas | 3:02 |
| Video | Time |
|---|---|
| 6. What is an array? | 2:34 |
| 7. What is an array formula? | 2:53 |
| 8. 3 basic array formulas | 2:55 |
| 9. How to see arrays in formulas | 2:55 |
| Video | Time |
|---|---|
| 10. Introduction to Booleans | 3:22 |
| 11. How to convert booleans to numbers | 3:08 |
| 12. Boolean algebra in Excel | 3:00 |
| 13. Array formulas with AND and OR logic | 3:36 |
| 14. Boolean operations in array formulas | 3:14 |
| Video | Time |
|---|---|
| 15. Basic SORT function example | 2:33 |
| 16. Basic SORTBY function example | 2:48 |
| 17. SORT and SORTBY with multiple columns | 3:02 |
| 18. Sort by custom list with SORTBY | 2:50 |
| 19. How to perform a random sort | 2:31 |
| Video | Time |
|---|---|
| 20. FILTER function basic example | 2:44 |
| 21. FILTER function with two criteria | 3:21 |
| 22. FILTER data between two dates | 3:06 |
| 23. FILTER with boolean logic | 2:57 |
| Video | Time |
|---|---|
| 24. The UNIQUE function | 3:12 |
| 25. How to count unique values | 3:22 |
| 26. Unique values with criteria | 3:08 |
| Video | Time |
|---|---|
| 27. The SEQUENCE function | 3:11 |
| 28. SEQUENCE of dates | 2:45 |
| 29. SEQUENCE of times | 3:03 |
| Video | Time |
|---|---|
| 30. How to generate random dates | 2:53 |
| 31. How to generate random times | 2:45 |
| 32. How to generate random text values | 2:58 |
| Video | Time |
|---|---|
| 33. Basic XLOOKUP example | 2:48 |
| 34. Basic XLOOKUP approximate match | 3:22 |
| 35. XLOOKUP with multiple lookup values | 2:39 |
| 36. XLOOKUP with boolean logic | 3:05 |
| Video | Time |
|---|---|
| 37. Excel TEXTAFTER function | 4:15 |
| 38. Excel TEXTBEFORE function | 3:48 |
| 39. TEXTAFTER and TEXTBEFORE advanced options | 3:41 |
| 40. TEXTAFTER with TEXTBEFORE | 3:48 |
| 41. Excel TEXTSPLIT function | 3:17 |
| 42. TEXTSPLIT with multiple delimiters | 3:58 |
| 43. TEXTSPLIT with numbers | 3:30 |
| Video | Time |
|---|---|
| 44. Two-way summary with dynamic arrays | 3:37 |
| 45. How to show top or bottom n results | 2:56 |
| 46. Filter with dynamic dropdown list | 2:55 |
| 47. List duplicate values with FILTER | 2:52 |
What you'll learn
Introduction to Dynamic Arrays
- What are Dynamic Arrays, and why is this such a big change to Excel's calculation engine?
- How do Dynamic Arrays impact traditional formulas and functions?
- The 6 new functions the directly leverage dynamic arrays, with examples of how each can be used.
- The concept of spilling and the spill range. How dynamic array formulas deliver multiple results, and how you can reference these results in other formulas.
- The importance of understanding that all formulas and functions will now run on a new calculation engine.
- How dynamic array formulas can be nested together to perform more than one operation at the same time.
Array formula basics
- What is an array, and what is an array constant?
- How arrays map perfectly to Excel ranges, and how to recognize vertical and horizontal arrays.
- What is an array formula, and do array formulas still need to be entered in a special way?
- A look at 3 simple array formulas to illustrate how array formulas can be used to solve tricky problems.
- How to "see" arrays in formulas – the best techniques to easily inspect arrays in a formula.
Boolean operations
- What are Boolean values and how are they handled by Excel?
- The best ways to convert TRUE and FALSE values in Excel to their numeric equivalents, 1 and 0, and why this matters.
- How Boolean algebra works – why addition is used for OR logic, and multiplication is used for AND logic.
- How Boolean algebra is used in array formulas and why you can't use the AND function, or the OR function.
- How you can use the SUM function to analyze data based on simple logical expressions.
- The importance of Boolean operations in modern Excel formulas and functions.
Sorting with formulas
- How the SORT function can be used to dynamically sort data in ascending or descending order.
- How the SORTBY function is used to dynamically sort data by more than one column at the same time.
- How to sort by multiple columns with both the SORT and SORTBY functions.
- How you can use array constants with the SORT function for a more compact formula.
- How to sort data in a custom order with a formula. This trick uses the MATCH function in a simple, elegant way.
- How to randomly sort data with a formula using the RANDARRAY function.
Filtering and extracting data
- How to set up the FILTER function to extract data based on matching criteria. FILTER works like a supercharged VLOOKUP to find and return multiple results.
- How to configure the FILTER function with two criteria. This is a great demonstration of how Boolean operations are used in modern Excel formulas.
- How the FILTER function can be used with the DATE function to extract data between dates.
- How FILTER is used with Boolean algebra to extract data using AND logic and OR logic.
Unique values
- A detailed intro to the UNIQUE function, which solves a difficult problem in Excel: how to extract unique and distinct values from a set of data.
- How to count the unique values returned by the UNIQUE function, and how to add FILTER to the mix to limit the count to values that meet certain conditions.
- How to apply conditions to limit the values returned by the UNIQUE function and exclude blank values.
Generating numeric sequences
- A detailed walkthrough of the SEQUENCE function, which can be used to generate numeric sequences of all kinds.
- How to generate a sequence of dates with the SEQUENCE function so that the number of dates and starting date can be easily changed.
- How to create a list of times by using SEQUENCE to create fractional values that Excel recognizes as time.
Generating random values
- How to generate 20 random dates in a given month with the RANDARRAY function.
- How to use the RANDARRAY function to generate random times between two given times.
- How to create a list of random text values using RANDARRAY with the INDEX function.
- How to use an Excel Table to feed new text values to the RANDARRAY function.
XLOOKUP
- How to quickly configure XLOOKUP for exact match lookup. One nice feature of XLOOKUP, in contrast to VLOOKUP, is that exact match is the default behavior.
- How to set up XLOOKUP for an approximate match lookup. With approximate matches, XLOOKUP can find the next largest OR the next smallest value.
- How XLOOKUP can find and return results for multiple lookup values at the same time.
- How to configure XLOOKUP to apply multiple criteria with Boolean logic.
Practical examples
- How to create a two-way summary with dynamic array formulas that works like a Pivot Table.
- How to dynamically list the top 3 or bottom 3 results in a set of data, and how to set up the formula so that 3 is a variable that can be easily changed.
- How to create a dynamic dropdown list that can be used to filter and extract matching records with the FILTER function and data validation.
Q + A
What version of Excel do I need?
Dynamic Array formulas are only available in Excel Office 365. This is is the subscription version of Excel, which always has the latest features. An easy way to check if you are using this version of Excel is check if you have the UNIQUE function. If you have UNIQUE, you have Dynamic Arrays.
Why Dynamic Arrays?
Dynamic Arrays are the coolest and most useful addition to Excel formulas in years, maybe ever. Dynamic Arrays make it easy to build array formulas to handle multiple values at the same time, solving many complex problems in an elegant way. You can now build formulas that filter, sort, and extract data with a clean, logical syntax. Dynamic Arrays are the future of reports, models, and dashboards that need to respond to changes instantly.
Will the course work for both Windows and Mac?
Yes. Dynamic Array formulas work on both Windows and the Mac, as long you use the 365 version of Excel. Videos were recorded in Excel 365 on Windows 10. All videos show shortcuts for both Windows and Mac.
Do you have a guarantee?
Of course! If you aren't satisfied, just let us know within 30 days and we'll issue a full refund. We want you to be completely happy.
How do I access the course content?
When you purchase the course, an account will be created for you automatically on the Exceljet website. You will receive an email immediately with login information. When you log in, you'll have full access to all videos and practice worksheets.
Why short videos?
You must learn Excel by doing, there is no other way. Our videos are short and focused, so you can immediately practice what you've learned, and come back any time for a quick refresher.
Your Satisfaction Guaranteed
If you're not completely satisfied, let us know within 30 days for a full refund. We want you to be 100% satisfied.
