Excel Dynamic Arrays

Introduction & Glossary of Modern Excel Terms Preview 02:11

In this section we’ll look at the major changes that came with Excel Dynamic Arrays and the new calculation engine. This includes some rules and important points you need to be aware of when you work with Excel formulas going forward.

Formulas Spill & Other Excel Dynamic Array Characteristics Preview 07:40

In Excel with Dynamic Arrays any formula that returns many results will now spill to many cells. In this lecture we’ll cover some important characteristics you need to be aware of.

Excel FILTER Function (The New Excel Power Multi-Lookup) Preview 09:27

One of my new favorite functions is the Excel FILTER function. I’ll show you why I’m such a fan and what you can use it for.

SORT & SORTBY Functions in Excel Preview 09:42

SORT and SORTBY are two very useful new Excel Dynamic Array functions. I’ll show you how you can take full advantage of these amazing additions to Excel.

UNIQUE Function (Get a Unique List of Values in Excel) Preview 07:26

We’ll cover Excel’s new UNIQUE function which will help you get a unique or distinct list of items from a data set.

Practical Combination of UNIQUE, SORT & FILTER in Excel Preview 06:47

In this lecture we’ll combine the new Excel dynamic array functions UNIQUE, SORT and FILTER to perform a super-fast data analysis.

SEQUENCE Function in Excel Preview 08:12

We’ll cover how the new Excel SEQUENCE function can easily help you perform tasks that used to be difficult with Excel formulas like an Excel calendar or a loan amortization table. I’ll also show you how the SEQUENCE function can transform legacy Excel formulas to power formulas.

RANDARRAY Function in Excel Preview 07:03

Another useful new dynamic array function is RANDARRAY. I’ll show you how you can use it for modeling, test cases or training materials.

'@ Prefix for Compatibility (in Excel Formulas) Preview 04:47

You’ll come across the @-Prefix when you’re opening an older Excel file in your new Excel Dynamic Array version. We’ll cover why it’s there and what it does.

Challenge: Create an Excel Summary Report FAST Preview 00:19

Answer: Create an Excel Summary Report FAST Preview 03:14

In the challenge we create a quick dynamic array overview report.

NEW XLOOKUP in 2020 for Excel Office 365 Preview 00:35

XLOOKUP Basics - The New Power Lookup Formula in 2020 Preview 06:39

Learn the basics of Excel's new Power Lookup formula: Excel XLOOKUP Function which is available in Excel for Office 365.

XLOOKUP - 5 Practical Examples Preview 11:56

In this lecture you'll have a better idea on how you can use the new Excel XLOOKUP function in your daily work. I show 5 practical examples. You'll learn how to use XLOOKUP to:

- Handle #NA (when items aren't found)

- Use XLOOKUP for horizontal lookups (instead of HLOOKUP)

- Use Wildcards in XLOOKUP

- Two way lookup with XLOOKUP

- Approximate lookup instead of Exact match lookup with XLOOKUP

Quiz: Test Your Excel Dynamic Array Knowledge

Conclusion: Dynamic Array Rules Recap Preview 00:48

Introduction - DA impact on Excel Formulas Preview 03:31

We’ll learn what Multi and Scalar formulas are, why it's important to understand the difference, and how it all changed with the new calculation engine in Office 365.

Backward & Forward Compatibility with Excel Dynamic Arrays Preview 10:06

In this lecture we’ll cover backward and forward compatibility between Legacy Excel and Office 365 and what you should be aware of.

Absolute & Mixed Cell Referencing - No Longer Needed in Excel? Preview 05:46

In legacy Excel we always had to think about absolute and mixed cell referencing. Meaning how we want a reference to react when we pull the formula down. In the new Excel with Dynamic Array we have to worry about it much less. I’ll show you why.

SUMIFS & COUNTIFS Improved! (More than one criteria in the same column) Preview 07:34

In this lecture we’ll account for more than 1 criteria in one column. Something that was not easily possible in legacy Excel.

VLOOKUP with Excel Dynamic Arrays Preview 05:43

In this lecture we’ll have a look how we can take advantage of the new dynamic array referencing together with the Excel classic VLOOKUP function.

IF Function & Excel Dynamic Arrays Preview 04:09

