Microsoft Excel’s New Dynamic Array Functions Simplify Excel Programming

The Microsoft Corporation is on a tear!  First, it rebuilt Excel’s Calculation Engine in 2018.  Then it released a series of new Dynamic Array Functions for Office 365 in 2020.   Microsoft’s Dynamic Array Functions simplify Excel Programming.

=SORT(FILTER(CHOOSECOLS(rangeEmployeeData,1,2,3,4,5,6,7),AgeColumn>AgeSelection),3,1)

 

Microsoft’s Dynamic Array Functions are a Game Changer

How:  The Microsoft Excel Dynamic Array Functions simplify Excel programming in several ways.  For one, you will write fewer formulas, you will populate fewer cells with those formulas.  You will no longer need to copy formulas into every cell in the range if you use the new dynamic array formulas.

Secondly, you will write less Excel VBA code as these functions do so much in memory. The Unique and Filter Functions for example are pretty amazing.  Both are now dynamic and can be used in formulas.

Excel has changed, time to change with it; learn the new Dynamic Array Functions, or hire us to program them for you.

Example:  The new Sort and SortBy Function eliminate the need to write Excel vba code. No need to use the Sort Feature on the ribbon, and no need to write a macro to do it.  Sorting is now formula based via the Spill Functions.

Reason:   The new functions return a Dynamic Array. The old functions populate one cell.  With the old formulas, you must place the formula in every cell.  With the new formulas, you write the function in one cell, and it Spills into adjacent cells, as needed.   Add # to your functions that reference the Spill cell, Game Changer Folks!

Uses:   Use the Dynamic Array Functions to quickly and easily populate Excel Reporting & Analysis packages, to populate Dashboards, to supply data to Pivot Tables, to feed UserForms and Validation Controls, and so much more.

Basically do everything you are already doing, just do it easier, quicker, with fewer formulas and less vba.  Do it all in one cell, one function, dynamically.

Benefits:  Less broken formulas, fewer formulas to write and update.  Less time-consuming Excel VBA coding.

 

 

Our Thoughts on Being an Expert Excel Programmer.

To be a true expert programmer in Excel, one should know how to use the new Dynamic Array Functions, just as an expert should know how to code advanced Excel vba.  If you are an expert, in any field, you will find the need to keep your skills up to date, to be able to do the latest and greatest.  Excel is no different.  We recommend that you learn how to combine the new array functions into one powerful Excel formula.

 

Our Advice:  Learn to use the new functions.  Learn to combine them with other Spill functions as well as with older Excel functions, just add the # symbol.   Then supplement them with the older functions as needed.

Be the office Excel guru.  These functions can help to get you there. These are a huge game changer.

 

An actual example of three Spill functions in one function, to populate Excel Validation Control with Dynamic List of Regions, sorted:

=SORT(UNIQUE(FILTER(Regions, Regions>0)))

One Cell, One Function, No VBA Code,  100% Dynamic.

It is easier this way, and Microsoft Excel should be easy to use.

 

 

We are Excel and Access, LLC, an Excel Consultancy offering programming and training services in Microsoft Excel.

Excel Expert’s Comment:  In the past few years Microsoft Excel has really changed.  Excel is becoming more like writing Access Select Queries than writing traditional Excel formulas.   Dynamic, more power, less work.

 

 

 

Our Excel Programming and Training Services cover the new Dynamic Array Functions

If you need help with any of the new Dynamic Array Functions, the Excel and Access, LLC Team of Consultants can provide the Programming and Training Services your Organization needs.

We offer free consultations.  Please call Maria today.

 

We are here to discuss your Spill Range Function needs, give us a call at 877-392-3539.

 

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

 

 

 

 

 

Microsoft Simplifies Array Functions in Office 365

Gone are the days of hitting  the Ctrl+Shift+Enter keys

In the past, many Excel programmers avoided Array formulas like the plague.  They found them to be too complex, too hard to understand, and confused by the need to hit the Ctrl+Shift+Enter keys.  The traditional Excel Arrays were definitely powerful, unlike anything in Excel, and that created issues for some programmers.

But that has changed, Excel’s new Dynamic Array Functions greatly simplify Excel programming.  In our eyes, as well as in the eyes of many Excel MVP’s, these are a huge game changer.  Hence the growing number of videos on them. Watch the videos by Jon Acampora, Excel MVP extraordinaire.

 

Microsoft has some good content on their website on the new dynamic array functions.


