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.

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.

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 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.

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

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

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.

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

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.

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

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

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

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.

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

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.

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

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.

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

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

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.

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.

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

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

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.

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

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

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.

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

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

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

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

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.

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

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.

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

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

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

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.

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

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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

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.

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

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.

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

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

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.

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