In this lecture we’ll use the classic Excel IF function together with dynamic array referencing.

Referencing Spilled Data Ranges in Other Excel Sheets Preview 06:35

In this lecture we’ll take a look at how we can reference spilled data ranges in other sheets.

TRANSPOSE & FREQUENCY Functions Made Easier with Excel DA Preview 05:31

The Excel TRANSPOSE and the FREQUENCY functions are great for report creation and data analysis. Such formulas were difficult to write in legacy Excel because they were CSE (Ctrl+Shift+Enter) formulas.

Do Excel Dynamic Arrays Make Spreadsheets Slow? Preview 05:56

In this lecture we’ll look at the performance of the new array referencing vs. standard referencing, especially for large datasets. I’ll use a special Excel add-in to measure the calculation speed and I’ll show you which one is faster.

Challenge: Data Analysis with Legacy Excel Formulas & Dynamic Arrays Preview 00:33

Answer: Data Analysis with Legacy Excel Formulas & Dynamic Arrays Preview 08:30

In the challenge we’re doing data analysis with legacy Excel formulas together with Dynamic array functionality.

Conclusion: Impact of Dynamic Arrays on Legacy Excel Formulas Preview 02:11

In this recap we’re looking at the key takeaways for this section.

Introduction - DA Impact on Excel Features Preview 01:11

In this section we’re going to take a look at some of the most common Excel features and how we can use them together with Dynamic arrays or spill range references.

Data Validation List using Spill Range (and Excel Table) Preview 05:53

We’ll create simple drop-downs with Dynamic Arrays and Spill Range References to get a dynamic drop down.

Excel Drop-down List (Excludes Blank Cells) Preview 05:07

In this lecture we’ll create a data validation drop-down that automatically excludes any blank cells and duplicates in the source range.

Dependent Drop-down Excel Data Validation (Unique & Sorted) Preview 04:49

We’ll learn how to create a dependent drop-down data validation using the new Excel Dynamic Arrays. There will be 2 drop downs and the choices available in the second drop-down will be dependent on the selection made in the first drop-down. We’ll use Excel’s SORT, FILTER and UNIQUE functions to create these selections.

Conditional Formatting & Excel Dynamic Arrays Preview 10:57

We’ll look at the conditional formatting options for spill ranges in Excel Dynamic Arrays.

Excel Charts with Dynamic Arrays Preview 07:46

We want to create Excel charts that automatically expand depending on our dataset by using spill array referencing.

Challenge: More Dynamic Dependent Data Validation Excel Report Preview 00:25

Answer: More Dynamic Dependent Data Validation Excel Report Preview 05:11

In our challenge we want to create a dynamic, dependent data validation report.

Bonus: Interactive Excel Dashboards with Radio Buttons Preview 13:15

Conclusion: DA Impact on Excel Features Preview 01:27

In this lecture we’ll review some key takeaways from this section.

Congratulations! You Completed the Fundamentals! Preview 01:26

After completing the first sections you now know everything about the new dynamic array functions, the changes brought by the new calculation engine of Excel, what spill references are, and how this impacts Excel formulas and features. In the following sections we’ll learn how to do more complex tasks and data analysis. We’ll learn about Array Constants and how you can use them to create interactive reports and do advanced Excel analysis.

More Challenges & Practice Activities Preview 00:07

Quiz: Challenge Successfully Completed?

Challenge Answers Preview 00:01

Download the Workbook to double check the answers for this section.

Why THIS Excel Theory is Important Preview 01:37

Boolean logic is part of the IF function or in complex SUMIF formulas. Understanding Boolean logic will give you the ability to create more complex formulas that can handle complex tasks.

Boolean Logic (TRUE & FALSE) in Excel Formulas Explained Preview 04:29

In this lecture we’ll learn about Boolean logic and what TRUE and FALSE means in Excel.

Excel FILTER to Exclude Blanks, Zero-Value Cells & Text Preview 05:55

We’ll apply Boolean logic in some practical examples to exclude blank, zero-value cells or text.

FILTER Function for Multiple Criteria (AND Logical Test in Excel) Preview 07:33

In this lecture we’ll use the new Excel FILTER function and filter our dataset based on multiple criteria.