Microsoft has some good content on their website on the new dynamic array functions.

 

 

If you are an expert Excel programmer, you will want to know how to use these. Combining them into one function, mixing them with the traditional functions is an art form.

Things Have Changed:  Unlike the Legacy Arrays, these new Spill Arrays are super easy to use, and you write them just like any other Excel formula.   As a result, the older Legacy (CSE) Array Functions are no longer needed, unless you are using an outdated version of Microsoft Excel, or if you are old-school, just preferring that approach. So still very good to know, you should know this, but you really do not need to necessarily use it.

 

 

According to Microsoft,  Ctrl+Shift+Enter array formulas are only retained for backwards compatibility reasons. Going forward, you should use Dynamic Array formulas instead.

 

 

Image of legacy Array function. Microsoft recommends not using these, but rather suggests using the new Dynamic Array Functions. As they simplify Excel programming.


The Legacy Arrays, while powerful, they could be intimidating. They were unlike other formulas in Excel and that could confuse people. So many people avoided them. They lost the power they had, but for some, they were too complicated to use.

The new Dynamic Array Functions changed that, now anyone can use an Array Function.

 

 

 

So what is a Microsoft Excel Dynamic Array Function?

aka Spill Array Functions, Spill Functions

Array Functions are nothing new to Microsoft Excel, advanced Excel programmers have been using them for years.  The new name for the older ones is “Legacy Arrays”, or “CSE Arrays”. These are the arrays that require you to hir the Ctrl + Shift + Enter keys.  What a pain.

Excel’s new Dynamic Array Functions are not the same, however.  These so-called Spill Array Functions Dynamically populate, or “Spill”, into neighboring blank cells, as needed. If the cell is not blank, then you will get the #Spill Error.

 

https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531

Says Microsoft.

 

 

 

Unlike traditional Excel Formulas

When using the new Dynamic Array Functions, you put the formula in one cell, and one cell only.  The results spill into adjacent cells.  The results are in memory; this is a virtual dataset.

 

The real skill is in the ability to combine two or more of the Excel dynamic array functions. Get this right, and you have incredible power at your finger tips.


The Formula composed of 5 Dynamic Array Functions is in the Green shaded Cell. That is it, the rest of the cells are empty.

 

Benefit:  Long gone is the need to manually place that VLookup formula into hundreds or even tens of thousands of cells.  Then when you update the formula, you need to update all of those cells. Not anymore, one complex array function, in one cell.

Less Work:  You no longer need to worry about your formulas reaching the bottom of the adjacent data-set.  Having to visually confirm that the formulas do go all the way down, each time you update the data.  Not anymore, not with these. Just add the # symbol to your functions, referencing the Spill cell.

 

We like to call the new Dynamic Array Functions available in Office 365, Spill Functions for short. In the image you can see how these functions work when combined.


Do less work: Excel goes Dynamic in Office 365 !

Base your XLookup and SumIfs on a Dynamic Array Function, add the # symbol, and these functions are now dynamic as well. No need to copy down the formula, it does that on its own.

 

 

In Our Opinion:  The Dynamic Array Functional are the most powerful, and easy-to-use functions in Excel today. That is from a programming perspective.  These are the functions we use first, then we use the traditional functions, as needed.

 

Experts Advice:

If you want to be an Excel expert, you MUST learn these.  These are the most powerful and useful functions in Excel today. They are super easy to use, and they actually save you a lot of time and work.

 

 

 

 

 

 

We can help you with your Business with Dynamic Array Functions  

If you need help with any of the new Dynamic Array Functions, the Excel and Access, LLC Team of Consultants can provide the programming and training services your organization needs. We offer free consultations.  Please call today.

 

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

 

 

 

 

Microsoft Excel Dynamic Array Functions

Here they are, in all of their glory, the new Microsoft Excel Dynamic Array Functions.  I have looked at many sites, including Microsoft’s, and it is hard to get a complete list of the new Office 365 Functions.   We compiled the list below, from what I could find on the web.  Overtime we will add to the list.   Resources listed below.

The list below is as of 6/2023. 

Image of new functions in Microsoft Excel. These are Dynamic Array Functions and they replace the Array Functions.

