# Excel Dynamic Arrays – New Functions and Calculation Methods

Dynamic Array formulas are new to Excel Office 365.

Microsoft has listened to our requests and designed a set of functions that will completely change the way we use Excel.

After this, everything will change!

You’ll be using simple Excel formulas for Complex tasks.

**Everything we wished Excel could do, can now be done!**

The (Excel) World has Changed

If you have ever worked with complex formula combinations (see intimidating example below), or advanced formulas that require the use of a **CTRL-Shift-Enter, ** you have no doubt become known as the “Excel Guru” or formula expert of your office.

There are very few Excel users that enjoy creating such complicated formulas, and even fewer who enjoy deciphering these kinds of formulas made by users who have left the company and provided no documentation on the logic of such a creation.

At the Microsoft Ignite Conference in 2018, Microsoft introduced a new way of thinking and dealing with data in Excel.

## Introducing Dynamic Arrays

**Dynamic Arrays are not simply a new batch of functions added to the Excel Function Library. **

**Excel’s calculation engine has been completely redesigned to make many functions work faster and with greater flexibility.**

To demonstrate how much simpler and faster a solution can be achieved, lets take two previous video solutions, total their times to complete, and then compare the time it takes to arrive at the same goal using Dynamic Arrays (watch the video to see the results).

### Create a Unique List of Items (for a Data Validation List)

Check out the blog post and video here.

### Find Multiple Matches in a Dependent Dropdown List

For more details, check out this post.

We will also demonstrate the simplicity and brevity of the formulas by comparing their lengths and steps, both sequential and nested steps.

Problem #1 – Dependent Dropdown Lists

Below we have a list by Division. The Divisions are not grouped together; they are randomly arranged and each Division has an associated App.

The data validation drop-down includes a unique list of Division names. A second unique list of Apps is created and associated with the Division dropdown list. When the user selects a Division, the App dropdown list changes to reflect only Apps related to the selected Division.

For these lists to interact with one another, a cross-tabular data preparation table was created that lists all the Divisions in row order and all the associated Apps in column order.

Look at the formula used to create and dynamically manage the data preparation table.