FILTER Formula for Either Value (OR Logical Test in Excel) Preview 03:18

We’ll apply the OR logical test, i.e. we’ll test if either criterion is TRUE or not. For instance, to show all employees from a data set that either earn >100,000 or < 30,000.

Challenge: Dynamic Array Filter Like a Pro Preview 00:37

Answer: Dynamic Array Filter Like a Pro Preview 07:50

In the challenge we’ll apply some of the knowledge of Boolean logic to the Include-Argument in the Excel FILTER function.

Conclusion: How to Filter Like a Pro in Excel Preview 00:12

Debugging Trick & Excel Array Syntax - Comma versus Semicolon Preview 07:33

In this section we’ll learn Basic Array logic. It’s important to understand Array Syntax to correctly use array constants in your formulas and learn to properly debug formulas when they don’t work.

Note on Array Syntax for European & None-US Regional Settings Preview 01:04

Named Array Constants for Quick Report Setup in Excel Preview 02:32

We’ll create Array Constants out of names. We can Define Names for Array Constants, so Excel memorizes it under a certain name.

FILTER Function with Array Constants for IF EMPTY Argument Preview 02:51

In this lecture we’ll look at how we can use Array Constants in the IF EMPTY argument of the new Dynamic Array FILTER function.

Top 3 Values with Excel LARGE Function Preview 04:43

We can also use Array Constants in legacy Excel functions such as the LARGE function to retrieve the top 3 values of a dataset.

Custom Dynamic Sort with Multiple Columns in Excel Preview 03:30

Array Constants can also be used in the new Dynamic Array function SORT. We’ll do a custom sort with multiple columns and we’ll also decide how each of the columns should be sorted.

Quiz: Working Correctly with Array Constants in Excel

Conclusion: Use the Correct Array Syntax in Excel Formulas Preview 00:08

Introduction - Interactive Excel Reports Made Easy Preview 00:45

In this section we’ll create Interactive Reports in Excel that do not need refreshing. We’ll setup a fully dynamic loan amortization table and an Excel calendar. We’ll use learn about searchable drop-down lists and a shrinking data validation.

Easy Pivot Table-Style Reports with Dynamic Arrays Preview 12:28

We’ll look a Pivot-Style reports with Excel’s Dynamic Arrays. These reports change automatically as soon as the source data changes. We’ll use the new Excel functions SORT, UNIQUE, and TRANSPOSE. We’ll also apply some Custom Formatting to make the report look cleaner.

Pivot Table (Style) Report with Row & Column Selection (INDEX MATCH & DAs) Preview 13:30

We’ll create an even more dynamic Pivot-Style report where the user can select which categories they want to see in the rows and columns. We’ll use Excel’s classic INDEX & MATCH functions together with the new Dynamic Array functions SORT and UNIQUE.

Interest Paid per Month & Year with Excel SEQUENCE Function Preview 08:47

In this lecture we’ll calculate the total amount of interest we must pay for a loan. We’ll use the Excel SEQUENCE function to solve this.

Loan Amortization Table in Excel (Interest, Principal) with SEQUENCE Preview 07:05

With the use of the SEQUENCE function we’ll generate a complete loan amortization table. We’ll split the monthly loan payment in the portion to repay interest and the principal pay down.

Dynamic Excel Calendar with SEQUENCE Preview 11:44

In this lecture we’ll create an automatic calendar in Excel using the SEQUENCE function and some of Excel’s useful Date-Functions. In Legacy Excel such a calendar was quite complex to setup. Now, with the new Dynamic Array Excel we can get our formulas to spill automatically and use the SEQUENCE function to set this up quite easily. We’ll also cover how dates work in Excel and apply the WEEKDAY and MONTH function.

Shrinking Data Validation - Hide Used Items in Excel Preview 07:50

In this lecture we’ll setup a data validation where used items will be hidden from the selection. So, in the drop-down list, an option can only be selected once. I’ll show you how to use Excel’s COUNTIF and FILTER function to create such a shrinking data validation list.

Searchable Data Validation Drop-down List in Excel Preview 09:07

We’ll create a searchable data validation drop-down list. We’ll use the Excel SEARCH function together with the ISNUMBER and the new FILTER function to set this up.