Microsoft released a series of new functions in Microsoft Excel in Office 365. These are Dynamic Array Functions, and they replace the “Legacy” Array Functions.

 

 

  1. Build a Dataset:

    1. ChooseCols – Returns the specified columns from an array.
    2. ChooseRows – Extracts the specified rows from an array.
    3. Take – Extracts a specified number of contiguous rows or columns from the start or end of an array.
    4. VStack – Vertically stacks arrays into one array.
    5. HStack – Horizontally stacks arrays into one array.

  2. Eliminate Records from a Dataset:

    1. Drop – Removes a certain number of rows or columns from an array.
    2. Filter – This function filters an array or range of data based on given criteria
    3. Unique – This function sorts a range or array of data by a corresponding array or range

  3. Manipulate Dataset:

    1. Sort – This function filters an array or range of data based on given criteria
    2. SortBy – This function sorts a range or array of data by a corresponding array or range
    3. Transpose – This function transposes a range or array of data
    4. Sequence – This function generates a list of sequential numbers or dates.
    5. ByCol – Convert an array or range to a single column.
    6. ByRow – Applies the LAMBDA function to each row and then returns an array of results.
    7. ToCol – Returns the array as one column.
    8. ToRow – Transform a range or array into a single row.

  4. Lookup Functions:

    1. XLookup – Searches a range or an array for a match and returns the corresponding results from a second range or array.
    2. XMatch – Is a more powerful successor Vlookup, HLookup and Lookup that can look up both in columns and rows and return multiple values.

  5. Text Manipulation:

    1. TextJoin – Concatenates a list or range of text strings using a delimiter.
    2. TextSplit – Splits strings by a specified delimiter across columns or/and rows.
    3. TextBefore – Returns text that’s before delimiting characters.
    4. TextAfter – Returns text that’s after deliminitaing characters.

  6. Others:

    1. WrapRows – Transform a range or array into a single row.
    2. WrapCols – Re-shapes a row or column into a 2D array based on the specified number of values per column.
    3. RandArray – This function generates an array of random numbers
    4. Let – Assigns calculation results to names.
    5. Lambda – Creates a function value, whcih can be called in formulas.
    6. Reduce
    7. Expand – Expands an array to the specified dimensions.
  7.  Pending ….

 

 

 

 

 

Examples of why you would want to use the Spill Array Functions

=VSTACK(TAKE(CHOOSECOLS(SalesDataTable,1,2,3,4,5,6,7),1),DROP(SalesDataTable,1))

 

Image of the Filter and Unique Functions combined. Of all the new Dynamic Array Spill Functions, these two are my favorite, and I use them daily. Add ChooseCols and Sort, and I can do a lot.


Learning how an Excel function works is one thing. Learning how best to leverage the function in your work is another. As with all things Excel, you want to find best practices and stick to those. What works best. Often that means combining several functions into one.

Below are real life examples of how to use the new Dynamic Array Functions.

 

 

 

