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 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#
If we select a different Division from the first dropdown, the options for the second dropdown change to match.
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])The Divisions are now sorted.
We will apply the same SORT feature to our second “spilled array”.
=SORT(FILTER(Table1[App],Table1[Division]=E4))We now have a sorted list of Apps.
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!
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