Challenge: Interactive KPI Selection Excel Report Preview 00:29

Answer: Interactive KPI Selection Excel Report Preview 07:46

In the challenge we create an interactive KPI selection report. The user of the Excel report will have the ability to select which category and KPI to look at and the report will update automatically.

Introduction - Advanced Data Analysis with Excel Dynamic Arrays Preview 01:04

In this section we’ll look at the different challenges you might face when you have to do advanced data analysis in Excel. We’ll look at how to get multiple match results for many different categories. We’ll also look at how to filter for approximate text matches. Well also create reports that’ll return the top n largest categories or values.

Multiple Match Results Across Columns (Excel FILTER Function) Preview 03:18

In this lecture we’ll use the UNIQUE and FILTER function to get multiple match results in multiple columns.

Multiple Match Results in Single Cell (Excel TEXTJOIN) Preview 03:54

Here we’ll use an alternate method of showing multiple match results. This time we’ll show all matches in one cell. We’ll use the Excel TEXTJOIN function which combines the text from different cells or ranges in one cell.

Lookup Approximate Match with Excel FILTER Function Preview 06:17

In this lecture we’ll lookup values based on an approximate match. We’ll use the Excel FILTER function to return all found values.

Find Top n Largest Categories & Values (FILTER) in Excel Preview 05:45

Here we want to find the top n largest values and categories. So, we’re not going to just return the largest values but also the category they belong to. To solve this, we’ll use the Excel FILTER function together with the LARGE function.

Challenge: Top 3 Salaries & Employees by Department Preview 00:27

Answer: Top 3 Salaries & Employees by Department Preview 07:01

In the challenge we want to find out the top 3 salaries and employees by department.

Introduction - How Arrays Work in Excel Preview 01:22

In this section we’ll learn how Arrays work together in Excel and how you can manipulate them to work for you. This is important for any function you want to add a logical test to or account for more criteria.

Lifting & Pairwise Lifting (Excel Arrays) Preview 05:20

In this lecture we’re going to look at 2 important array behaviors: Lifting and Pairwise Lifting.

Broadcasting Excel Array Behavior Preview 04:57

In this lecture we’ll look at the third important array behavior called Broadcasting.

Implicit Intersection - The Main Legacy Excel Problem Preview 01:55

Implicit Intersection is a term used in Legacy Excel for cases when people provide multiple values to scalar arguments and get strange results. We’ll find out the reason for this and how this looks like in the new Dynamic Array Excel.

Are Excel CSE Formulas No Longer Needed? Preview 01:51

We’ll find out if CSE Formulas (Ctrl-Shift-Enter) are still needed in the new Dynamic Array Excel.

Quiz: How Excel Arrays Work Under the Hood

Introduction - More Advanced Excel Dynamic Array Examples Preview 01:18

In this section we’ll work on examples that require us to take advantage of our knowledge of lifting and broadcasting. We’ll learn how to get or filter unique values from non-adjacent columns. We’ll also do a case where we need to filter for either both or either criteria.

Unique Values From Nonadjacent Columns in Excel Preview 06:24

In this lecture we’ll learn how to get unique values from non-adjacent columns. We’ll use the Excel FILTER function together with the CHOOSE and the SORT function.

FILTER for BOTH Criteria or EITHER Criteria (AND, OR Together in Excel) Preview 08:20

Here we’ll learn how to filter for two criteria or for either criteria. We’ll use the Excel FILTER function together with the ISBLANK function and take advantage of Excel’s Boolean logic, broadcasting and pairwise lifting in one formula.

Split Text & Numbers From 1 Cell to 2 Columns in Excel Preview 09:50

In this lecture we’ll going to split text and numbers from one cell to two separate columns. Something that was quite difficult in legacy Excel. We’ll use Excel’s TEXTJOIN function together several other functions to get this done easily.

Challenge: Filter Values From Non-Adjacent Columns in Excel Preview 00:30

Answer: Filter Values From Non-Adjacent Columns in Excel Preview 03:51

In the challenge we want to filter values from non-adjacent columns.

Final Words Preview 01:40

I hope after completing the course, you are now confidently working with Excel Dynamic Array formulas and are using them to solve difficult tasks and create impressive reports.

** Next Steps - BONUS Preview 01:41