Using the VStack Function to Consolidate Excel Data, into one Dataset:

  • The data you need for your Excel Dashboard is on multiple-tabs, one for each month of the year, for the past ten years. Possibly in seperate files.
  • How do you quickly consolidate the data, into one vertical data table so you can easily access it via XLookup and SumIfs Functions?
    • VStack Function
      • =VSTACK(DataSet1#,DataSet2#,DataSet3#, DataSet4#)

 

Image of VStack Functions being used. This is not one that will be used daily, but it is a life saver in certain circumstances.


VStack is a powerful Dynamic Array Function that allows the programmer to fix issues with their file, taking data from multiple tabs and files, and placing it into one vertical table.

 

 

 

Using the ChooseCols or Take Functions to Create a new Dataset:   

  • You have a table with 100 columns, you only need 10 or fewer columns of that data for your Sales Report.
  • You want to give the user the ability to change the order of the columns, on the fly.
    • ChooseCols Function, Filter Function (Reads Validation Control)
      • =FILTER(CHOOSECOLS(rangeEmployeeData,ChooseColsColumns#),RegionsColumn=RegionFilter)

 

Image of user-friendly application, leveraging the new Dynamic Array Functions to allow the user to do ad-hoc reporting. This is one complex function, in one cell, referencing external filters, no vba needed.


You can create a 100% dynamic report any Excel user can use. These Dynamic Array Functions are a game changer. Write fewer formulas, write less vba code.

 

 

 

Using the Unique, Sort and Filter to Populate a Drop-Down list:   

  • You want to dynamically feed a Validation Control, or ComboBox, or a ListBox with the list of Zip Codes in which your data table, sorted.
  • You want to feed a Unique list, that is dynamic, one that changes with the underlying data.
  • Set the Validation Control to allow “new” entries to the list. “Ignore Blank“, unchecked.
    • Unique Function, Sort Function, and Filter Function, all in one complex formula.
      • =FILTER(SORT(UNIQUE(P:P),1,1),SORT(UNIQUE(P:P),1,1)<>0)
        • Note the use of the Hashtag in the Validation Control, #

 

Image of Dynamic Validation Control, using the # Symbol to get the job done. That little # symbol is a game changer in Excel.


Drop-Down lists are one of the most used features in advanced Excel files. Validation Controls, ComboBoxes, ListBoxes, all are a way to allow the user to quickly select an item from a list.

Using the Filter, Unique and Sort Dynamic Array Functions allows you to create a dynamic list. Enter the # symbol. Gone is Offset w/ CountA to get a dynamic list.

 

 

 

 

Using the Unique Function to Remove Duplicates:   

  • You want to dynamically remove duplicate records from your data table.  Using this function, you can do so in seconds.
  • You can combine this function with other functions, such as Take, ChosseCols, Sort, SortBy, Filter, Drop, VStack, etc.
    • Unique Function.
      • =UNIQUE($H$5:$M$24)

 

 

Image of the Unique Function being used against a data range, containing duplicates. The Function eliminates duplicates from the dataset, programmatically, dynamically.


In the past removing duplicates in Excel was not so easy. Then Microsoft created the “Remove Duplicates” button which greatly enhanced ease of use. Now, with the Unique Function you can do so on the fly, programmatically, and it is 100% dynamic. A HUGE game changer.

 

 

 

Using the Transpose Function to Pivot your Data:

  • You want to eliminate duplicates from your dataset.
    • Unique Function, Transpose Function, Sort Function.
      • =TRANSPOSE(SORT(UNIQUE(tblEployeeData3[Region]),1,1))

 

Image of the new Transpose Function. Instead of using vba, or PasteSpecial Transpose, each time you want to pivot your data, use the dynamic Transpose Function.


Transposing Data has never been easier. Combine with the Unique Function, and you can do a lot with it. Feed lists, create headers, etc.

 

 

Using the ChooseCols, Unique, Sort and Filter to Simplify Ad-Hoc Reporting:   

  • You want to dynamically generate an ad-hoc report, but you want it to be easy to use, 100% interactive, without vba coding.
  • You want to allow the user to select the columns, the order of the columns, to be able to filter the records, sort by any column, based on the underlying data.
    • ChooseCols Function, Sort Function, and Filter Function, all in one complex formula.
      • =SORT(FILTER(CHOOSECOLS(PatientDataSortRange,PatientReportColumnOrder_Filtered#),PatientTableStatusColumn=PatientReportFilter_1),PatientReportSort_1,PatientReportSort_2)

 

 

Image of ChooseCols Function for dynamic reporting.


Traditionally, Excel reports are created by developing a tab, with many formula, and often macros. Now using the new Dynamic Array Functions, you can write a 100% dynamic, and interactive report, with one function, in one cell, without the need of writing vba code. Reporting in Excel has never been easier.

 

 

 

 

Expert Excel Programmer’s Opinion:

The real power of the new Microsoft Excel Dynamic Array Functions is in the combination of them, with other Spill Functions, as well as traditional functions.  This is where you really see them dance.  When you want to make your XLookup, SumIf or other functions dynamic, base them on the first cell of a Spill Range, and add the # symbol.  Now your funcitons will grow and shrink with the dataset, no need to copy down your formulas.

 

Advanced Example:  Build a unique list off of your Excel Table, using the Unique Function.  Then have the SumIf reference the first cell of that range, and add the # symbol.  Now your SumIf will grow and shrink with the dataset it is referencing, as long as that dataset is dynamic.  That is power folks.  ( See Gif by Microsoft, below )

 

 

 

Conclusion – Dynamic Array Functions Simplify Excel Programming

in 2018 Microsoft really upped their game with the creation of the new calculation engine.  Since 2020 Microsoft has released a series if new, powerful functions for Excel 365.

The Microsoft Excel Dynamic Array Functions simplify Excel programming in several ways.  For one, you will write fewer formulas, you will populate fewer cells with those formulas.  You will also find yourself writing less vba code.  The changes are monumental, there is no going back, Excel has evolved.

The Spill Functions will change how you program an Excel file, as your options have greatly changed.  Gone are many of the manual methods and steps needed.   The new functions are dynamic, and they do some amazing things, in seconds, such as removing duplicates, sorting datasets, filtering results, and more.

If your organization needs help with any of the new functions, or with anything Excel, our team of expert Excel consultants are here to help.  We offer both programming and training services in Microsoft Excel, Access, SQL Server and Azure.

 

 

 

Contact us for help with your Dynamic Array Functions

Contact Us Image.

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

 

 

 

 

 

FAQ – On Microsoft Excel new Dynamic Array Functions:

  1. Why would someone want to use the new Dynamic Array Functions versus the older, legacy Arrays?
    1. Simple, they are easier to use, they go in one cell, they are dynamic, they work with other formulas.
  2. Why do the cells in the Spill Range need to be empty, other than the cell with the Dynamic Array Function?
    1. The Dynamic Array Function results are in memory.  They are not placed in the cells, you cannot edit the function in those cells, the cells are empty.
    2. If one or more of the cells are populated, then you will get a Spill Error.
  3. Can you use the new array functions with other array functions and what about the older functions such as SumIf or VLookups?
    1. Yes, definitely, and you should, in both cases.  Take the best of both worlds and make a stellar application.
  4. Can the older, non-Array functions be made dynamic as well?
    1. Yes, just base them on the first cell of a Spill Range, then add the # symbol to the function.
  5. How can you get help with these functions, you can call us to discuss your needs live.  We can help with:
    1. Programming the new Dynamic Array Functions.
    2. We can train you or your staff on the new functions.
    3. We can mentor you on advanced Excel programming with these new functions.

 

 

 

People Also Ask:

  1. What is a Legacy Array, aka CSE Array?
    1. A “Legacy Array” refers to an older method of performing calculations and storing multiple values in a single cell or a range of cells in Microsoft Excel. These are often referred to as CSE Arrays, due to the key strokes one must use to create the array.
  2. What is a Dynamic Array Function.
    1. An Excel Dynamic Array Functions are a series of new functions released in Microsoft Office 365. Dynamic Array Functions, aka Spill Functions, allow a single formula to return multiple values in a Spill range, which automatically expands, or contracts based on the size of the result set.
  3. What is a Spill Function
    1. ,The term “Spill Range” or “Spilling” is used in the context of the new Dynamic Excel Array Functions in Office 365.  They are called Spill Functions because when you use a dynamic array function, such as Folter, Sort, Unique, or Transpose, the function automatically Spills the results into an adjacent range of cells that is fully dynamic, which means that expands or contracts as needed.

 

 

 

Other Posts on how to use the new Excel Spill Array Functions for Business.

We have written many posts on the subject, how to best utilize the new Excel functions.  Here we give real-life examples, to help you make sense of what these can do for your business.

 

Microsoft Excel Spill Array Functions for Business – what to do with them.

1. Related Post:  Using ChooseCols Function in Excel Reports – Excel Reporting Just Got Easier.

2. Related Post:  Using VStack Function in Excel VStack Function to Consolidate Excel Data

3. Related Post:  Using Unique Function with Sort Function in Validation Controls – Users Love Drop Down Lists

4. Related Post:  Using Transpose Function in Excel – Transposing Excel Then and Now

5. Related Post:  Using Unique Function in Excel to remove duplicates – 5 Ways to Remove Duplicates in Excel

6. Related Post:  Using Excel XLookup Function w/ Messy PDF Data – XLookup built-in Offset Functions use with Messy PDF Data

 

 

 

 

Additional Resources on the new Dynamic Array Spill Functions

 

Dynamic array formulas and spilled array behavior

  • Excel formulas that return a set of values, also known as an array, return these values to neighboring cells. This behavior is called spilling.
  • Formulas that can return arrays of variable size are called dynamic array formulas. Formulas that are currently returning arrays that are successfully spilling can be referred to as  spilled array formulas. Says Microsoft.
  • Dynamic array formulas vs. legacy CSE array formulas – Microsoft Support

 

Image of Microsoft Support page on the new Dynamic Array Functions.

Visit the Microsoft website for more information.

 

 

Outstanding resources below on the Microsoft Excel Dynamic Array Functions:

Getting information on the release of the Dynamic Array Functions, in the beginning, was not easy, as it was top secret.  Back then finding anything at all on them was difficult.  Luckily those days are over.  The links below have much information to offer on this as well as other topics.  We recommend you check them out if the subject is of interest to you.