Dynamic Array Formulas
Dynamic Arrays are a huge upgrade that makes Excel formulas more powerful than ever. With Dynamic Arrays, you can extract unique values, sort and filter data, lookup multiple values, and build dynamic lists that instantly respond to new information. This course covers new functions in Excel that leverage dynamic arrays directly (FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, RANDARRAY, and XLOOKUP) and Boolean algebra, the logic used to apply criteria in modern formulas.
Note: Dynamic Array Formulas and XLOOKUP are only available in Excel 365.
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)
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.
- 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.
- 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.
- 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.
- 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.
Contact us with any questions.
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.