=IF($D9<COLUMNS($F$7:F$7),"",INDEX(TableDiv[[App]:[App]],AGGREGATE(15,3,(TableDiv[[Division]:[Division]]=$E9)/(TableDiv[[Division]:[Division]]=$E9)*(ROW(TableDiv[Division])-ROW(TableDiv[[#Headers],[Division]])),COLUMNS($F$7:F$7))))

Even the most battle-hardened Excel veteran gets an uneasy feeling when presented with such a formula.

Remember the second dropdown list that is dependent on the selection made in the first dropdown list? Look at the formula (stored as a Data Validation rule) used to coordinate this conversation.

=OFFSET($F$7,MATCH($E$4,$E$8:$E$17,0),,1,COUNTIF(OFFSET($F$7,MATCH($E$4,$E$8:$E$17,0),,1,20),"?*"))

When the formula compiles the App list, it is smart enough to stop at the last App and not include any empty cells which may be located at the bottom of the table.

## Solution #1 – Dynamic Arrays to Create Dependent Dropdown Lists

We will still need to create a data preparation table, but this table will use one of the new array functions. This function superhero is named **UNIQUE**. The structure for the Unique function is as follows:

=UNIQUE(array, [by_col], [occurs_once])

The only required variable is the first argument, “array”. In **UNIQUE**‘s simplest form, we will provide the function with the cell range containing the Divisions and press Enter.

=UNIQUE(Table1[Division])

Observe the results.

Observe that even though we entered the formula in a single cell, and we did NOT utilize CTRL‑Shift‑Enter; the formula’s results occupy several cells. When a formula returns more than one result, the additional results “spill” into the adjacent rows.

What is even more exciting is that the original first dropdown list that displayed Divisions can refer to this new “spill list” as a source.

If we return to the Data Validation option for the Division dropdown, we don’t refer to the current list as the source (i.e. E8:E10); this is because the Division list may expand, and the dropdown would not display the additional Divisions. All we need to do is refer to the FIRST cell where we originally entered the Unique function.

There is no need to select the entire list.

There is, however, a new syntax requirement when referring to “spilled arrays”. After you select the original cell containing the Unique function, add a **# (pound or hashtag)** symbol to the end of the reference.

=E8 becomes =E8#

### What about adding new data to the bottom of the list?

The obvious question is, “What happens if a new Division is added to the Division list?

Will the “spill range” update to include the new Division?”

If your Division list has been formatted as a Data Table, adding new Divisions will be absorbed into the existing table.

If you are nor formatting your list as a Data Table, a way to reproduce this behavior is to include additional blank cells in the array range defined in the **UNIQUE** function.

Now for the more complicated part of this problem: the dependent App dropdown list. The new Dynamic Array **FILTER** function will solve this problem. The Filter function has the following structure:

=FILTER(array, include, [if_empty])

For the “array” argument, we will provide it the list of Apps. Because we only want to see Apps related to a selected Division, we will provide the “include” argument the list of Divisions and compare that list to the selected Division.

=FILTER(Table1[App],Table1[Division]=E4)

This creates the requested “filtered” list of Apps based on the selected Division.

To feed these Apps to the second dependent dropdown, return to the cell and set the Data Validation rule to point to the cell containing the Filter function (the first answer in the **FILTER** “spill range”).

=F8#

## Problem #2 – Sorted lists

Now that we have our unique lists, what if we need them sorted?

Previously, to sort our unique Division and App lists was a daunting task.

Not anymore.

**SORT** is a new function superhero. The structure for the **SORT** function is as follows:

=SORT(array, [sort_index], [sort_order], [by_col])

The function supports arguments that allow control over the sort criteria, sort order, and sort direction. In this example, we will only include the array of values to be sorted and accept the defaults for all remaining arguments.

=SORT(UNIQUE(Table1[Division])

=SORT(FILTER(Table1[App],Table1[Division]=E4))

## Compare the Old with the New

Let’s compare these two formulas.

**Old Formulas:**

=IF($D9<COLUMNS($F$7:F$7),””,INDEX(TableDiv[[App]:[App]],AGGREGATE(15,3,(TableDiv[[Division]:[Division]]=$E9)/(TableDiv[[Division]:[Division]]=$E9)*(ROW(TableDiv[Division])-ROW(TableDiv[[#Headers],[Division]])),COLUMNS($F$7:F$7))))

=OFFSET($F$7,MATCH($E$4,$E$8:$E$17,0),,1,COUNTIF(OFFSET($F$7,MATCH($E$4,$E$8:$E$17,0),,1,20),"?*"))

**New Formulas:**

=SORT(UNIQUE(Table1[Division])

=F8#

In addition, *THEY ARE SORTED!!!!*

## Conclusion

These are just a few of the many new Dynamic Array functions to be released in the very near future.

By using these new functions along side all of the existing Excel functions, we have thousands of new combinations of strategies to solve complex Excel problems… but in far simpler and better ways.

New users will never know how good they have it.

This is truly an exciting time to be an Excel user.

Athumani SimbeyeDecember 17, 2018 at 7:14 ami love the way u teach

i want tobe like you

Brian CrawfordDecember 19, 2018 at 10:35 pmThis is a great addition. Couple questions

Instead of tables could you use Dynamic Named Ranges

Are these as easily usable in VBA

thanks

Leila GharaniDecember 20, 2018 at 9:10 amYes – you can use dynamic named ranges in VBA.

GaryMarch 15, 2019 at 9:49 amHi – many thanks, a great solution! A question, can you combine the UNIQUE and FILTER functions rather than setting up two columns, and Im getting an error (#value) when there is only one item in my FILTER range?

Many thanks!

Bryon SmedleyApril 9, 2019 at 11:09 pmI’m not sure why you would get a #Value! error. If the filter criteria is a match, you would get the returned expected value. If the filter criteria is NOT a match, you would get a #CALC! error. You can suppress the error by adding a third parameter to the FILTER function. This is the action to take if there are no matches.

Example

=FILTER(A1,B1=C1,”No Matching Items”)

Hope this helps.

The XelPlus Team

Kun ThaungApril 30, 2019 at 10:54 amThank you so much for your help and contribution to us.

I had learned a lot of function from your expertise.

McKay SMay 15, 2019 at 4:18 amHi Leila, this is a great post – super clear on how to set this up. I have a question though: how can we adapt the filter function so that it gives the full list if a criteria cell is left blank? I’m trying to set up a situation where one can select from multiple criteria. I can get the filter function to work just fine if all criteria cells have a value, but if we need the criteria to be optional, I can’t get the filter to work. Any thoughts?

Leila GharaniJuly 4, 2019 at 6:42 amHi – we cover that in the advanced section of the dynamic arrays course where we filter for AND as well as OR conditions – basically if a selection is left blank it includes everything instead of looking for blank cells. You can do this using boolean logic with ISBLANK function. You just need to add that to the logical test. If you’re in the course, make sure you check out those last sections.

Leung Chi KwongJune 13, 2019 at 11:38 pmI think my Excel is the last Office 365 ProPlus version, but there is no dynamic array functions. How can I get it?

Leila GharaniJune 19, 2019 at 2:40 pmYou can get it if you get the Office insiders. The updates haven’t been released to the monthly channel yet.

JorgeJuly 4, 2019 at 3:58 amHi pretty! There is a work file for this video?

Leila GharaniJuly 4, 2019 at 6:38 amYes. The files are included in the course.